Posted By

theroamingcoder on 11/10/09

Tagged

database node tree row child column Parent

Versions (?)

Who likes this?

4 people have marked this snippet as a favorite

23andwalnut
salebab
jkriddle
Merstzik

(php sql to array tree) Convert parent child database structure to a tree and then print it as html

/ Published in: PHP

This code is meant to take it easy on the database by only requiring one query. It also prints nicely indented html and can be used in pretty much any situation where you have a flat parent/child structure that needs to be converted into a tree. This method can also build the tree in a single pass in some cases. Note - I have also added code to have it ignore orphaned children.

Expand | Embed | Plain Text

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <title>PHP Tree Builder</title>
  </head>
  <body>
<?php
 
function tree_example(){
  $rows = array();  //stores all the database rows that are to be converted into a tree
  $tree = array();  //stores the tree
  $tree_index = array();  //an array used to quickly find nodes in the tree
  $id_column = "tag_id";  //The column that contains the id of each node
  $parent_column = "parent_id";  //The column that contains the id of each node's parent
  $text_column = "name";  //The column to display when printing the tree to html
 
  //a sample array - normally you would create this array from your database table (sorted by id)
  //it is important that they keys of the array = the id of each row
  $rows[1] = array("tag_id" => 1, "name" => "food", "parent_id" => NULL);
  $rows[2] = array("tag_id" => 2, "name" => "pizza", "parent_id" => 1);
  $rows[3] = array("tag_id" => 3, "name" => "pepperoni", "parent_id" => 2);
  $rows[4] = array("tag_id" => 4, "name" => "lazagna", "parent_id" => 1);
  $rows[5] = array("tag_id" => 5, "name" => "pie", "parent_id" => 6);
  $rows[6] = array("tag_id" => 6, "name" => "dessert", "parent_id" => NULL);  //a parent defined after the child
  $rows[7] = array("tag_id" => 7, "name" => "cinnamon", "parent_id" => 8);
  $rows[8] = array("tag_id" => 8, "name" => "bear claw", "parent_id" => 9);
  $rows[9] = array("tag_id" => 9, "name" => "donut", "parent_id" => 6); //a parent defined after another late parent and with two children
 
  //build the tree - this will complete in a single pass if no parents are defined after children
  while(count($rows) > 0){
    foreach($rows as $row_id => $row){
      if($row[$parent_column]){
        if((!array_key_exists($row[$parent_column], $rows)) and (!array_key_exists($row[$parent_column], $tree_index))){
           unset($rows[$row_id]);
        }
        else{
          if(array_key_exists($row[$parent_column], $tree_index)){
            $parent = & $tree_index[$row[$parent_column]];
            $parent['children'][$row_id] = array("node" => $row, "children" => array());
            $tree_index[$row_id] = & $parent['children'][$row_id];
            unset($rows[$row_id]);
          }
        }
      }
      else{
        $tree[$row_id] = array("node" => $row, "children" => array());
        $tree_index[$row_id] = & $tree[$row_id];
        unset($rows[$row_id]);
      }
    }
  }
 
  //we are done with index now so free it
  unset($tree_index);
 
  //start printing out the tree
  $html = "    <div id='tree'>\n";
  $html .= "      <ul>\n";
  foreach($tree as $node){
    //go to each top level node and print it and it's children
    $html .= print_tree($node, $text_column, 8, 2);
  }
  $html .= "      </ul>\n";
  $html .= "  </div>\n";
  return $html;
}
 
//recursive function used to print tree structure to html
function print_tree($node, $text_column, $indent, $indent_size){
  //print the current node
  $html = str_repeat(" ", $indent) . "<li>". $node['node'][$text_column];
  if($node['children']){
    $html .= "\n". str_repeat(" ", $indent + $indent_size) . "<ul>\n";
    //then print it's children nodes
    foreach($node['children'] as $child){
      $html .= print_tree($child, $text_column, $indent + $indent_size * 2, $indent_size);
    }
    $html .= str_repeat(" ", $indent + $indent_size) . "</ul>\n". str_repeat(" ", $indent);
  }
  $html .= "</li>\n";
  return $html;
}
 
print tree_example();
 
?>
  </body>
</html>

Report this snippet Tweet

Comments

Subscribe to comments

Posted By: Merstzik on March 4, 2011: great one! I've been trying to figure it out the whole day..
Posted By: jammy on September 18, 2011: Hi this is the great example, But what should be the Sql query if you want to output the tree of a desired user in the Database?
Posted By: rvercesi on November 12, 2011: I have it returning a Fatal Error on line 37. I get the array from a database but parentid are NOT NULL. Instead, root elements have parentid = 0. So I have changed line 31 to if((int)($row[$parentcolumn])>0){ but it still has an error somewhere on if(arraykeyexists($row[$parentcolumn], $tree_index)){. Any thoughts? Thanks.

Comment:

You need to login to post a comment.