In Relational database storing tree data is a common problem. Generally table represent as rows and columns. It is not suitable for represent tree data. Multiple solutions are available to store tree data in the database. In this articles, we’ll explore tree (hierarchical) data using Adjacency List Model approach in PHP & MySQL.

Adjacency list model or Recursion method is an elegant approach, because simple function to iterate through your tree. Majority of programmers used this approach. At first, create table “tree” with id, parent_id, child_name as column. In this, each row stores its parent.

Fetch all data using single select query and store all the result in to an associative array. Also child’s id for each parent node to be stored in another associative array. Finally call display_tree function to displays all children of the specified node. For each of these children, the function should retrieve and display all the child nodes of that child. For these children, the function should again display all children, and so on.

$data = array();
$index = array();
$query = mysql_query("SELECT id, parent_id, child_name FROM tree");
while ($row = mysql_fetch_assoc($query)) {
    $id =$row["id"];
    $parent_id =$row["parent_id"];
    $data[$id]= $row;
    $index[$parent_id][]=$id;
}

function display_tree($parent_id, $level)
{
    global $data, $index;
    if (isset($index[$parent_id])) {
        foreach ($index[$parent_id] as $id) {
            echo str_repeat(" -", ($level*2)) . $data[$id]["child_name"] . "<br>";
            display_tree($id, $level + 1);
        }
    }
}
display_tree(0, 0);

Output:

Mathematics
 - -Algebra
 - - - -Elementary Algebra
 - - - -Abstract Algebra
 - - - -Linear Algebra
 - -Geometry
 - - - -Points
 - - - -Lines
 - - - -Planes
 - - - - - -Triangle
 - - - - - - - -Equilateral Triangle
 - - - - - - - -Scalene Triangle
 - - - - - - - -Isosceles Triangle
 - - - - - -Quadrilaterals
 - - - -Solids