Json from two mysql tables in PHP

Question

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.

for example: Restaurants table

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

Dishes table

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

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"
                }
            ],

        }
    ]
}

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);

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.

Answer 1

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 

and then use the result to build the JSON object.

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

the result will look like this: 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']
}

Answer 2

This is the valid solution I have used:

<?php
session_start();
error_reporting(E_ALL ^ E_NOTICE);
function __autoload($classname) {
    $filename = "classes/". $classname.".class.php";
    include_once($filename);
}`enter code here`
$recepie=new recepie(new database());
$recepies=mysql_query("SELECT * FROM recepies");
while ($recipe=mysql_fetch_assoc($recepies,MYSQL_ASSOC))
{
$rec_id=$recipe['rec_id'];
$ingredients=mysql_query("SELECT * FROM recepie_ingredients WHERE rec_id=".$rec_id);

unset($ing);
$ing= array();
  while($ingredient=mysql_fetch_assoc($ingredients,MYSQL_ASSOC))
    {
                    $ing[]=array("ing_id"=>$ingredient['ing_id'],
                               "ing_name"=>$ingredient['ing_name'],
                               "ing_amount"=>$ingredient['ing_amount'],
                                "ing_unit"=>$ingredient['ing_unit']);
    }

                     $json["Recepies"][]=array( "rec_id"=>$recipe['rec_id'], 
                                                "rec_name"=>$recipe['rec_name'],
                                                "rec_image"=>$recipe['rec_image'],
                                                "rec_createby"=>$recipe['rec_createby'],
                                                "rec_createdate"=>$recipe['rec_createdate'],
                                                "rec_description"=>$recipe['rec_description'],
                                                "rec_Ingredients"=>$ing );


}
echo json_encode($json);   

?>