php - 如何使用 php mysql 获取嵌套的 json 对象

Question

我正在尝试从同一个表中获取父类别和子类别。我正在使用 php rest api。

这是我的 table 。

id  parent  category
--------------------
1   | 0 |   Fruits
2   | 0 |   Cars
3   | 1 |   Orange
4   | 1 |   Apple
5   | 0 |   Books
6   | 2 |   Benz
7   | 5 |   Comics
8   | 2 |   Honda
9   | 5 |   Fantasy

我如何通过 php/mysql 查询获得以下 json。

{
    "categories": [{
            "id": "1",
            "category": "Fruits",
            "subCat": [{
                "id": "3",
                "category": "Orange"
            }, {
                "id": "4",
                "category": "Apple"
            }]
        }, {
            "id": "2",
            "category": "Cars",
            "subCat": [{
                "id": "6",
                "category": "Benz"
            }, {
                "id": "8",
                "category": "Honda"
            }]

        }, {
            "id": "5",
            "category": "Books",
            "subCat": [{
                "id": "7",
                "category": "Comics"
            }, {
                "id": "9",
                "category": "Fantasy"
            }]

        }

    ]
}

感谢您的帮助。

Answer 1

我想这会解决你的问题

    function buildTree(array $elements, $parentId = 0)  {
    $branch = array();
    foreach($elements as $element)
        {
        if ($element['parent'] == $parentId)
            {
            $children = buildTree($elements, $element['id']);
            if ($children)
                {
                $element['subCat'] = $children;
                }

            $branch[] = $element;
            }
        }

    return $branch;
    }

$query1 = mysql_query("select * from table");

while ($rlt2 = mysql_fetch_array($query1, MYSQL_ASSOC))
    {
    $child[] = $rlt2;
    }

$tree['categories'] = buildTree($child);
$this->response($this->json($tree) , 200);