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.

Dragos · Accepted Answer · 2014-02-19 11:18:34Z

2

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

edited Feb 19, 2014 at 11:18

answered Feb 18, 2014 at 20:26

Dragos

1,93416 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Anton Over a year ago

Thank you for your response, I tried your query. While it works for getting the data that I need however, with that query I get a new row of restaurant for each dish associated with it. When I put it in this loop, I get a new restaurant for each dish, however, I need an array of dishes for each restaurant item.

Anton Over a year ago

Here is the code I am using now

while ($row = mysql_fetch_assoc($result)) { 	$count = $row['id']; 	while($row['f_id'] = $count){ 		$dishList[] = Array( 			"dish" => $row['dish'], 			"description" => $row['description']); 	} 	$json['Restaurants'][] =  		Array("Restaurant" => Array( 				"name" => $row['name'],  				"longitude" => $row['longitude'],  				"latitude" => $row['latitude'],  		"Dishes" => Array( 			$dishList))); }

Dragos Over a year ago

The result you're getting from this query is completely right. All you need to do is parse the array. It's faster and more efficient to parse an array (working with data in the memory) rather than send a new query for each of the results in the first query.

Anton Over a year ago

I understand that the problem lies in my iteration loop. I've tired out my logic hat, could you point me in a right direction with how I should build my loop? Any help will be greatly appreciated!

Dragos Over a year ago

There are at least 2 or 3 ways you can approach this iteration of the result. You can group the result based on the Restaurant ID, or use the Restaurant ID as the key for an intermediate array. If you can't get it done now, I will come back in about 20 hours to post a code suggestion on how to iterate/parse the result. I don't have too much time now, otherwise I would post it now.

|

wogsland · Accepted Answer · 2016-02-19 04:35:45Z

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

?>

Collectives™ on Stack Overflow

Json from two mysql tables in PHP

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related