这是早期帖子的后续内容 .

这是我的数据库中的数据

category_id subcategory_id  item_id item_name
Drinks  Beer    5   Miller Lite
Drinks  Beer    6   Yuengling
Drinks  Beer    7   Bud Select
Drinks  Wine    8   White Zin
Food    Sandwiches  9   Hamburger

Individual tables as JSON

Categories
[{"id": 1,"category_name": "Drinks","active": 1,"site_id": 1}, {"id": 2,"category_name": "Food","active": 1,"site_id": 1}]

Subcategories
[{"id": 1,"sub_category_name": "Beer","active": "","site_id": 1,"category_id": 1}, {"id": 2,"sub_category_name": "Wine","active": "","site_id": 1,"category_id": 1}, {"id": 3,"sub_category_name": "Sandwiches","active": "","site_id": 1,"category_id": 2}]

Items
[{"id": 5,"item_name": "Miller Lite","item_max_qty": 4,"active": "","site_id": 1,"sub_category_id": 1}, {"id": 6,"item_name": "Yuengling","item_max_qty": 4,"active": "","site_id": 1,"sub_category_id": 1}, {"id": 7,"item_name": "Bud Select","item_max_qty": 4,"active": "","site_id": 1,"sub_category_id": 1}, {"id": 8,"item_name": "White Zin","item_max_qty": 4,"active": "\0","site_id": 1,"sub_category_id": 2}, {"id": 9,"item_name": "Hamburger","item_max_qty": 4,"active": "\0","site_id": 1,"sub_category_id": 3}]

我的代码显示在这里，你可以看到我运行select并期望代码循环我的上面的结果集，并在完成所有操作后将所有内容转换为JSON .

$sql = "SELECT categories.category_name AS category_id, sub_categories.sub_category_name AS subcategory_id, items.id AS item_id, items.item_name
    FROM categories
    LEFT JOIN sub_categories ON (categories.id = sub_categories.category_id)
    LEFT JOIN items ON (sub_categories.id = items.sub_category_id)
    WHERE categories.site_id = 1
    ORDER BY category_id,subcategory_id";


$result1 = mysqli_query ($dbc, $sql) or trigger_error("Query: $sql\n
MySQL Error: " . mysqli_error($dbc));

if($result1 === FALSE)
{
echo(mysqli_error()); // TODO: better error handling
} else
{



$tempCategoryArray = array();
$tempSubCategoryArray = array();

$categoriesArray = array("categories" => array());
$category = "";
$sub_category = "";

while($row = mysqli_fetch_array($result1)) 


    {

    // If we have a new category, let's start anew

    if($row['category_id'] != $category) 
            {

            // Add the temporary array to the main one
                if(!empty($tempCategoryArray)) 
                    {
                    array_push ($categoriesArray['categories'], $tempCategoryArray);

                    }


                //$tempCategoryArray = array();
                unset($tempCategoryArray );
                $tempCategoryArray['category_id'] = $row['category_id'];

                $tempCategoryArray['subcategories'] = array();
                $category = $row['category_id'];
            }
    // Same here, if a new sub, let's start fresh

    if($row['subcategory_id'] != $sub_category) 
            {
            // Add it to the tempCategory
            $tempSubCategoryArray['subcategory_id'][] = $row['subcategory_id'];
                if(!empty($tempSubCategoryArray)) 
                    {
                    array_push   ($tempCategoryArray['subcategories'],       $tempSubCategoryArray);

                    }
                //$tempSubCategoryArray = array();
                unset($tempSubCategoryArray);
            //  $tempSubCategoryArray['subcategory_id'][] =     $row['subcategory_id'];

            $tempSubCategoryArray['items'] = array();

                $sub_category= $row['subcategory_id'];
            }

    // Finally, no need for temporary arrays with items, since they have no sub-    array
    array_push($tempSubCategoryArray['items'],array('item_name'=>     $row['item_name'], 'item_id'=> $row['item_id'], 'item_qty'=> 0));

    }

}

$categoryJson = json_encode($categoriesArray);

echo $categoryJson;

但是它没有完成所有行 .

{“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”：[ “葡萄酒”]}]}]}

我究竟做错了什么？

php mysql json