How to retrieve mysql data into json using php

hi all i have 3 table in mysql database related to foreign key i want to extract the data from mysql in json i.e


[ "table1"
	{
		"tab1_id": "1",
		"name": "donut",
		"overview": "Cake",
		"category": 4,
                "image": imageURL,
		"table2":
			{
				"table_name":
					[
						{ "id": "1","text":"sometext","amount":"4" },
                                                { "id": "2","text":"sometext","amount":"3" },
						{ "id": "3","text":"sometext","amount":"4" },
						{ "id": "4","text":"sometext","amount":"4" },
						
					]
			},
		"table3":
			[
                            "table_name"
       				{ "id": "1", "text": "sometext" },
				{ "id": "2", "text": "sometext" },
				{ "id": "3", "text": "sometext" },
			]
	}]

i have search for this for last two days but could’t get the clue
any suggestion or help would be greatly appreciated.

It looks like your data structure is broken. What it should look like is for there to be three table properties, each which have their own data. It’s not normally a good idea to have name1, name2, name3, as in the table names. Instead you should just use array indexes instead.

For example, $data could be


[
    {
        "tab1_id": "1",
        ...
    },
    {
        "table_name":
            [...]
    },
    {
        "table_name":
            [...]
    }
]

So the first table could be $data[0], the second table $data[1] and the third table $data[2]

If you really want to use table1, table2, table3 then your JSON data could be like this:


[
    "table1": {
        "tab1_id": "1",
        ...
    },
    "table2": {
        "table_name":
            [...]
    },
    "table3": {
        "table_name":
            [...]
    }
]

Then you can retrieve the data with $data[‘table1’], the second table $data[‘table2’] and the third table $data[‘table3’]

but how can i get the resulted json from mysql using php.

my code is

$result=array();
$table_first = 'recipe';
$query = "SELECT * FROM $table_first";
$resouter = mysql_query($query, $conn);



while ($recipe = mysql_fetch_assoc($resouter, MYSQL_ASSOC)){
   $result['recipe']=$recipe;

$query2="SELECT ingredients.ingredient_id,ingredients.ingredient_name,ingredients.ammount FROM ingredients where rec_id = ".$recipe['rec_id'];
$result2 = mysql_query($query2, $conn);

 while($ingredient = mysql_fetch_assoc($result2)){


        $result['ingredient']= $ingredient;

 }
  //echo json_encode($result);
}
  echo json_encode($result);

but it show only one record

{"recipe":{"rec_id":"23","name":"this is test recipe for category","overview":"category overview","category":"4","time":"2002-12-10 13:30:39","image":"http:\\/\\/www.localhost\\/cafe\\/pics\\/logout (1).gif"},"ingredient":{"ingredient_id":"59","ingredient_name":"test recipe","ammount":"4 gm"}}

You can go to the website to find out. Really.
http://www.json.org/
Scroll down and you’ll find many PHP JSON libraries.

However, it’s also built in to PHP 5.2 onwards as well, where you can use json_deconde() to decode it.

sir i want to encode mysql data into json

$result=array();
$table_first = 'recipe';
$query = "SELECT * FROM $table_first";
$resouter = mysql_query($query, $conn);
while ($recipe = mysql_fetch_assoc($resouter, MYSQL_ASSOC)){
   $result[]=$recipe;
}

this is for retrieving data from one table in json but what should i do if i want to retrieve from two table or more(related to each other using foreign key).

Then use json_encode

That depends on how you want to process them once they are decoded. The easiest way is for each table to be a single array item in a larger array.


$data = array();
array_push($array1);
array_push($array2);
$json = json_encode($data);

Sir i used this code to retrieve data from two tables

$result=array();
$table_first = 'recipe';
$query = "SELECT * FROM $table_first";
$resouter = mysql_query($query, $conn);



while ($recipe = mysql_fetch_assoc($resouter, MYSQL_ASSOC)){
   $result['recipe']=$recipe;

   
$query2="SELECT ingredients.ingredient_id,ingredients.ingredient_name,ingredients.ammount FROM ingredients where rec_id = ".$recipe['rec_id'];
$result2 = mysql_query($query2, $conn);

 while($ingredient = mysql_fetch_assoc($result2)){
     
     
        $result['ingredient']= $ingredient;
     
 }
  echo json_encode($result);
}

But this retrieved only last ingredient for first recipe id and there are 5 relevant records in ingredient table against this recipe id i.e
the out put is

{"recipe":{"rec_id":"14","name":"Spaghetti with Crab and Arugula","overview":"http:\\/\\/www","category":"","time":"2010-11-11 14:35:11","image":"localhost\\/pics\\/SpaghettiWithCrabAndArugula.jpg"},"ingredient":{"ingredient_id":"55","ingredient_name":"test","ammount":"2 kg"}}{"recipe":{"rec_id":"15","name":"stew recipe ","overview":"http:\\/\\/www","category":"","time":"2010-11-11 14:42:09","image":"localhost\\/pics\\/stew2.jpg"},"ingredient":{"ingredient_id":"25","ingredient_name":"3 parsnips cut into cubes","ammount":"11"}}

The fault is of this line here:


$result['recipe']=$recipe;

Each time that line is executed, it replaces whatever used to be there.

If you want to add each recipe to the result array, set up an empty array for the results:


$result = array(
    'recipe' => array(),
    'ingredient' => array()
);

then use the notation


$result['recipe'][] = $recipe;

or array_push(), whichever you like better.


array_push($result['recipe'], $recipe);

Thanks for your deep attention sir i used your above code but still it does’t work
i want the format should like ( see the my question post) but it shows the data like

{“recipe”:[{“rec_id”:“14”,“name”:“Spaghetti with Crab and Arugula”,“overview”:“http:\/\/www”,“category”:“”,“time”:“2010-11-11 <br/>14:35:11”,“image”:“localhost\/pics\/SpaghettiWithCrabAndArugula.jpg”}],<br/>“ingredient”:,“0”:{“ingredient”:{“ingredient_id”:“7”,“ingredient_name”:“13 ounces spaghetti”,“ammount”:“10 kg”}},“1”:{“ingredient”:{“ingredient_id”:“8”,“ingredient_name”:“1 pound crabmeat”,“ammount”:“10”}},“2”:{“ingredient”:{“ingredient_id”:“9”,“ingredient_name”:“7 ounces arugula, washed”,“ammount”:“10”}},“3”:<br/>{“ingredient”:{“ingredient_id”:“10”,“ingredient_name”:“2 cloves garlic, finely chopped”,“ammount”:“10”}},“4”:{“ingredient”:{“ingredient_id”:“11”,“ingredient_name”:“1 chili pepper, seeded and finely chopped”,“ammount”:“10”}},“5”:{“ingredient”:{“ingredient_id”:“12”,“ingredient_name”:“4 tablespoons lemon juice”,“ammount”:“10”}},“6”:<br/>{“ingredient”:{“ingredient_id”:“13”,“ingredient_name”:“6 tablespoons olive oil”,“ammount”:“10”}},“7”:{“ingredient”:{“ingredient_id”:“14”,“ingredient_name”:“Salt and pepper to taste”,“ammount”:“10”}},“8”:{“ingredient”:{“ingredient_id”:“55”,“ingredient_name”:“test”,“ammount”:“2 kg”}}}<br/>{“recipe”:[{“rec_id”:“14”,“name”:“Spaghetti with Crab and Arugula”,“overview”:“http:\/\/www”,“category”:“”,“time”:“2010-11-11 14:35:11”,“image”:“localhost\/pics\/SpaghettiWithCrabAndArugula.jpg”},

i think sir we are near to the solution because the relevant ingredients are now retrieved but there is little problem.

i used

while ($recipe = mysql_fetch_assoc($resouter, MYSQL_ASSOC)){
   $result['recipe'][]=$recipe;

and

while($ingredient = mysql_fetch_assoc($result2)){


        $result[]['ingredient']= $ingredient;

Can you explain the problem?

The problem is it give me first the recipe array and then ingredients and what i want to do is ingredient array should be inside recipe array.
also this print out some extra records i.e (see my out put quote).

Will the JSON data contain multiple recipies, or will there only ever be one recipe that the JSON data handles.

well sir i edit my code a little and it fulfill my requirement, like it retrieve first recipe then all the ingredient against that specific recipe and also all the instruction.
But the problem now is just the json format.
i show you my queries and my output

$result=array();
$table_first = 'recipe';
$query = "SELECT * FROM $table_first";
$resouter = mysql_query($query, $conn);
while ($recipe = mysql_fetch_assoc($resouter, MYSQL_ASSOC)){
   $result[]=$recipe;


   
$query2="SELECT ingredients.ingredient_id,ingredients.ingredient_name,ingredients.ammount FROM ingredients where rec_id = ".$recipe['rec_id'];
$result2 = mysql_query($query2, $conn);

 while($ingredient = mysql_fetch_assoc($result2)){
     
     
        $result[]= $ingredient;
     
 }
$query="SELECT instructions.instruction_id,instructions.instruction_text FROM instructions where rec_id = ".$recipe['rec_id'];
$resinner=mysql_query($query, $conn);
while($instruction=  mysql_fetch_assoc($resinner))
{
    $result[]=$instruction;
}
  }
 echo json_encode($result);

and the result for recipe id 14 is

[{“rec_id”:“14”,“name”:“Spaghetti with Crab and Arugula”,“overview”:“http:\/\/www”,“category”:“”,“time”:“2010-11-11 14:35:11”,“image”:“localhost\/pics\/SpaghettiWithCrabAndArugula.jpg”},{“ingredient_id”:“7”,“ingredient_name”:“13 ounces spaghetti”,“ammount”:“10 kg”},{“ingredient_id”:“8”,“ingredient_name”:“1 pound crabmeat”,“ammount”:“10”},{“ingredient_id”:“9”,“ingredient_name”:“7 ounces arugula, washed”,“ammount”:“10”},{“ingredient_id”:“10”,“ingredient_name”:“2 cloves garlic, finely chopped”,“ammount”:“10”},{“ingredient_id”:“11”,“ingredient_name”:“1 chili pepper, seeded and finely chopped”,“ammount”:“10”},{“ingredient_id”:“12”,“ingredient_name”:“4 tablespoons lemon juice”,“ammount”:“10”},{“ingredient_id”:“13”,“ingredient_name”:“6 tablespoons olive oil”,“ammount”:“10”},{“ingredient_id”:“14”,“ingredient_name”:“Salt and pepper to taste”,“ammount”:“10”},{“ingredient_id”:“55”,“ingredient_name”:“test”,“ammount”:“2 kg”},{“instruction_id”:“14”,“instruction_text”:“Cook spaghetti according to directions on package. Drain and set aside.”},{“instruction_id”:“15”,“instruction_text”:“In a large saucepan, heat olive oil. Add garlic and chili and saute for 30 seconds. Stir in crabmeat and lemon juice. Season with salt and pepper. Cook for 3-5 minutes, stirring constantly. Add arugula leaves.”},{“instruction_id”:“16”,“instruction_text”:“Add pasta and toss to coat. Adjust taste with salt and pepper if necessary. Serve immediately.”}

i just want to the ingredient array and instruction array should be inside the recipe array but look at my result when the recipe array finished then the ingredient array start
i want this to be inside in recipe array i.e

{“rec_id”:“14”,“name”:“Spaghetti with Crab and Arugula”,“overview”:“http:\/\/www”,“category”:“”,“time”:“2010-11-11 14:35:11”,“image”:“localhost\/pics\/SpaghettiWithCrabAndArugula.jpg”,
{then ingredient close here } and { instruction and recipe close here }}

So you don’t need to start with an array at all.

You can use:


$result = $recipe;

Then append the ingredients on to it:


$result[] = $instruction;

The problem has nothing to do with JSON. The problem is that you don’t have the rpoper data structure to begin with. Once the proper data format is achieved than json_encode() will do the dirty work of converting it to JSON for you. Though, you must first have the intended array structure.

is your friend:


echo '<pre>',print_r($array_value),'</pre>';

This will out put just a single recipe no ingredients and no instruction is there.

Just noticed: that should be:


$result['ingredients'][] = $instruction;

Then please guide me how to achieve the correct format cause it suffer me for the couple of days and still unable to get.

Both does’t work pmw57
it just the problem of closing brace of recipe table.

Its pretty basic stuff. Trial and error. Like I said, you can use print_r() to check the data structure being built. Take a little initiative.