php - 从 php 和 mysql 构建 json 的最有效方法

Question

我的目标 JSON 对象看起来像这样的结构

[
   {
  "categories":[
     {
        "CATEGORY_ID":"Drinks",
        "subcategorys":[
           {
              "subcateory_ID":"beer",
              "items":[
                 {
                    "item_NAME":"yuengling",
                    "item_id":1
                 },
                 {
                    "item_NAME":"miller lite",
                    "item_id":2
                 }
              ],
              "subcateory_ID":"wine",
              "items":[
                 {
                    "item_NAME":"white zin",
                    "item_id":3
                 }
              ]
           }
        ]
     },
     {
        "CATEGORY_ID":"food",
        "subcategorys":[
           {
              "subcateory_ID":"sandwiches",
              "items":[
                 {
                    "item_NAME":"hamburger",
                    "item_id":1
                 },
                 {
                    "item_NAME":"ham & cheese",
                    "item_id":2
                 }
              ],
              "subcateory_ID":"sides",
              "items":[
                 {
                    "item_NAME":"fries",
                    "item_id":3
                     }
                  ]
               }
            ]
         }
      ]
   }
]

我的sql查询是这样的

SELECT categories.id, category_name FROM categories WHERE site_id = 1

SELECT sub_categories.id, sub_category_name FROM sub_categories WHERE site_id = 1 AND category_id = 1

SELECT items.id, item_name FROM items WHERE site_id = 1 AND sub_category_id = 1 AND site_id = 1

我一直在努力寻找在每次选择后使用嵌套循环在 php 中创建此 json 的最有效方法。

这是我开始的路

  $query1 = "SELECT categories.id, category_name FROM categories WHERE site_id = ".$siteId;

  $result1 = mysqli_query ($dbc, $query1) or trigger_error("Query: $query1\n<br />MySQL Error: " . mysqli_error($dbc));

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

$categoriesJson = ' [ {"categories":[{'

while( $row = mysqli_fetch_array($result1) )
{
    $category = $row['category_name'];
    $categoriesJson = $categoriesJson.'"CATEGORY_ID":"'.$category.'","subcategorys":[{';


    // Make the query:
    $query2 = "SELECT sub_categories.id, sub_category_name FROM sub_categories WHERE site_id = ".$siteId . " AND category_id = ". $row['id'];
    $result2 = mysqli_query ($dbc, $query2) or trigger_error("Query: $query2\n<br />MySQL Error: " . mysqli_error($dbc));
    if($result2 === FALSE)
    {
        echo(mysqli_error());
    }
    else
    {
        while( $row2 = mysqli_fetch_array($result2) )
        {
            $subcategory = $row2['sub_category_name'];
            $categoriesJson = $categoriesJson.'"subcateory_ID":"'.$subcategory.'","items":[{';


            $query3 = "SELECT items.id, item_name FROM items WHERE site_id = ".$siteId . " AND sub_category_id = ". $row2['id'] . " AND site_id = ".$siteId;
            $result3 = mysqli_query ($dbc, $query3) or trigger_error("Query: $query3\n<br />MySQL Error: " . mysqli_error($dbc));
            if($result3 === FALSE)
            {
                echo(mysqli_error());
            } else
            {

                while( $row3 = mysqli_fetch_array($result3) )
                {
                $itemname = $row3['item_name'];
                $itemid = $row3['id'];
                $categoriesJson = $categoriesJson.'"itemName":"'.$itemname.'","itemId":"'.$itemid.'"';



                }
                $categoriesJson = $categoriesJson.'}]';
            }

        }

        $categoriesJson = $categoriesJson.'}]';

    }
    $categoriesJson = $categoriesJson.'}]';
}

Answer 1

此特定代码未经测试，但总体思路已经过良好测试，我希望它不会太难理解。这个想法是以正确的方式构建数组，然后使用 json_encode。如果我们已经有了那个类别/子类别，通过检查每一行，我们确保我们得到了正确的格式。由于您不在 json 中使用标识符(例如 Drinks: {...}，我们必须使用临时数组并在填充后添加它们。

您还可以寻找更通用的代码，在 google 中搜索 php pivot 函数。

<?php
$sql = "SELECT categories.category_name AS CATEGORY_ID, sub_categories.sub_category_name AS subcategory_ID, items.id, items.item_name 
        FROM categories
        LEFT JOIN sub_categories ON (categories.id = sub_categories.category_id and sub_categories.site_id = 1)
        LEFT JOIN items ON (sub_categories.id = items.sub_category_id and items.site_id = 1)
        WHERE categories.site_id = 1";
// ...

$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)) {
            $categoriesArray['categories'][] = $tempCategoryArray;
        }
        $tempCategoryArray = array();
        $tempCategoryArray['CATEGORY_ID'] = $row['CATEGORY_ID'];
        $tempCategoryArray['subcategorys'] = array();
    }
    // Same here, if a new sub, let's start fresh
    if($row['subcategory_ID'] != $sub_category) {
        // Add it to the tempCategory
        if(!empty($tempSubCategoryArray)) {
            $tempCategoryArray['subcategorys'] = $tempSubCategoryArray;
        }
        $tempSubCategoryArray = array();
        $tempSubCategoryArray['subcategory_ID'] = $row['subcategory_ID'];
        $tempSubCategoryArray['items'] = array();
    }

    // Finally, no need for temporary arrays with items, since they have no sub-array
    $tempSubCategoryArray['items'][] = array('item_NAME': $row['item_name'], 'item_id': $row['id']);
}

$categoryJson = json_encode($categoryArray);