CODEWITHSUNDEEP
php
hunter
hunter

Reputation: 1101

how to extract data json format

i retrieved data from mysql tables in json using php i.e

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


$set=array();
while ($link = mysql_fetch_array($resouter, MYSQL_ASSOC)){
foreach ($link as $fieldname => $fieldvalue){
    $set[]= $fieldvalue;}
 $query2="SELECT ingredients.ingredient_id,ingredients.ingredient_name,ingredients.ammount FROM ingredients where rec_id = ".$link['rec_id'];
$result2 = mysql_query($query2, $conn);

 while ($rs = mysql_fetch_array($result2, MYSQL_ASSOC)){
     foreach($rs as $fieldname =>$fieldvalue){
         $set[]=$fieldvalue;
     }

 }

}
echo json_encode($set);

The result of the code is 

["14","Spaghetti with Crab and Arugula","http:\/\/www","","2010-11-11 14:35:11","localhost\/pics\/SpaghettiWithCrabAndArugula.jpg",
"7","13 ounces spaghetti","10 kg",
"8","1 pound crabmeat","10"]

Note: The ingredients id starts after the image tag. 7 is the ingredient id followed by two fields "ingredients txt and amount" then 8 is another ingredient id relevant to the recipe id. like there is no ({) open or (}) close bracket in my result.

what i want to do is to output it in a correct json format. i.e

[
 {
  "rec_id": "14",
  "name":"Spaghetti with Crab and Arugula",
  "overview":"http:\/\/www",
  "category":"category",
                "time":"2010-11-11 14:35:11",
                "image":"localhost\/pics\/SpaghettiWithCrabAndArugula.jpg"
  "ingredients":
   {
    "ingredient":
     [                       {"ingredient_id":"7","ingredient_name":"13ounces spaghetti","amount":"10kg" },
{ "ingredient_id": "8", "ingredient_name": "1 pound crabmeat","amount":"10kg" },

     ]
   }]

and same for recipe id 15 and so on.......

so how can get this....!! any suggestions

Upvotes: 0

Views: 347

Answers (1)

timdev
timdev

Reputation: 62894

You're outputting perfectly valid json.

So look at how you're constructing $set ... see the problem yet?

You're just pushing scalar values onto an array, so it should be no surprise that when you json-encode it you get a long array of scalars, with no structure. Your code is actively destroying the structure you want!

I could fix your code for you, but I'm not going to. You need to look at your queries and loops, and figure out what's going on.

You basically want to do something like this:

$result = array();
$recipes = mysql_query('....');
while($recipe = mysql_fetch_assoc($recipes)){
    $result[$recipe['id']] = $recipe;

    $ingredients = mysql_query('...');
    while($ingredient = mysql_fetch_assoc($ingredients)){
        $result[$recipe['id']] = $ingredient;
    }
}

var_dump($result); //or echo json_encode($result);

See the differences?

Upvotes: 2

Related Questions