PHPLover PHPLover - 4 years ago 166
SQL Question

How to optimize the SQL query and add more array elements to the existing array?

I've an array named

$insert_users
as follows:

Array
(
[0] => 9def02e6337b888d6dbe5617a172c18d
[1] => a6d22e4cc3f65778a60b359842bcec82
[2] => e17151c98358b60910d4d9fd6ae73ac4
[3] => 472d3d7eca74cb3518bcba787aee8540
)


Now I have to fire an SQL query for each of these ids from the above array. The code I've written for this is as follows:

foreach($insert_users as $key => $value ) {


$sql = " SELECT DISTINCT ud.user_mobile_number, u.user_first_name, u.user_last_name FROM OCN.users_groups_subscribe ugs JOIN OCN.users_details ud ON ud.user_id=ugs.subscribe_user_id JOIN OCN.users u ON u.user_id=ugs.subscribe_user_id WHERE ugs.subscribe_user_id ='".$value."'";

$this->mDb->Query( $sql);
$students_data = $this->mDb->FetchArray();
}


It returns me following array for single id element from the
$insert_users
array:

Array
(
[0] => Array
(
[user_mobile_number] => 9929933345
[user_first_name] => Ashish
[user_last_name] => Patil
)

)


But I'm not getting the array in desired format. I want the array
$students_data
in following format and for all the values from the array
$insert_users
:

Array(
[0]=>Array
(
[user_full_name] => Ashish Patil //This key should be newly generated & should contain values of keys user_first_name & user_last_name concatenated to each other
[user_mobile_number] => 9929933345
)
)


Also, can anyone tell me is there any way to fire the SQL query just once and get the desired array which will contain details for all the ids from array
$insert_users
?

Answer Source

Rather than doing

foreach($insert_users as $key => $value ) {


    $sql  = " SELECT DISTINCT ud.user_mobile_number, u.user_first_name, u.user_last_name FROM OCN.users_groups_subscribe ugs JOIN OCN.users_details ud ON ud.user_id=ugs.subscribe_user_id JOIN OCN.users u ON u.user_id=ugs.subscribe_user_id WHERE ugs.subscribe_user_id ='".$value."'";

    $this->mDb->Query( $sql);
    $students_data = $this->mDb->FetchArray();                          
}

Do

$value = "'" . implode("','", $insert_users) . "'";
$sql  = " SELECT DISTINCT ud.user_mobile_number, CONCAT(u.user_first_name,' ',u.user_last_name) AS user_full_name FROM OCN.users_groups_subscribe ugs JOIN OCN.users_details ud ON ud.user_id=ugs.subscribe_user_id JOIN OCN.users u ON u.user_id=ugs.subscribe_user_id WHERE ugs.subscribe_user_id IN(" .$value . ")";

$this->mDb->Query( $sql);
$students_data = $this->mDb->FetchArray();

Now you should get all the result you want in one query

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download