Recursive Functions in PHP to Manage Single MySql Hierarchy Table

The recursive function is a necessary tool when it comes to creating very deep levels of categories and pages. These functions in fact are one of the best solutions in order to manage deep levels of relationships that are also referred to the “parent” and “child” relationship (generalization hierarchy). There are many ways to accomplish this task however a recursive function allows the hierarchy to grow infinite or lets say as much allotted memory that the server can provide. Simply these functions can help to eliminate hard coded queries to allow even greater possibilities. In my example I will be instructing how to create your own PHP and MySql recursive hierarchy function. You will only need a single MySql table where we will store our “many” categories with a hierarchical database pattern using a unique identifier and referencing it as a foreign key however it is within the same table.

The recursive function is simply a function that constantly calls itself recursively. You are probably wondering why I am demonstrating a “recursive” loop, ain’t that bad? Not at all, if you do it correctly it will become more understandable and you will learn the benefits of this function. I’ve seen many experienced programmers, including myself creating an extra database table to store the “parent” and “child” categories. I’m not saying this is the incorrect approach, however you will be shit out of luck when your client tells you that they need another level added in. Not to mention, this also consists of more code to be written and another table to manage.

First off, lets create our database table. I will be using MySql for this tutorial.

CREATE TABLE categories 
(category_id INT NOT NULL AUTO_INCREMENT, 
category_name VARCHAR(100) NOT NULL, 
parent_id INT NOT NULL, 
PRIMARY KEY (category_id));

The query will create the categories table with 3 fields. The primary storing the auto inc. identifiers, parent_id accepting the self referenced foreign key (the primary key) and the category name. You may add other fields such as a category_status and so on…

Next, the php function to work this magic… Oh, and please make sure that you are connected to the MySql database!

< ?php
 
//here we call our function - pass your ID variable for the current category here
buildTree($id);
 
function buildTree($id) {
 
    //make sure we can use our arrays outside of the function
    global $tree_arr;
    global $treeid_arr;
    $sql = "SELECT * FROM categories WHERE category_id='''$id'";
    $data = mysql_fetch_assoc(mysql_query($sql));
 
    //store values into the next key on the array
    $tree_arr[]   = $data['category_name'];
    $treeid_arr[] = $data['category_id'];
    //check that parent_id
 
    if($data['parent_id'] > 0) {
        //call function recursively and pass ID
        buildTree($data['parent_id']);
    }
}
 
//reverse sort the arrays (sorting on the key not the value)
krsort($tree_arr, SORT_NUMERIC);
krsort($treeid_arr, SORT_NUMERIC);
 
//iterate through the arrays - we only need to iterate through one. keys should be identical on both arrays
//We concatenate our &quot;$tree&quot; string... so we can print / echo it later.
$tree = '';
foreach($tree_arr as $key => $value) {
    $tree .= '<a href="page.php?catid=' . $treeid_arr[$key] . '">';
    //we are pulling data from MySQL so we must clean slashes
    //and protect against HTML code in the category names
    //using stripslashes and htmlspecialchars
    $tree .= stripslashes(htmlspecialchars($value)) . '</a>';
}
 
//our tree will have a trailing > character with a space either side -
//let's clean it up
$tree= substr($tree, 0, strlen($tree) - 3);
?>

As you can see, we created a dynamic category hierarchy. All you need to do now is get much more creative and start plugin away. I suggest trying this out with XML and javascript to create some really cool dynamic drop downs. You can also us the ul and li listing syntax to display the tree for your users… It’s all up to you now. Ciao