Multi-dimensional Array - mysql_fetch_assoc to Json

Question

I have used MySql_fetch_assoc and then json_encode

    $type = mysql_query("SELECT type_food,category_food,name_food FROM menu ORDER BY type_food,category_food,name_food"); 
    $rows = array();
    while($t = mysql_fetch_assoc($type)) { 
          $rows[]= $t;      
    }
print json_encode($rows);

which gives me the following results:

    {"type_food":"pizza","category_food":"Gourmet","name_food":"pepperoni"},
{"type_food":"pizza","category_food":"Gourmet","name_food":"supreme"},
{"type_food":"pizza","category_food":"Gourmet","name_food":"hawaiian"},
{"type_food":"pizza","category_food":"Gourmet","name_food":"tropical"},
{"type_food":"pizza","category_food":"traditional","name_food":"margherita"},
{"type_food":"pizza","category_food":"traditional","name_food":"vegetarian"}

However I need to make a multidimensional json result like so:

{"pizza": [
    {"Gourmet": [
                {"pepperoni"},
                {"supreme"},
                {"hawaiian"},
                {"tropical"}]},
    {"traditional": [
                {"margherita"},
                {"vegetarian"}]}
]},
{"etc": [ ... ]}

Is this possible using MySQL associative arrays?

you should post your PHP code of how you came up with your result. — kennypu, Mar 05 '13 at 04:21
You could use [`GROUP_CONCAT()`](http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat) in your mysql query (ie. `GROUP_CONCAT(name_food) ... GROUP by category_food`) and then `explode()` the `GROUP_CONCAT(name_food)` array. — Sean, Mar 05 '13 at 04:29
mysql results are typically flat, not structured like a tree. I think you'll need to build that in a loop. — Kevin Seifert, Mar 05 '13 at 04:39
A loop becomes very messy and somehow still does not produce the desired result unless of course I echo it out to the way it should be and not use json_encode — g r, Mar 05 '13 at 05:41
Group_concat() may work. I cant seem to get it producing all results. Also it produces all of name food with a separator not a position in an array — g r, Mar 05 '13 at 05:46
Look at [this answer](http://stackoverflow.com/a/37347651/6361173) by me. Should be what you need. — Giorgio Zanetti, May 20 '16 at 13:32

score 1 · Answer 1 · answered Mar 05 '13 at 04:41

<?php
$str='[{"type_food":"pizza", "category_food":"Gourmet", "name_food":"pepperoni"},
{"type_food":"pizza","category_food":"Gourmet","name_food":"supreme"},
{"type_food":"pizza","category_food":"Gourmet","name_food":"hawaiian"},
{"type_food":"pizza","category_food":"Gourmet","name_food":"tropical"},
{"type_food":"pizza","category_food":"traditional","name_food":"margherita"},
{"type_food":"pizza","category_food":"traditional","name_food":"vegetarian"}]';
$rows=json_decode($str,true);
$res=array();
foreach($rows as $row)
{
  $res[$row['type_food']][$row['category_food']][]=$row['name_food'];
}
echo json_encode($res);
?>

score 1 · Accepted Answer · answered Mar 05 '13 at 06:07

Try this:

$type = mysql_query("SELECT type_food,category_food,name_food FROM menu ORDER BY type_food,category_food,name_food"); 
$rows = array();
while($t = mysql_fetch_array($type)) { 
$rows[$t['type_food']][$t['category_food']][]=$t['name_food'];
}
print json_encode($rows);

Multi-dimensional Array - mysql_fetch_assoc to Json

2 Answers2