PHP Array to JSON formatted object -

PHP Array to JSON formatted object -

i think need pair of eyes review code. cant quite right combination of array statements json object want.

$sql = "select categories.category_name category_id, sub_categories.sub_category_name subcategory_id, items.id item_id, items.item_name categories left bring together sub_categories on (categories.id = sub_categories.category_id) left bring together items on (sub_categories.id = items.sub_category_id) categories.site_id = 1 order category_id,subcategory_id"; $result1 = mysqli_query ($dbc, $sql) or trigger_error("query: $sql\n<br />mysql error: " . mysqli_error($dbc)); if($result1 === false) { echo(mysqli_error()); // todo: improve error handling } else { $tempcategoryarray = array(); $tempsubcategoryarray = array(); $categoriesarray = array("categories" => array()); $category = ""; $sub_category = ""; $newcat= false; $newsubcat = false; while($row = mysqli_fetch_array($result1)) { // if have new category, let's start anew if($row['category_id'] != $category) { $tempcategoryarray['category_id'][]= $row['category_id']; // add together temporary array main 1 if(!empty($tempcategoryarray)) { array_push ($categoriesarray['categories'], $tempcategoryarray); } $tempcategoryarray = array(); $tempcategoryarray['subcategories'] = array(); } $category = $row['category_id']; // same here, if new sub, let's start fresh if($row['subcategory_id'] != $sub_category) { $tempsubcategoryarray['subcategory_id'][] = $row['subcategory_id']; $tempsubcategoryarray['items'] = array(); // add together tempcategory if(!empty($tempsubcategoryarray)) { array_push ($tempcategoryarray['subcategories'], $tempsubcategoryarray); } } { $tempsubcategoryarray['items'] = array(); array_push($tempsubcategoryarray['items'],array('item_name'=> $row['item_name'], 'item_id'=> $row['item_id'], 'item_qty'=> 0)); array_push($tempcategoryarray,$tempsubcategoryarray['items']); unset($tempsubcategoryarray); } // finally, no need temporary arrays items, since have no sub- array $sub_category= $row['subcategory_id']; } array_push ($categoriesarray['categories'], $tempcategoryarray); } //var_dump($categoriesarray); $categoryjson = json_encode($categoriesarray); echo $categoryjson; //echo '<script src="js/menupages.js"></script>'; // clean up: mysqli_free_result($result1); ?>

this object getting , variations of when create changes cant targeted json object

current json

{ "categories":[ { "category_id":[ "drinks" ] }, { "subcategories":[ { "subcategory_id":[ "beer" ], "items":[ ] }, { "subcategory_id":[ "wine" ], "items":[ ] } ], "0":[ { "item_name":"miller lite", "item_id":"5", "item_qty":0 } ], "1":[ { "item_name":"yuengling", "item_id":"6", "item_qty":0 } ], "2":[ { "item_name":"bud select", "item_id":"7", "item_qty":0 } ], "3":[ { "item_name":"white zin", "item_id":"8", "item_qty":0 } ], "4":[ { "item_name":"reistling", "item_id":"10", "item_qty":0 } ], "category_id":[ "food" ] }, { "subcategories":[ { "subcategory_id":[ "sandwiches" ], "items":[ ] } ], "0":[ { "item_name":"hamburger", "item_id":"9", "item_qty":0 } ], "1":[ { "item_name":"hot dog", "item_id":"11", "item_qty":0 } ] } ] }

the json want

{ "categories":[ { "category_id":[ "drinks" ] }, { "subcategories":[ { "subcategory_id":[ "beer" ], "items":[ [ { "item_name":"miller lite", "item_id":"5", "item_qty":0 } ], [ { "item_name":"yuengling", "item_id":"6", "item_qty":0 } ], [ { "item_name":"bud select", "item_id":"7", "item_qty":0 } ] ] }, { "subcategory_id":[ "wine" ], "items":[ [ { "item_name":"white zin", "item_id":"8", "item_qty":0 } ], [ { "item_name":"reistling", "item_id":"10", "item_qty":0 } ] ] } ], "category_id":[ "food" ] }, { "subcategories":[ { "subcategory_id":[ "sandwiches" ], "items":[ [ { "item_name":"hamburger", "item_id":"9", "item_qty":0 } ], [ { "item_name":"hot dog", "item_id":"11", "item_qty":0 } ] ] } ] } ] }

php json