我有一个有点复杂的php sql查询，我需要把它放入JSON中发送回我的终端。有人能帮忙吗?我一直在努力解决这个问题，似乎总是做不好。我的代码是这样的:
//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(//?);
任何帮助都非常感谢!
谢谢!
一次查询:
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
然后在PHP部分:
$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);
我会将获取模式设置为PDO::FETCH_OBJ(或PDO::FETCH_ASSOC)，而不是处理数字。您可以在连接上这样做，也可以在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);