如何从数据库创建JSON树

Question

“我正在设置一个文件浏览器，并将文件夹的名称保存在数据库中。任何数量的文件夹都可以保存在数据库中，从保存的数据中，我想创建一个json树。

这是我的数据库表结构

folder_id    folder_name      parent_id
  1          parentFolder1    <NULL>
  2          parentFolder2    <NULL>
  3          subFolder1          1
  4          subFolder2          1
  5          subFolder3          3
  6          subFolder4          2
  7          subFolder5          5

parent\u id是一个外键，用于引用文件夹id。

这是我试图从上述数据库创建的json树结构，将来将添加更多父文件夹和子文件夹。是否可以从上面的表结构中创建json树？有人能帮我吗？

[
    {
        "id": "1",
        "value": "parentFolder1",
        "data": [
            {
                "id": "3",
                "value": "subFolder1",
                "data": [
                    {
                        "id": "5",
                        "value": "subFolder3",
                        "data": [
                            {
                                "id": "7",
                                "value": "subFolder5",
                                "data": []  
                                ]
                            }
                        ]
                    }
            },
            {
                "id": "4",
                "value": "subFolder2",
                "data": []
            }
        ]
    },
    {
        "id": "2",
        "value": "parentFolder2",
        "data": [
            {
                "id": "6",
                "value": "subFolder4",
                "data": []
            }
        ]
    }
]

Answer 1

这里是C代码示例，这不是一个完美的代码。您需要修改它以供使用

#if 0
folder_id    folder_name      parent_id
  1          parentFolder1    <NULL>
  2          parentFolder2    <NULL>
  3          subFolder1          1
  4          subFolder2          1
  5          subFolder3          3
  6          subFolder4          2
  7          subFolder5          5
#endif
#include<stdio.h>
#include<iostream>
#include<vector>
#include<tuple>
#include <bits/stdc++.h>
#include<boost/property_tree/ptree.hpp>
#include <boost/property_tree/json_parser.hpp>
std::vector<std::tuple <int, std::string, int>> list;
namespace pt = boost::property_tree;
pt::ptree rootNode;
int main () {

    list.push_back(std::make_tuple(1, "parentFolder1", 0));
    list.push_back(std::make_tuple(2, "parentFolder2", 0));
    list.push_back(std::make_tuple(3, "subFolder1", 1));
    list.push_back(std::make_tuple(4, "subFolder2", 1));
    list.push_back(std::make_tuple(5, "subFolder3", 3));
    list.push_back(std::make_tuple(6, "subFolder4", 2));
    list.push_back(std::make_tuple(7, "subFolder5", 5));

    int x, y;
    int k = 0;
    pt::ptree mainRootNode;
    for (int i=0; i < list.size(); i++) {
        if (std::get<2>(list[i])) {
            continue;
        }
        k = 0;
        pt::ptree subMainRootNode;
        subMainRootNode.add("id", std::get<0>(list[i]));
        subMainRootNode.add("value", std::get<1>(list[i]));
        pt::ptree Node;
        y = x = std::get<0>(list[i]);
        int saved_place = 0;
        for (int j = 0; j < list.size(); j++) {
            if (x == std::get<2>(list[j])) {
                k++;
                std::string str;
                for(int m = 0;m < k; m++) {
                    str.append("data.");
                }
                pt::ptree child;
                std::string id = str + "id";
                std::string value = str + "value";
                subMainRootNode.add(id, std::get<0>(list[j]));
                subMainRootNode.add(value, std::get<1>(list[j]));
                x = std::get<0>(list[j]);
                if (!saved_place) {
                    saved_place = j;
                }
            }
            if (((j+1) == list.size()) && saved_place) {
                j = saved_place ;
                k = 0;
                saved_place = 0;
                x = y;
            }
        }
        mainRootNode.add_child("data", subMainRootNode);
        pt::write_json(std::cout, subMainRootNode);
    }
}

Answer 2

$arr = array(
  array('id'=>100, 'parentid'=>0, 'name'=>'a'),
  array('id'=>101, 'parentid'=>100, 'name'=>'a'),
  array('id'=>102, 'parentid'=>101, 'name'=>'a'),
  array('id'=>103, 'parentid'=>101, 'name'=>'a'),
);

$new = array();
foreach ($arr as $a){
    $new[$a['parentid']][] = $a;
}
$tree = createTree($new, array($arr[0]));
print_r($tree);

function createTree(&$list, $parent){
    $tree = array();
    foreach ($parent as $k=>$l){
        if(isset($list[$l['id']])){
            $l['data'] = createTree($list, $list[$l['id']]);
        }
        $tree[] = $l;
    } 
    return $tree;
}