我想组合两个表的数据并将其作为json返回，但我似乎做不好。我尝试过INNER/LEFT Join和嵌套查询，但都没有有效的结果。
以下是我的表格结构：
事件：
----------------------------------
id | title | extra1 | extra2
----------------------------------
1 | EventNo1 | ... | ...
2 | EventNo2 | ... | ...
3 | EventNo3 | ... | ...
4 | EventNo4 | ... | ...
5 | EventNo5 | ... | ...
图像：
----------------------------------
id | event | val | height | width
----------------------------------
2 | 5 | xxx | xxx | xxx
3 | 2 | xxy | xxx | xxx
6 | 5 | xyx | xxx | xxx
所以基本上，我想要的是从events获得数据，对于images.event与events.id匹配的每个事件，我想要用images的数据进行扩展。
这是我想要实现的json结果：
[
{
"id": "1",
"title": "EventNo1",
"extra1": "...",
"extra2": "...",
"images": []
},
{
"id": "2",
"title": "EventNo2",
"extra1": "...",
"extra2": "...",
"images": [
{
"id": 3,
"val": "xxy",
"height": "xxx",
"width": "xxx"
}
]
},
{
"id": "3",
"title": "EventNo3",
"extra1": "...",
"extra2": "...",
"images": []
},
{
"id": "4",
"title": "EventNo4",
"images": []
},
{
"id": "5",
"title": "EventNo5",
"extra1": "...",
"extra2": "...",
"images": [
{
"id": 2,
"val": "xxx",
"height": "xxx",
"width": "xxx"
},
{
"id": 6,
"val": "xyx",
"height": "xxx",
"width": "xxx"
}
]
}
]
如何在php+mysqli中实现这一点？
$q = "select * from events";
$res = mysql_query($q);
while($row = mysql_fetch_assoc($res)){
$image_data= array();
$q2 = "select * from images where event = ".$row['id'];
$res2 = mysql_query($q2);
while($row2 = mysql_fetch_assoc($res2)){
$image_data[] = $row2;
}
$row['images'] = $image_data;
$event_data[] = $row;
}
echo json_encode($event_data);
伪代码，因为我不知道你用什么库/包装器来访问数据库：
$query = "select * from events";
$rows = $db->query($query);
foreach($rows as $k => $row) {
$query = "select * from images where event = ".$row['id'];
$rows[$k]['images'] = $db->query($query);
}
echo json_encode($rows);
这是假设$db->query()以数组的形式返回结果集，如果找不到行，则包括一个空的结果集。