Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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!

share|improve this question

2 Answers

up vote 1 down vote accepted

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);
share|improve this answer
Awesome, thanks! – Jason Mar 29 at 23:57
up vote 0 down vote

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);
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.