Parent/Child Category Tree

By bdparnes on Mar 14, 2006

This is a simple PHP script to generate a Category Tree structure based on parent/child MySQL database. Database structure looks like this:

  • category_id
  • parent_id
  • title

And will generate a structure like this:

Category 1

  • Sub Category
    • Sub, Sub Category
  • Sub Category 2
    Category 2

This is much simpler and works very well. The other code I found on this forum didn't work at all and was very complicated, so I wrote an easy script. Just 1 note: It's meant for a structure that does not have a root element.

If you do use the code, I just ask that you give me credit for the code. Thanks!

<?php

// Recursive function to generate a parent/child tree
// Without the need for a Root parent
// Written by: Brian Parnes
// 13 March 2006

//$connect = mysql_connect(HOST_NAME, USERNAME, PASSWORD);
//mysql_select_db(DATABASE);
$nav_query = mysql_query("SELECT * FROM `categories` ORDER BY `category_id`");
$tree = "";                 // Clear the directory tree
$depth = 1;                 // Child level depth.
$top_level_on = 1;          // What top-level category are we on?
$exclude = array();         // Define the exclusion array
array_push($exclude, 0);    // Put a starting value in it

while ( $nav_row = mysql_fetch_array($nav_query) )
{
    $goOn = 1;          // Resets variable to allow us to continue building out the tree.
    for($x = 0; $x < count($exclude); $x++ )        // Check to see if the new item has been used
    {
        if ( $exclude[$x] == $nav_row['category_id'] )
        {
            $goOn = 0;
            break;              // Stop looking b/c we already found that it's in the exclusion list and we can't continue to process this node
        }
    }
    if ( $goOn == 1 )
    {
        $tree .= $nav_row['title'] . "<br>";                // Process the main tree node
        array_push($exclude, $nav_row['category_id']);      // Add to the exclusion list
        if ( $nav_row['category_id'] < 6 )
        { $top_level_on = $nav_row['category_id']; }

        $tree .= build_child($nav_row['category_id']);      // Start the recursive function of building the child tree
    }
}

function build_child($oldID)            // Recursive function to get all of the children...unlimited depth
{
    global $exclude, $depth;            // Refer to the global array defined at the top of this script
    $child_query = mysql_query("SELECT * FROM `categories` WHERE parent_id=" . $oldID);
    while ( $child = mysql_fetch_array($child_query) )
    {
        if ( $child['category_id'] != $child['parent_id'] )
        {
            for ( $c=0;$c<$depth;$c++ )         // Indent over so that there is distinction between levels
            { $tempTree .= "&nbsp;"; }
            $tempTree .= "- " . $child['title'] . "<br>";
            $depth++;       // Incriment depth b/c we're building this child's child tree  (complicated yet???)
            $tempTree .= build_child($child['category_id']);        // Add to the temporary local tree
            $depth--;       // Decrement depth b/c we're done building the child's child tree.
            array_push($exclude, $child['category_id']);            // Add the item to the exclusion list
        }
    }

    return $tempTree;       // Return the entire child tree
}

echo $tree;

?>

Comments

Sign in to comment.
setahost   -  Apr 23, 2011

you are querying recursive mysql !!! it's really bad. Just read the results to array and then make recursive function or while loop on array .

Regards.

 Respond  
sunil bisht   -  Jan 27, 2011

hiii, please help me,i have a table with parent id and referrer id..how can i see the persons that are joined under the parent id

 Respond  
espradley   -  Jan 12, 2011

I just wrote this a few minutes ago...perhaps it will help someone. Searches for all root elements. Also supports multipe child->parent relationships. Supports up to 5 levels.

        $query = "SELECT faq_categories_id id FROM faq_cat_relationship WHERE faq_parent_id is Null OR faq_parent_id = ''";
        $firstLevel = $this->runQuery($query);

        while(list($firstID) = @mysql_fetch_row($firstLevel)){
            $firstCategoryName = $this->getCatNameByID($firstID);
            $query = "SELECT faq_categories_id FROM faq_cat_relationship WHERE faq_parent_id = '$firstID'";
            $secondLevel = $this->runQuery($query);
            $catArray[$firstID] = '';
            while(list($secondID) = @mysql_fetch_row($secondLevel)){
                $secondCategoryName = $this->getCatNameByID($secondID);
                $query = "SELECT faq_categories_id FROM faq_cat_relationship WHERE faq_parent_id = '$secondID'";
                $thirdLevel = $this->runQuery($query);
                $catArray[$firstID][$secondID] = '';
                while(list($thirdID) = @mysql_fetch_row($thirdLevel)){
                    $thirdCategoryName = $this->getCatNameByID($thirdID);
                    $query = "SELECT faq_categories_id FROM faq_cat_relationship WHERE faq_parent_id = '$thirdID'";
                    $fourthLevel = $this->runQuery($query);
                    $catArray[$firstID][$secondID][$thirdID] = '';  
                    while(list($fourthID) = @mysql_fetch_row($fourthLevel)){
                        $query = "SELECT faq_categories_id FROM faq_cat_relationship WHERE faq_parent_id = '$fourthID'";
                        $fifthLevel = $this->runQuery($query);
                        $catArray[$firstID][$secondID][$thirdID][$fourthID] = '';   
                            while(list($fifthID) = @mysql_fetch_row($fifthLevel)){
                                $catArray[$firstID][$secondID][$thirdID][$fourthID][$fifthID] = '';
                            }
                    }
                }

            }
            unset($fourthID, $thirdID, $secondID, $firstID);
        }//end while loop
        return $catArray;
    }
 Respond  
ram4nd   -  Oct 09, 2010

It's great, but hard to understand and seems like you don't care about performance. You might be looking for something like: http://www.browse-tutorials.net/tutorial/create-treeview-using-recursion-in-php

 Respond  
Chintan   -  Sep 16, 2010

Hey thank you so much for sharing code I was trying to build this code from last few days but i can't achieved displaying properly in dropdown upto infinite level.It works & shows subcategoris after respective parent but one can't differentiate it. Your one is perfect.
My function was as follows
<?php
function finddepth($byid)
{

//check weather category has child
//echo "**Passed Id=".$byid;
$re_query=mysql_query("SELECT
FROM categories where parent='$byid'");

$num_rows = mysql_num_rows($re_query);
//echo " Has children:".$num_rows."
";

if(mysql_num_rows($re_query) > '0')
{

while($recursive_rw=mysql_fetch_array($re_query))
{
    $p=$recursive_rw['id'];
    $catnm=$recursive_rw['category'];
    $parent=$recursive_rw['parent'];
    $pre="|_";

    if($parent=="0")
    {
?>
<option><?php echo $catnm; ?></option>
<?php   
    }
    else
    {
    echo '<option>'.$pre.$catnm.'</option>' ;
    }
    finddepth($p);

}//end while

}//end if

}//end of function
?>

<?php finddepth(0); ?>
 Respond  
ersin d   -  May 08, 2009

Thanks for the code but i tried my before seen this code it helpme alot

this i writed before seen your code...

<?php

$dbconn = @mysql_connect("localhost","ersin","") or die("DB ERROR");
@mysql_select_db("cats",$dbconn) or die("DB ERROR");

function childcat($id,$name){
$query = "SELECT * FROM categories WHERE parent='$id'";
$result = @mysql_query($query) or die ("Table doesn't exists!");
while($row = mysql_fetch_array($result)){
echo "

  • -$row[name]";
    $id = $row['id'];
    $name = $row['name'];
    childcat($id,$name);
    echo "";
    }
    echo "
";
}

$query = "SELECT * FROM categories WHERE parent='0'";
$result = @mysql_query($query) or die ("Table doesn't exists!");
echo "

  • ";
    while($row = mysql_fetch_array($result)){
    echo "$row[name]";
    $name = $row['name'];
    $id = $row['id'];
    childcat($id,$name);
    echo "";
    }
    echo "
";

echo "


";
?>
Uday  -  Jul 02, 2013

Hi ersin,
I am willing to fetch all the subcategories and their subcategories by passing parent category name to a MYSQL select statement.
Can you please share if you are aware about it?

Sign in to comment

sreta   -  Apr 09, 2009

This is nice. But, consider if you have 100 or more categories, how many times will you execute the query inside your function per request. You have already fetched all categories from a table. So, it would be much faster to pass an array of all categories as the second parameter and loop throughout them.

 Respond  
casepoth   -  Oct 01, 2007

This is perfect for what I am trying to do, with one exception: I need told build this tree within a Function, and you program uses a function to build the child trees. Do you have any ideas on how to do this without using a \"Function\" within your code??

 Respond  
Hawkee   -  Mar 14, 2006

Not bad, I like the commenting. It really helps explain what\'s going on. I didn\'t test it, but after reading through I\'m confident it will work with little tinkering.

 Respond  
Are you sure you want to unfollow this person?
Are you sure you want to delete this?
Click "Unsubscribe" to stop receiving notices pertaining to this post.
Click "Subscribe" to resume notices pertaining to this post.