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.

Looks like a good minimal reproducible example with a good coding attempt. It's been so long, I nearly forgot how to upvote a question. ;) — mickmackusa
– mickmackusa ♦, Commented Feb 27, 2022 at 11:59
The expected result is invalid. In the first sub_folders level, you have multiple properties called folder. Property names must be unique within the same object. — M. Eriksson
– M. Eriksson, Commented Feb 27, 2022 at 12:20

Traian GEICU · Accepted Answer · 2022-03-02 00:48:46Z

One way is to take advantage of OPP combined with standard way of constructing a custom List. In order not to use any sql-queries an intermediary data-structure is used to hold initial associations. Assume also this association is properly-set and acyclic.

//for initial storage and to avoid db-query(but here can be adjusted even with sql)
class MyList
{
    public $arr=array();

    public function addNode($node)
    {
       $this->arr[$node->id]=$node;
    }
    
    public function getChilldren($pid)
    {
        $list=array();
        foreach($this->arr as $node)
        {
            if($node->pid==$pid)
            {
                $list[]=$node;
            }
        }
        return $list;
    }
}

//base component
class Node
{
    public $id;
    public $name;
    public $pid;
    public function __construct($id,$value,$pid)
    {
        $this->id=$id;
        $this->name=$value;
        $this->pid=$pid;
    }
    public function __toString()
    {
        return $this->id.":".$this->name.":".$this->pid;
    }
}

//list implementation
class MyNode
{
  public $root;
  public $leafs=array();
  public function __construct($root)
  {
      $this->root = $root;
  }
  public function add(MyList $list, $pid)
  {
      if($list->arr[$pid]!=null)
      {
          $this->root = $list->arr[$pid];
          $this->leafs=array();
      }
      else
      {
          return null;
      }
      $data = array();
      $data[]=$this->root;
      
      $current = array_shift($data);
      while($current  !=null)
      {
          //echo $current."<br>";
          $children=$list->getChilldren($current->id);
          foreach($children as $child)
          {
              $data[]=$child;
              $parentList = [$this];
              $start = null;
              while($parentList != null)
              {
                  $start = array_shift($parentList);
                  if($start->root->id == $child->pid)
                  {
                      $start->leafs[] = new MyNode($child);
                      //end_loop
                      break;
                  }
                  
                  //next search through children
                  foreach($start->leafs as $next)
                  {
                      $parentList[] = $next;
                  }
              }    
          }
          $current = array_shift($data);
      }
     // var_dump($this);
  }
}

//run
//add associations
$arr = new MyList();
$arr->addNode(new Node(4,"n4",3));
$arr->addNode(new Node(5,"n4",4));
$arr->addNode(new Node(6,"n4",4));
$arr->addNode(new Node(7,"n4",4));
$arr->addNode(new Node(1,"n1",null));
$arr->addNode(new Node(2,"n2",1));
$arr->addNode(new Node(3,"n3",1));
//names have no importance when constructing the tree

var_dump($arr);

//make full-list
$run = new MyNode(null);
$run->add($arr,1);

echo "<pre>";
print_r($run);
echo "</pre>";

echo json_encode($run)."<br>";

//make partial-list from a subtree
$run2= new MyNode(null);
$run2->add($arr,4);
echo json_encode($run2)."<br>";

Output:

//run1-json
{
"root":{"id":1,"name":"n1","pid":null},
"leafs":[{"root":{"id":2,"name":"n2","pid":1},"leafs":[]},
         {"root":{"id":3,"name":"n3","pid":1},
          "leafs":[{"root":{"id":4,"name":"n4","pid":3},
                    "leafs":[{"root":{"id":5,"name":"n4","pid":4},"leafs":[]},
                             {"root":{"id":6,"name":"n4","pid":4},"leafs":[]},
                             {"root":{"id":7,"name":"n4","pid":4},"leafs":[]}]}]}]
}

//run2-json
{"root":{"id":4,"name":"n4","pid":3},
 "leafs":[
            {"root":{"id":5,"name":"n4","pid":4},"leafs":[]},
            {"root":{"id":6,"name":"n4","pid":4},"leafs":[]},
            {"root":{"id":7,"name":"n4","pid":4},"leafs":[]}]}

Thank you. I will try this when I get back on this work and let you know.
kindly let me know how it's work. It should be proper as tested, but still it can be enhanced otherwise.
Thank you this helped me a LOT with getting a working model.

chepakwak · Accepted Answer · 2022-02-27 12:00:35Z

0

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.

answered Feb 27, 2022 at 12:00

chepakwak

11 silver badge3 bronze badges

2 Comments

Markus AO Over a year ago

That's not going to build up the hierarchy OP wants, it'll just result in a flat/1-dimensional JSON object.

Community Over a year ago

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

MySQL/PHP rendering of tree hierarchy to array/JSON

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related