MySQL/PHP rendering of tree hierarchy to array/JSON

Question

Given the following hierarchy of folder saved to a database table:

         top-
            |- subtop1
            |- subtop2
               |- subsubtop1

The query returns:

      ID NAME      PARENT ID
      1, top,       null
      2, subtop1,      1
      3, subtop2,      1
      4, subsubtop1,   3

I want to convert this to an array and then to JSON as below:

      {
          "folder":
          {
                "fold_id": 1, "fold_name": "top", "fold_parent_fold_id": null,
                "sub_folders":
                {
                      "folder":
                      {
                            "fold_id": 2, "fold_name": "subtop1", "fold_parent_fold_id": 1,
                            "sub_folders": {}
                       },
                      "folder":
                      {
                            "fold_id": 3, "fold_name": "subtop2", "fold_parent_fold_id": 1,
                            "sub_folders":
                            {
                                  "folder":
                                  {
                                        "fold_id": 4, "fold_name": "subsubtop1", "fold_parent_fold_id": 3,
                                        "sub_folders":
                                        {
                                        }
                                  }
                            }
                       }
                }
          }
      }

Here is my code (part of a class):

    public function buildTree ( $tree, $dir ) : array
    {
        $retarr = array();
        foreach ($tree as $tnode)
        {
        var_dump($tnode);
            if ( $tnode['folder']['fold_id'] == $dir['fold_parent_fold_id'])
            {
                if ( count($tnode['folder']['fold_id']['subfolders']) == 0 )
                {
                    // Next line errors with " Cannot use a scalar value as an array "
                    $tnode['folder']['fold_id']['subfolders'] =  array($dir['fold_id']=>$dir);
                }
                else
                {
                    array_push($tnode['folder']['fold_id']['subfolders'], array($dir['fold_id']=>$dir)  );
                }
            }
            else
            {
               $tnode = $this->buildTree ($tnode, $dir);
            }
        var_dump($tnode);
            array_push($retarr, $tnode);
        }
        return ($retarr);
    }

This is the function that gets the flat hierarchy list from the database and calls the buildTree function to build the tree array up bit by bit:

    public function getFolderTree ( $foldID ) : array
    {
        $foldContent = "";

        $sql =<<<SQL

        SELECT  1 oby,
                fold_id,
                fold_name,
                fold_parent_fold_id
        FROM    (SELECT * FROM folders
                 ORDER BY fold_parent_fold_id, fold_id) items_sorted,
                (SELECT @iv := ?) initialisation
        WHERE   find_in_set(fold_parent_fold_id, @iv)
        AND     length(@iv := concat(@iv, ',', fold_id))
        UNION
        SELECT  0 oby,
                fold_id,
                fold_name,
                fold_parent_fold_id
        FROM    folders
        WHERE   fold_id = ?
        ORDER BY 1, 4, 3
SQL;

        $rows = $this->db->query($sql, $foldID, $foldID)->fetchAll();
        $tree_array = array();
        $dirs = array();

        foreach ($rows as $row)
        {
            $subs = array();
            //var_dump ( $row );
            if ( $row['fold_parent_fold_id'] == null || count($dirs) == 0 )
            {
                $dirs[$row['fold_id']] = array ( "folder" => $row, "subfolders" => $subs );
                $tree_array = $dirs;
            }
            else
            {
               $tree_array = $this->buildTree ($tree_array, $row);
            }
            var_dump ( $tree_array );
        }
        //var_dump($rows);
        return ( json_encode($tree_array) );
    }

As indicated above I an error running this in the buildTree function:

                    // Next line errors with " Cannot use a scalar value as an array "
                    $tnode['folder']['fold_id']['subfolders'] =  array($dir['fold_id']=>$dir);

However, I am also not sure I am going about this the right way. I tried to extract the data as JSON using a query from the database but that didn't work.

Answer 1

Take a look at the native php function json_encode()

https://www.php.net/manual/en/function.json-encode.php

It is an inbuild php function that is used to convert PHP array or object into JSON (JavaScript Object Notation) representation.