Add the results from a PHP array into a SQL Select for each Result to be procesed

Tags: , ,



I am a newbie so I want to know if what I am asking is possible.

I have a SQl Select query that returns a While loop and I convert the results (peoples names)to a php Array.

In a another SQL Query below it on the same page I need to get the address for each name using a SQL SELECT.

I have echo’d the json_encode($name_arr) and see all three names, so I know my array of names works in the first SQL Query.

I have tried using implode() but I got one result for one name and not all three names or nothing.

Thank you for your help.

Array Result = 3 records found.
Array=
id Name
1   John
2   Mike
3   Jane
**Get names script**

$sql = "SELECT  names FROM directory WHERE  age = '33' ";
$result = $conn->query($sql);


if ($conn->query($sql) === FALSE) {
echo"error";
die();

} else if ($result->num_rows > 0) {

while($row = $result->fetch_array()) {

$name =  "".  $row['name']."";

$name_arr = array(  "name"  => $name);

}} 
**Get address script**

$sql = "SELECT * FROM address WHERE name IN ('".implode("','",$name_arr "') ";
$result = $conn->query($sql);

//Check if it failed to do above
if ($conn->query($sql) === FALSE) {

echo"error";
die();

} else if ($result->num_rows > 0) {

while($row = $result->fetch_array()) {


$street =    "".  $row['street']."";
$postcode =  "".  $row['postcode']."";
$city =      "".  $row['city']."";

  $address_arr[] = array(
   
  "street"    => $street,
  "postcode"  => $postcode,
  "city"      => $city);
  }
   
echo json_encode($address_arr);
exit();
} 

Answer

You can put it in one query to get all infos like:

SELECT  ad.*
FROM directory d 
LEFT JOIN address ad ON ad.name = d.name 
WHERE  d.age = '33' ";


Source: stackoverflow