How to create JSON tree from database

Question

“I’m setting up a File browser and saving folder's name in database. Any number of folders can able to save in database, from the saved data i want to create a json tree.

This is my database table structure

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_id is a foreign key which refer folder_id.

This is the json tree structure am trying to create from the above database and more parent folder and subfolders will be add in future. if it is possible create json tree from the the above table structure? can any one please help me?

[
    {
        "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": []
            }
        ]
    }
]

You need a recursive function to build your tree structure. Then, once done, `json_encode()` it — Cid, Aug 17 '19 at 07:42
Tagging a DBMS seems irrelevant since this will be done in php. However, don't tag 2 differents DBMS — Cid, Aug 17 '19 at 07:43
You need to recursively iterate over the table, start with parent_id=none as root folders. Then add the second level, third level and so on. Use an array to collect data and finally convert the array to json using json_encode($array,JSON_PRETTY_PRINT) — WeSee, Aug 17 '19 at 07:45
You might consider moving to a nested set model. Either way, I agree that application code would be a sensible way forward, for some or all of the steps required — Strawberry, Aug 17 '19 at 07:56
@WeSee Yes i can do like that but where iam strucking is i can't predict the level because it's dynamic data. Any number of sub folders can able to add. — stevin, Aug 17 '19 at 07:57
@stevin look at how a recursive function works. There is always a stop condition that terminates recursion. In your case if there is no more subfolder then recursion terminates. In each recursion step you need to make a seperate SQL query. Maybe have a look at https://www.elated.com/php-recursive-functions/ — WeSee, Aug 17 '19 at 08:07
This [question](https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query) will show you how to create a hierarchical query in MySQL which might be easier to traverse into your desired data structure. — Nick, Aug 17 '19 at 08:16

score 3 · Answer 1 · answered Aug 17 '19 at 11:14

$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;
}

score 0 · Answer 2 · answered Aug 17 '19 at 22:51

Here is C++ code example, this is not a perfect code. you need to modify it for your use

#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);
    }
}

How to create JSON tree from database

2 Answers2