CODEWITHSUNDEEP
phpsqljson
Jason
Jason

Reputation: 1817

PHP nested SQL query into JSON format

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!

Upvotes: 0

Views: 1013

Answers (2)

didierc
didierc

Reputation: 14730

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

Upvotes: 1

Explosion Pills
Explosion Pills

Reputation: 191749

I would set the fetch mode to PDO::FETCH_OBJ (or PDO::FETCH_ASSOC) instead of working with the numbers. You can do this on the connection, but also fetch.

$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);

Upvotes: 1

Related Questions