PHP + recursive menu tree and one path

after some try, I finally found a solution to get from a php recursive tree only one branch. am searing the code, for those who are looking for this kind of script.

Sample source come from this blog : Managing Hierarchical Data in MySQL

The table source :

===Database model3

== Table structure for table category

|------
|Column|Type|Null|Default
|------
|//**id**//|int(11)|No|
|title|varchar(20)|No|
|id_parent|int(11)|Yes|NULL
|path|varchar(11)|No|
== Dumping data for table category

|1|ELECTRONICS|0|0
|2|TELEVISIONS|1|1/2
|3|TUBE|2|1/2/3
|4|LCD|2|1/2/4
|5|PLASMA|2|1/2/5
|6|PORTABLE ELECTRONICS|1|1/6
|7|MP3 PLAYERS|6|1/6/7
|8|FLASH|7|1/6/7/8
|9|CD PLAYERS|6|1/6/9
|10|2 WAY RADIOS|6|1/6/10
|11|PLANS|0|0
|12|SPITFIRE|11|11/12
|13|LOW QUALITY|8|1/6/7/8/13

Before the code, some explanation :

With php recursive tree, we are able to get from an array a tree with its branch, like this :

• ELECTRONICS
• • TELEVISIONS
• • • TUBE
• • • LCD
• • • PLASMA
• • PORTABLE ELECTRONICS
• • • MP3 PLAYERS
• • • FLASH
• • • • LOW QUALITY
• • • CD PLAYERS
• • • 2 WAY RADIOS
• PLANS
• • SPITFIRE

The very simple code that I use for the sample over :

$get_parent = "0";
$rep_1 = mysqli_query($connexion,' SELECT * FROM category');
$get_array = array();
while($don_1 = mysqli_fetch_array($rep_1))
{
    $get_array[$don_1['id']] = array("id" => $don_1['id'], "id_parent" => $don_1['id_parent'], "title" => $don_1['title']);
}
mysqli_free_result($rep_1);
function tree($array,$parent,$currLevel=0)
{
    foreach($array as $key => $value)
    {
        if($value['id_parent'] == $parent)
        {
            echo "".str_repeat("-", $currLevel)."title : ".$value['title']."<br/>";
            $currLevel++;
            tree($array,$key,$currLevel);
            $currLevel--;
        }
    }
}
echo tree($get_array,$get_parent);

The The Adjacency List Model is great for simple mysql query, however, after several days in google search, I found that it was a real head hash to Retrieving a Single Path as we must know the final level, for static menu its fine, but for other purpose like forum parent or pages parent I found it wasn't the best way to process for Retrieving a Single Path.

I documented about the The Nested Set Model, on paper, it's great. But I found it was a bit a mess when INSERT / UPDATE and DELETE are requested.

I finally did some test with the path enumeration : Hierarchical data in MySQL (and other RDBMS) and found a solution for Retrieving a Single Path, like this :

• ELECTRONICS
• • PORTABLE ELECTRONICS
• • • FLASH
• • • • LOW QUALITY

The very simple code that am using :

$get_parent = "0";
$rep_1 = mysqli_query($connexion,' SELECT * FROM category WHERE id=7');
$get_array = array();
while($don_1 = mysqli_fetch_array($rep_1))
{
    $explod_array = explode("/",$don_1['path'],9999);
    foreach ($explod_array as $key=>$value)
    {
            $rep_2 = mysqli_query($connexion,' SELECT * FROM category WHERE id="'.$value.'"');
            while($don_2 = mysqli_fetch_array($rep_2))
            {
                $get_array[$don_2['id']] = array("id" => $don_2['id'], "id_parent" => $don_2['id_parent'], "title" => $don_2['title']);
            }
            mysqli_free_result($rep_2);
    }
}
mysqli_free_result($rep_1);
function tree_1($array,$parent,$currLevel=0)
{
    foreach($array as $key => $value)
    {
        if($value['id_parent'] == $parent)
        {
            echo "".str_repeat("-", $currLevel)."id : ".$value['id']." | id_parent : ".$value['id_parent']." | title : ".$value['title']."<br/>";
            $currLevel++;
            tree_1($array,$key,$currLevel);
            $currLevel--;
        }
    }
}
echo tree_1($get_array,$get_parent);

This way, I keep the same php recursive tree menu code. I do not charge my mysql table with a big query.

The badest point, is that I will have to code a bit more for the INSERT / UPDATE AND DELETE query, but I already worked on it, and it's doable with a little code.

I hope it will help.