来自PHP中两个MySQL表的Json [英] Json from two mysql tables in PHP

问题描述

我正在尝试从两个相关的MySQL表构建一个json输出. 我有一个餐厅"表和盘子"表，餐厅"表中的每个项目在盘子"表中都有几个由ID引用的相关项目.每个餐厅"商品ID都是菜肴"表中的外键，为f_id.

I am trying to build a json output from two related MySQL tables. I have a table of "Restaurants" and table "Dishes" each item in Restaurants table has several relative items in the Dishes table which are referenced by id. Each Restaurant item ID is a foreign key in the Dishes table as f_id.

例如: 餐厅表

+----+-------------+-----------+
| Id |    Name     | Misc Info |
+----+-------------+-----------+
|  1 | Restaurant1 | Some Info |
+----+-------------+-----------+

菜肴表

+----+------+-----------+-------------+
| Id | f_id |   dish    | description |
+----+------+-----------+-------------+
|  1 |    1 | DishName  | DishDesc.   |
|  2 |    1 | DishName2 | DishDesc.   |
+----+------+-----------+-------------+

我想从这些表创建一个JSON输出，如下所示:

I would like to create a JSON output from those to tables to look like this:

{
    "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.这是到目前为止我尝试过的代码.

I am using PHP and mysql_query methods to figure out the logic, I plan on using PDO int he production version. Here is the code I've tried so far.

//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包装器中获得的int数量都不同.任何帮助将不胜感激，我已经为此工作了几天，并用尽了我的基本PHP知识.

The problem I have is the dishes do not iterate according to the current restaurant item, for each restaurant item, I am getting dishes from the first restaurant. I think the problem lies in the loop itself since I am getting different int for count in each Restaurant wrapper. Any help would be much appreciated, I've been working on this for several days already and have exhausted my basic PHP knowledge.

推荐答案

您正在使用大量查询.为什么不在单个查询中做到这一点?

You are using extremely large numbers of queries. Why not doing it in a single query?

SELECT * FROM `Restaurants` `r`
    LEFT JOIN `Dishes` `d` ON (`r`.`id` = `d`.`f_id`)
ORDER BY `r`.`id` ASC 

，然后使用结果构建JSON对象.

and then use the result to build the JSON object.

EDIT 为了使迭代结果更容易，我将查询更改为:

EDIT To make it easier to iterate the result, I changed a bit the Query to:

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

您现在可以像这样迭代结果:

you can now iterate the result like this:

$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']
}

这篇关于来自PHP中两个MySQL表的Json的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！