mysql的json结构比较,MySQL的Json菜单结构

I have been going round and round with this issue, seemed very simple. I am trying to produce a menu/submenu json out put with PHP/MYSQL: These are the tables:

CREATE TABLE `menuHome`

`id`,

`titleName`

CREATE TABLE `menu`

`id`,

`parentmenu`,

`name`

'menuHome' would have 'titleName' such as 'About Us' joined by 'id' on 'menu' by 'parentmenu' this would have multiple entries such as 'history', 'Owners', 'News'.

The structure I am trying to achieve is:

`

{

"menu" : {

"sections" : [

{

"title" : "About Us",

"items" : [

{

"name" : "History",

"id" : "0909"

},

{

"name" : "Owners",

"id" : "0910"

},

{

"name" : "News",

"id" : "0916"

}

]

},

{

"title" : "Contact Us",

"items" : [

{

"name" : "Address",

"id" : "0949"

},

{

"name" : "Map",

"id" : "0978"

}

]

},

{

"title" : "Products",

"items" : [

{

"name" : "Jeans",

"id" : "1010"

},

{

"name" : "Tables",

"id" : "1088"

},

{

"name" : "Shoes",

"id" : "2424"

}

]

}

]

}

}

`

I have tried, while, for, for each in lots of configurations but I can not make it build the right array structure to encode to json. I am now at the code blindness stage so any help would be greatly received

this was my last attempt:

$sql_query = 'SELECT menuHome.titleName, menuHome.id FROM menuHome';

$result = $mysqli->query($sql_query);

$menu = array();

while ($row = $result->fetch_assoc()) {

$menuid = $row["id"];

$sql_query2 = 'SELECT menu.name, menu.id FROM menu WHERE menu.parentmenu = "' . $menuid . '"';

$result2 = $mysqli->query($sql_query2);

while ($row2 = $result2->fetch_assoc()) {

$menu[$row["titleName"]][] = $row2;

}

}

return json_encode($menu);

This is the result the above brings:

{

"About Us" : [

{

"name" : "History",

"id" : "1"

},

{

"name" : "Owners",

"id" : "2"

},

{

"name" : "News",

"id" : "3"

}

],

"Contact Us" : [

{

"name" : "Address",

"id" : "4"

},

{

"name" : "Map",

"id" : "5"

}

],

"Products" : [

{

"name" : "Jeans",

"id" : "6"

},

{

"name" : "Tables",

"id" : "7"

},

{

"name" : "Shoes",

"id" : "8"

}

]

}

The problem is I can not get the 'pairs' prior - "title" = "About us"...it's something simple but I am brain dead over this issue.

I can add this before json_encode:

$menuoutput = array("menu" => array("sections" => array($menu)));

But how do I get the 'key' 'titleName' or just 'title' to display before 'About Us'...

解决方案First off I assume your data goes like this -

INSERT INTO menu

(id, parentmenu, name)

VALUES

(1,1,"history"),

(2,1,"owners"),

(3,1,"news"),

(4,2,"address"),

(5,2,"map"),

(6,3,"jeans"),

(7,3,"tables"),

(8,3,"shoes");

INSERT INTO menuHome

(id,titleName)

VALUES

(1,"About Us"),

(2,"Contact Us"),

(3,"Products");

secondly i will suggest you to change your query to -

SELECT pm.id, cm.id, pm.titleName, cm.name FROM menu as cm

LEFT JOIN (menuHome as pm)

ON (pm.id = cm.parentmenu);

third, lets get to coding

for our purposes we want something like this -

$arys = ["menu"=>["sections" => [

["title" => "About Us", "items" => [

["name"=>"History","id"=>1],

["name"=>"Owners","id"=>2],

["name"=>"News","id"=>3]

]],

["title" => "Contact Us", "items" => [

["name"=>"Address","id"=>4],

["name"=>"Map","id"=>5]

]],

["title" => "Products", "items" => [

["name"=>"Jeans","id"=>6],

["name"=>"Tables","id"=>7],

["name"=>"Shoes","id"=>8]

]]

]

]

];

So let's build it up

$querys = "Select pm.id AS pmd , cm.id AS cmd, pm.titleName AS pmt, cm.name AS cmt from menu as cm left join (menuHome as pm) on (pm.id = cm.parentmenu) Order By pmd, cmd ";

$result = mysql_query($querys);

$jsary = ["menu" => ["sections" => []]];

$lastPid = 0;

$currentPid = 0;

$title = "";

$ifff = 0;

$elss = 0;

while($row = mysql_fetch_array($result))

{

$currentPid = $row['pmd'];

$title = $row['pmt'];

$cmd = $row['cmd'];

$cmt = $row['cmt'];

if($lastPid != $currentPid)

{

$insAry = [];

$insAry = ["title"=> $title, "items" => [["name" => $cmt, "id" => $cmd]]];

array_push($jsary["menu"]["sections"], $insAry);

$lastPid = $currentPid;

$ifff = $ifff + 1;

$currentPid = 0;

}

else

{

$ind = 0;

if($ifff > 0)

{

$ind = $ifff-1;

}

$insAry = [];

$insAry = ["name" => $cmt, "id" => $cmd];

array_push($jsary["menu"]["sections"][$ind]["items"], $insAry);

}}

Now just call json_encode()

json_encode($jsary);

You'll have your desired output