将层次结构提取到 JSON 中

Question

我在将MySQL表转换为json时遇到问题。我有一个表格，显示了员工的层次结构，如下所示：

该表是员工表，其中 ID， 名字， 姓氏， 父母 ID

所以现在我想查询数据，将其提取到多级json对象中，以模拟显示员工及其主管。

我希望有人可以帮助我。

Answer 1

假设您已经从数据库中获取了数据，下面的解决方案将为您提供所需的数据。

解决方法

是通过递归。

PHP沙盒在线

<?php
function buildTree(array &$elements, $parentId = 0) {
    $branch = array();
    foreach ($elements as $element) {
        if ($element['parentid'] == $parentId) {
            // recursion:
            $children = buildTree($elements, $element['id']);
            if ($children) {
                $element['children'] = $children;
            }
            $branch[$element['id']] = $element;
            unset($elements[$element['id']]);
        }
    }
    return $branch;
}
$rows = [
    ['id' => 1, 'firstname' => 'john1', 'lastname' => 'doe', 'parentid' => 0],
    ['id' => 2, 'firstname' => 'john2', 'lastname' => 'doe', 'parentid' => 1],
    ['id' => 3, 'firstname' => 'john3', 'lastname' => 'doe', 'parentid' => 1],
    ['id' => 4, 'firstname' => 'john4', 'lastname' => 'doe', 'parentid' => 0],
    ['id' => 5, 'firstname' => 'john5', 'lastname' => 'doe', 'parentid' => 3],
];
print_r(buildTree($rows));

和生成的数组：

Array
(
    [1] => Array
        (
            [id] => 1
            [firstname] => john1
            [lastname] => doe
            [parentid] => 0
            [children] => Array
                (
                    [2] => Array
                        (
                            [id] => 2
                            [firstname] => john2
                            [lastname] => doe
                            [parentid] => 1
                        )
                    [3] => Array
                        (
                            [id] => 3
                            [firstname] => john3
                            [lastname] => doe
                            [parentid] => 1
                            [children] => Array
                                (
                                    [5] => Array
                                        (
                                            [id] => 5
                                            [firstname] => john5
                                            [lastname] => doe
                                            [parentid] => 3
                                        )
                                )
                        )
                )
        )
    [4] => Array
        (
            [id] => 4
            [firstname] => john4
            [lastname] => doe
            [parentid] => 0
        )
)

json_encode结果后，您将获得：

{
    "1": {
        "id": 1,
        "firstname": "john1",
        "lastname": "doe",
        "parentid": 0,
        "children": {
            "2": {
                "id": 2,
                "firstname": "john2",
                "lastname": "doe",
                "parentid": 1
            },
            "3": {
                "id": 3,
                "firstname": "john3",
                "lastname": "doe",
                "parentid": 1,
                "children": {
                    "5": {
                        "id": 5,
                        "firstname": "john5",
                        "lastname": "doe",
                        "parentid": 3
                    }
                }
            }
        }
    },
    "4": {
        "id": 4,
        "firstname": "john4",
        "lastname": "doe",
        "parentid": 0
    }
}