John Davidson

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

0 comments
Message:


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();
}


My Answer:
"SELECT id, name FROM people";
$result = mysqli_query($conn, $sql);

$name_arr = array();
while ($row = mysqli_fetch_assoc($result)) {
$name_arr[] = $row['name'];
}

echo json_encode($name_arr);

**Get addresses script**

foreach ($name_arr as $name) {
$sql = "SELECT address FROM addresses WHERE name = '$name'";
$result = mysqli_query($conn, $sql);

while ($row = mysqli_fetch_assoc($result)) {
echo $row['address'];
}
}

In the above code, we first retrieve the names from the people table and store them in an array. We then loop through each name in the array and use it to query the addresses table to get the address for each name. This way, we can get the address for each person based on their name.

Rate this post

3 of 5 based on 6088 votes

Comments




© 2024 Hayatsk.info - Personal Blogs Platform. All Rights Reserved.
Create blog  |  Privacy Policy  |  Terms & Conditions  |  Contact Us