Create a JSON tree view in PHP using delimiter

Question

I am trying to create a file explorer type treeview JSON to be read by FancyTree for a project I'm attempting.

The files are stored in a database, with an ID, name, URL, Type and code fields. The mock database looks like this:

ID      name        URL.        Type            code
1       test        dir.dir1    txt             sometext
2       next        dir.dir1    txt             somemoretext
3       main        dir         txt            evenmoretext

I need to build the JSON tree view from this data, using the URL as a path (period being the delimiter) and the files being inside the final directory so the tree looks like

/dir/dir1/test.txt
/dir/dir1/next.txt
/dir/main.txt

FancyTree JSON output should look like

[
    {
        "title": "dir",
        "folder": true,
        "children": [
            {
                "title": "dir1",
                "folder": true,
                "children": [
                    {
                        "title": "test.txt",
                        "key": 1
                    }, {
                        "title": "next.txt",
                        "key": 2
                    }
                ]
            }, {
                "title": "main.txt",
                "key": 3
            }
        ]
    }
]

Currently, I'm getting the data from the database into $scriptArray

SELECT 'name','url','type','id' FROM.....

I'm then sorting and building a tree with

    $url = array_column($scriptArray, 'url');
    array_multisort($url, SORT_ASC, $scriptArray);

    $result = [];

    foreach($scriptArray as $item) {
        $loop = 0;
        $keys = array_reverse(explode('.', $item->url));
        $tmp = $item->name;
        $tmp2 = $item->type;

        foreach ($keys as $keyid => $key) {
            if($loop == 0) {
                $tmp = ["title" => $tmp.".".$tmp2, 'key' => $item->id];
            } else {
                $tmp = ["title" => $keys[$keyid - 1], "folder" => true, "children" => [$tmp]];
            }
            $loop++;
        }
        $tmp = ["title" => $keys[count($keys)-1], "folder" => true, "children" => [$tmp]];

        $result[] = $tmp;
    }

However, the output I'm getting is.

[
    {
        "title": "dir",
        "folder": true,
        "children": [
            {
                "title": "dir2",
                "folder": true,
                "children": [
                    {
                        "title": "test.txt",
                        "key": 1
                    }
                ]
            }
        ]
    },
    {
        "title": "dir",
        "folder": true,
        "children": [
            {
                "title": "dir2",
                "folder": true,
                "children": [
                    {
                        "title": "next.txt",
                        "key": 2
                    }
                ]
            }
        ]
    },
    {
        "title": "main.txt",
        "key": 3
    }
]

I have tried applying an array_merge, array_merge_recursive and various others without success. Can anyone help with this?

Are you trying to put next.txt and test.txt into 'dir2' branch despite them having different urls? — Banzay, Nov 20 '21 at 12:29
Sorry no. I've corrected the question. Obviously, if I did have a /dir/dir2 or even /dir/dir1/dir2 then the file would appear in that tree. — Andy Stubbs, Nov 20 '21 at 13:16
This has nothing to do with JSON but rather with how you juggle the data in memory. What is the array you start with? What is the final array? Put both down in PHP notation. Then, write your loops to transform one into the other. If you're stuck, step through the code with a debugger to find out in which place it starts to misbehave. Also, make sure you have unit tests, complex transformations are easier to develop with a solid test base. — Ulrich Eckhardt, Nov 20 '21 at 13:30

score 0 · Accepted Answer · edited Nov 23 '21 at 06:50

Working with loop won't work unless you know per advance the maximum depth of your folder hierarchy.

A better solution is to build the folder path "recursively", and append the file to the final folder.

This can be achieved with by creating a reference with the & operator, and navigate to its children until the whole path is build :

$result = array();
foreach($files as $file)
{
    // build the directory path if needed
    $directories = explode('.', $file->url); // get hierarchy of directories
    $currentRoot = &$result ; // set the pointer to the root directory per default
    foreach($directories as $directory)
    {
        // check if directory already exists in the hierarchy
        $dir = null ;
        foreach($currentRoot as $i => $d)
        {
            if(isset($d['folder']) && $d['folder'] and $d['title'] == $directory)
            {
                $dir = &$currentRoot[$i] ;
                break ;
            }
        }
        
        // create directory if missing
        if(is_null($dir))
        {
            $item =  array(
                'title' => $directory,
                'folder' => true,
                'children' => array()
            );
            $currentRoot[] = $item ;
            $dir = &$currentRoot[count($currentRoot)-1];
        }
        
        // move to the next level
        $currentRoot = &$dir['children'] ; 
        unset($dir);
    }
    
    // finally append the file in the latest directory
    $currentRoot[] = array(
        'title' => $file->name . '.' . $file->type,
        'key' => $file->id,
    );
    
    
    unset($currentRoot);
}

echo json_encode($result);

This works perfectly. I had to add an isset($d['folder']) when checking if a directory exists, else an exception was thrown when checking files without a folder property. I've added the edit as a suggestion. — Andy Stubbs, Nov 22 '21 at 22:25
@AndyStubbs: my PHP is probably less strict than yours. I approve the edit. — Joffrey Schmitz, Nov 23 '21 at 06:53

Create a JSON tree view in PHP using delimiter

1 Answers1