Ask a Question Register Login

PHP nested SQL query into JSON format

546

I have a somewhat complicated php sql query that I need to put into JSON to send back to my terminal. Can someone help? I've been struggling with this and can't seem to get it right. My code is something like:

//Grab all people living in a city, and for each of those people, grab all of their carsmodel and license plate.  My output should be something like [{"firstname":John, "lastname":Smith, "cars":[car1, car2, car3...], "plates":[plate1, plate2, ...]},{"firstname":Jack,"lastname":Hide, "cars":[car1, car2, car3], "plates":[plate1, plate2,...]}]
$sql = "SELECT id, firstname, lastname FROM residents WHERE residents.city = ?";
$q = $connection->prepare($sql);
$q->execute(array($city));

while($row = $q->fetch())
{
    $sql2 = "SELECT carid FROM cars WHERE userid = ?"
    $q2 = $connection->prepare($sql2);
    $q2->execute(array($row[0]));
    while($row2 = $q2->fetch())
    {
        // What do I do here?!
    }
}

return json_encode(//?);

Any help greatly appreciate!

Thanks!

636

Answer

Solution:

Make it in one query:

SELECT id, firstname, lastname, carid -- and perhaps other cars columns here
FROM residents
INNER JOIN cars
ON cars.userid = residents.id
WHERE residents.city = ?
ORDER BY residents.id

then in the PHP part:

$data = array();
$current = array();
while($row2 = $q2->fetch())
{
    if ($current['id'] != $row[0]) {
        $data[] = $current;          
        $current = array('id' => $row[0], 'firstname' => $row[1], 'lastname' => $row[2] );
    } else {
        $current['cars'][] = array( /* row data for car */ );
    }
}
array_shift($data);
return json_encode($data);
846

Answer

Solution:

I would set the fetch mode toPDO::FETCH_OBJ (orPDO::FETCH_ASSOC) instead of working with the numbers. You can do this on the connection, but alsofetch.

$q2 = $connection->prepare("SELECT carid FROM cars WHERE userid = ?");
$users = array();
while ($row = $q->fetch(PDO::FETCH_OBJ)) {
    $q2->execute(array($row->id));
    $row->cars = $q2->fetchall(PDO::FETCH_OBJ);
    $users[] = $row;
}
return json_encode($users);

People are also looking for solutions to the problem: Issue with sending a .csv file through ajax to PHP file

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.