如何提取数据json格式

Question

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

Answer 1

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?