我正在尝试从两个相关的MySQL表构建一个json输出。我有一个“餐厅”表和“盘子”表，“餐厅”表中的每个项目在“盘子”表中都有几个相关的项目，这些项目由id引用。每个餐厅项目ID都是Dishes表中作为f_id的外键。
例如：饭店表
+----+-------------+-----------+
| Id | Name | Misc Info |
+----+-------------+-----------+
| 1 | Restaurant1 | Some Info |
+----+-------------+-----------+
菜桌
+----+------+-----------+-------------+
| Id | f_id | dish | description |
+----+------+-----------+-------------+
| 1 | 1 | DishName | DishDesc. |
| 2 | 1 | DishName2 | DishDesc. |
+----+------+-----------+-------------+
我想从这些表创建JSON输出，如下所示：
{
"Restaurants": [
{
"name": "String content",
"misc info": "String content"
"Dishes": [
{
"dish": "String content",
"description": "String content"
},
{
"dish": "String content",
"description": "String content"
}
],
},
{
"name": "String content",
"misc info": "String content"
"Dishes": [
{
"dish": "String content",
"description": "String content"
},
{
"dish": "String content",
"description": "String content"
}
],
}
]
}
我正在使用PHP和mysql_query方法来弄清楚逻辑，我计划在生产版本中使用PDO。这是到目前为止我尝试过的代码。
//Create Our Query
$srtResult = "SELECT * FROM Restaurants";
//Execute Query
$result=mysql_query($srtResult);
//Iterate Throught The Results
while ($row = mysql_fetch_assoc($result)) {
$count = $row['id'];
$srtResult2 = "SELECT * FROM Dishes WHERE id = $count";
$result2 = mysql_query($srtResult2);
while(mysql_num_rows($result2)){
$dishes = mysql_fetch_row($result2);
$dishList[] = Array(
"dish" => $dishes[3],
"description" => $dishes[4]);
}
$json['Restaurants'][] =
Array("Restaurants" => Array(
"name" => $row['name'],
"Dishes" => Array(
$dishList)));
}
header('Content-type: application/json');
echo json_encode($json);
我的问题是，菜品不会根据当前餐厅的菜品进行迭代，对于每个餐厅菜品，我正在从第一家餐厅得到菜品。我认为问题出在循环本身，因为我在每个Restaurant包装器中得到的计数值都不同。任何帮助将不胜感激，我已经为此工作了几天，并且已经用尽了我的基本PHP知识。
您正在使用大量查询。为什么不在单个查询中这样做？
SELECT * FROM `Restaurants` `r`
LEFT JOIN `Dishes` `d` ON (`r`.`id` = `d`.`f_id`)
ORDER BY `r`.`id` ASC
然后使用结果来构建JSON对象。
EDIT 为了使迭代结果更容易，我将查询更改为：
SELECT
`r`.`id` as `restaurantId`,
`r`.`name`,
`r`.`info`,
`d`.`id` AS `dishId`,
`d`.`dish`,
`d`.`description`
FROM `restaurants` `r`
LEFT JOIN `dishes` `d` ON (`r`.`id` = `d`.`f_id`)
ORDER BY `r`.`id` ASC
结果将如下所示： restaurantId, name, info, dishId, dish, description
您现在可以像这样迭代结果：
$jsonArray = array();
foreach ($record as $dishDetails){
// details of the restaurant
$jsonArray[$dishDetails['f_id']]['name'] = $dishDetails['name'];
$jsonArray[$dishDetails['f_id']]['info'] = $dishDetails['info'];
// build the dishes of the restaurant
$jsonArray[$dishDetails['f_id']]['dishes'][$dishDetails['dishId']]['dish'] = $dishDetails['dish']
$jsonArray[$dishDetails['f_id']]['dishes'][$dishDetails['dishId']]['description'] = $dishDetails['description']
}
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句