php - 从嵌套集模型结构为Fancytree创建JSON

我想用jquery插件Fancytree显示一个数据结构。在Google中进行了长时间的搜索之后，在数据库中存储数据结构的最佳方法似乎是嵌套集模型（在Stackoverflow中有描述）。但是，要使这种结构的相关嵌套JSON显示起来相当困难，我无法做到。。
这包括转换以下MySQL表：
|类别| id |名称| lft | rgt |深度|
|1 |电子| 1 | 20 | 0|
|2台电视2台9台1台|
|3 |管| 3 | 4 | 2|
|4 |液晶显示器| 5 | 6 | 2|
|5 |血浆| 7 | 8 | 2|
|6 |便携式电子设备| 10 | 19 | 1|
|7 | MP3播放器| 11 | 14 | 2|
|8|闪光| 12 | 13 | 3|
|9 | CD播放机| 15 | 16 | 2|
|10 |双向无线电| 17 | 18 | 2|
在这个JSON中：

[{"key": "1", "title": "ELECTRONICS", "expanded": true, "children": [
    {"key": "2", "title": "TELEVISION", "expanded": true, "children": [
        {"key": "3", "title": "TUBE"},
        {"key": "4", "title": "LCD"},
        {"key": "5", "title": "PLASMA"}
    ]},
    {"key": "6", "title": "PORTABLE ELECTRONICS", "expanded": true, "children": [
        {"key": "7", "title": "MP3 PLAYERS", "expanded": true, "children": [{"key": "8", "title": "FLASH"}]},
        {"key": "9", "title": "CD PLAYERS"},
        {"key": "10", "title": "2 WAY RADIOS"}
    ]}
]}]

我想知道是否存在将MySQL数据转换成分层嵌套JSON的简单方法？
谢谢！

最佳答案

最后，通过一点一点地创建JSON，我成功地使我的树工作。我不认为这是正确的，因为它不是由对象和数组组成的，但这似乎工作得很好

 <?php
    if (!isset($DB_connection)) { require_once('../config/_db_connect.php'); }
    $result = $DB_connection->prepare("SELECT node.category_id, node.name,
     (COUNT(parent.name) - 1) AS depth FROM nested_category AS node,
      nested_category AS parent WHERE node.lft BETWEEN parent.lft
      AND parent.rgt GROUP BY node.name ORDER BY node.lft;");
    $result->execute();

    $i = 0;
    $nb_open = 0;
    $current_level = '';

    echo "[{";
    while( $line = $result->fetch(PDO::FETCH_OBJ) )
    {
        if ($current_level == '') { $current_level = $line->depth; }

        if ($current_level == $line->depth) { if ($i > 0) { echo " }, {"; } }
        elseif ($current_level < $line->depth) {
            echo ',  "children": [ { ';
            $nb_open = $nb_open + 1;
        }
        elseif ($current_level > $line->depth) {
            $nb_to_close = $current_level - $line->depth;
            for ($j = 1; $j <= $nb_to_close; $j++) {
                $nb_open = $nb_open - 1;
                echo " }]";
            }
            echo "}, {";
        }

        echo ' "title": "' . $line->name . '", "key": "' .
              $line->category_id . '", "expanded": true';
        $current_level = $line->depth;
        $i++;
    }

    if ($nb_open>0) { for ($i = 1; $i <= $nb_open; $i++) { echo "}]"; } }
    echo "}]";
    ?>

json

php - 从嵌套集模型结构为Fancytree创建JSON