How to create a php mysql dynamic tree

Question

How can using php mysql create a dynamic tree for this tables

tbl_folder

db_id   db_foldername
1       accounting
2       hr
3       it

tbl_subfolder

db_id  db_folderid  db_subfoldername
1        1            xxx
2        1            yyy
3        2            zzz

tbl_childsubfolder

db_id  db_subfolderid db_childsubfoldername
1        1                ffff
2        2                llll

tbl_subchild

db_id    db_childsubfolderid   db_subchildname
1           1                     eee
2           1                     ppp

• accounting

  • xxx

    • fff
      • eee
      • ppp

  • yyy

    • lll

• hr

  • zzz

• it

  include("include/connect.php"); --SELECT-- $name"; }
  ?>
  
  

  if(isset($_POST['add'])){
      $foldername=$_POST['txt_name'];
      $select=$_POST['txt_select'];echo $select;
      $explod=explode("-",$select);
      $path=$explod['0'].';'.$explod['1'];
      if($path==";"){$path="";}
      $parent_id=$explod['1'];
      if($foldername==""){echo"enter a name";}
      else{ 
      $insert_query=mysqli_query($conn,"insert into tbl_folders(parent_id,path,name)values('$parent_id','$path','$foldername')")or die(mysqli_error($conn));
          header("location:index.php");
      }
  }
  
  
  $sql=mysqli_query($conn,"select * from tbl_folders where parent_id='0'")or die(mysqli_error($conn));
  while($row=mysqli_fetch_array($sql)){
      $name=$row['name'];
      $id=$row['db_id'];
      echo $name;echo"<br/>";
      $sqli=mysqli_query($conn,"select * from tbl_folders where parent_id='$id'")or die(mysqli_error($conn));
      while($row=mysqli_fetch_array($sqli)){
       $name=$row['name'];
      $id=$row['db_id'];
      $path=$row['path'];
      $x=explode(";",$path);echo $path;echo"<br/>";
      $pa=$x['1']; 
      echo $name;echo"<br/>";
      $sqli=mysqli_query($conn,"select * from tbl_folders where parent_id='$id' and path='$pa'")or die(mysqli_error($conn));
      while($row=mysqli_fetch_array($sqli)){
           $name=$row['name'];
      $id=$row['db_id'];
      $path=$row['db_path'];    
      echo $name;echo"<br/>";}
      }
  }
  

Answer 1

Building the tree

You can build one table with the following fields:

`id (int), parent_id (int), path (vachar), name`

id - is the identifier parent_id - refers to the id of the parent in the same table path - is the path of parent ids to the given element

Example entries in the table:

|id | parent_id | path | name|
-------------------------------
|1  | 0         |      | A   |
-------------------------------
|2  | 1         |;1;   | B   |
-------------------------------
|3  | 2         |;1;2; | C   |

where A is the parent, B is child of A, and C is child of B.

In your backend logic you need to have the following: When you add/edit new item in this table - if it is root parent (with no parents above it) you insert it with parent_id=0 and path='' When you add/edit new item in this table - if it has parent then you insert it with parent_id=:idOfParent and path=CONCAT(:parentPath, ';', :idOfParent, ';') where :idOfParent - is the parent id value and :parentPath is the path of the parent which you concatenate with the ;:idOfParent; ; - is the separator for the ids in the path

Path column gives you the advantage of directly getting all the parents of given element without using recursive approaches. So if you select an item with path ';1;2;3;' and you need the info for the parents also you will have 1 + 3 SELECT queries in total.

And when deleting an item you can do this:

 DELETE FROM table WHERE path LIKE (';:deleteId;')

where :deletedId is the id of the deleted element. This query will delete all the entries that have deleted item as a parent.

Visualization of the tree

you can get the $data with this query

'SELECT id, parent_id, path, name FROM table WHERE 1;'

But for the test i use the following example array

$data = [
    0 => ['id' => 1, 'parent_id' => 0, 'path' => '', 'name' => 'A'],
    1 => ['id' => 2, 'parent_id' => 1, 'path' => ';1;', 'name' => 'B'],
    2 => ['id' => 3, 'parent_id' => 2, 'path' => ';1;2;', 'name' => 'C'],
    3 => ['id' => 4, 'parent_id' => 3, 'path' => ';1;2;3;', 'name' => 'D'],

    4 => ['id' => 5, 'parent_id' => 1, 'path' => ';1;', 'name' => 'E'],
    5 => ['id' => 6, 'parent_id' => 2, 'path' => ';1;2;', 'name' => 'G'],
    6 => ['id' => 7, 'parent_id' => 0, 'path' => '', 'name' => 'H'],
];

$ref = null;
$tree = [];
foreach($data as $item) {
    if($item['path']) {
        $path = ltrim($item['path'], ';');
        $path = rtrim($path, ';');
        $pathArray = explode(';', $path);

        $i = 0;
        foreach($pathArray as $parentId) {
            if($i === 0) {
                if(!isset($tree[$parentId])) {$tree[$parentId] = ['name' => [], 'children' => []];}
                $ref = &$tree[$parentId]['children'];
            }
            else {
                if(!isset($ref[$parentId])) $ref[$parentId] = ['name' => [], 'children' => []];
                $ref = &$ref[$parentId]['children'];
            }
            $i++;
        }

        if($ref !== null) {
            $ref[$item['id']]['name'] = $item['name'];
            $ref[$item['id']]['children'] = [];
        }
    }
    else {
        $tree[$item['id']]['name']     = $item['name'];
        $tree[$item['id']]['children'] = [];
    }
}

Output part:

print '<pre>';
print_r($tree);
print '</pre>';

So here you change the code according to your needs. Print it in the way you want (may be you will need recurrsion to access every tree node)