How to get JSON tree?

Question

PHP

There is a table mysql
id pid name
1 0 Group1
2 1 Group1.1
3 0 Group2
4 2 Group1.1.1
5 1 Group1.2
how can I output an array so that using the json_encode() function I get a non-flat JSON like this [{"id": 1,"pid":null,"name":"ipsum"},{"id":2,"pid":1,"name":"ipsum1"},{"id":3,"pid": 1,"name":"ipsum2"},{"id":4,"pid":2,"name":"ipsum3"},{"id":5,"pid":3,"name": "ipsum4"},{"id":6,"pid":null,"name":"ipsum5"}]
array like this $data = array(
array('id' => 1, 'pid' =>null, 'name' => 'ipsum'),
array('id' => 2, 'pid' => 1, 'name' => 'ipsum1'),
array('id' => 3, 'pid' => 1, 'name' => 'ipsum2'),
array('id' => 4, 'pid' => 2, 'name' => 'ipsum3'),
array('id' => 5, 'pid' => 3, 'name' => 'ipsum4'),
array('id' => 6, 'pid' => null, 'name' => 'ipsum5' )
);
here the dude uses the json_encode function and he gets normal json out
How to get the final JSON like this

{
        label: 'node1',
        children: [
            { label: 'child1' },
            { label: 'child2' }
        ]
    },
    {
        label: 'node2',
        children: [
            { label: 'child3' }
        ]
    }

Answer 1

The problem is not in json_encode, but in the request to the database. As a result of the request to the database, you get a flat array, which, of course, is converted to flat json. Achieve at first reception of an array of the correct structure, and then already convert.

Answer 2

Here is the actual code. The nodes in the flat table must be sorted in the order of the LDL traversal (and not, like yours, in width - see the example). If width sorting is critical, then the algorithm will be different.

function flat2tree(&$flat){
    $tree = array();
    $path = array();
    $pathLength = 0;
    
    $N = (is_array($flat)) ? count($flat) : 0;

    for ($i=0; $i<$N; ++$i){
        $node =& $flat[$i];
        for(; $pathLength && $path[$pathLength-1]['id'] != $node['pid']; --$pathLength);

        if ($pathLength){
            $parent =& $path[$pathLength-1];
            if (!isset($parent['child']) || !is_array($parent['child'])){
                $parent['child'] = array();
            }
            $parent['child'][] = $node;
            $path[$pathLength++] =& $parent['child'][count($parent['child']) - 1];
        }
        else{
            $tree[] = $node;
            $path[$pathLength++] =& $tree[count($tree) - 1];
        }
    }

    return $tree;
}

$flat = array(
    array('id' => 1, 'pid' => null, 'name' => 'ipsum'),
    array('id' => 2, 'pid' => 1, 'name' => 'ipsum1'),
    array('id' => 4, 'pid' => 2, 'name' => 'ipsum3'),
    array('id' => 3, 'pid' => 1, 'name' => 'ipsum2'),
    array('id' => 5, 'pid' => 3, 'name' => 'ipsum4'),
    array('id' => 6, 'pid' => null, 'name' => 'ipsum5')
);
$tree = flat2tree($flat);
var_dump($tree);
echo json_encode($tree);

Answer 3

As the json_encode option, you must pass the JSON_PRETTY_PRINT constant, which is available since PHP 5.4 ( example )
br1.php.net/manual/en/json.constants.php#constant....

Answer 4

Agree with previous answer. json_encode/json_decode don't change the data structure. So you need to write a php function (or in a database) that will produce an appropriately structured result.
In an example specified by you, just as the answer offer similar function. Personally, I do not like that answer, because. there directly in the code the data structure with keys is stitched. If the format changes a little - everything will fly, you will have to rewrite the entire function - this is not a very good approach.
Your structure in this regard is simpler - an ordinary tree. If no one answers before, I'll try to sketch a recursive function to traverse this tree in the evening. Already times ten similar functions wrote. Even when I was in college. ;)

Answer 5

Another option, less "classic" or something .. There will be more memory, but it does not depend on sorting.

function flat2tree(&$flat){
    $tree = array();
    $nodeMap = array();
    
    $N = (is_array($flat)) ? count($flat) : 0;

    for ($i=0; $i<$N; ++$i){
        $node =& $flat[$i];
        
        if (isset($nodeMap[$node['pid']])){
            $parent =& $nodeMap[$node['pid']];
            if (!isset($parent['child']) || !is_array($parent['child'])){
                $parent['child'] = array();
            }
            $parent['child'][] = $node;
            $nodeMap[$node['id']] =& $parent['child'][count($parent['child']) - 1];
        }
        else{
            $tree[] = $node;
            $nodeMap[$node['id']] =& $tree[count($tree) - 1];
        }
    }

    return $tree;
}

$flat = array(
    array('id' => 1, 'pid' => null, 'name' => 'ipsum'),
    array('id' => 2, 'pid' => 1, 'name' => 'ipsum1'),
    array('id' => 3, 'pid' => 1, 'name' => 'ipsum2'),
    array('id' => 4, 'pid' => 2, 'name' => 'ipsum3'),
    array('id' => 5, 'pid' => 3, 'name' => 'ipsum4'),
    array('id' => 6, 'pid' => null, 'name' => 'ipsum5')
);
$tree = flat2tree($flat);
var_dump($tree);
echo json_encode($tree);

Answer 6

before var_dump($array);write downecho '<pre>';