Designing a Parent/Child Tree Category in PHP and MySQL

I found this very cool code that I was searching for a long time. It’s about designing and coding a Parent and Child Tree Category. A scenario is something like Parent Category has a child category at the same time the child category also has a child category, so it’s like a category has an unlimited subcategory.

  • Automobile
    • Fuel
      • Gasoline
      • Diesel
    • Maintenance
  • Food
    • Fish
    • Pork

Here’s how to implement that one.

First create a database table named “categories” that has fields.

- category_id (PK int)
- parent_id (int)
- title (varchar)

PHP CODE

<?php
    $connect = mysql_connect("localhost", "root", "") or die ( mysql_error() );
    mysql_select_db("test");
    $nav_query = mysql_query("SELECT * FROM `categories` ORDER BY `category_id`") or die( mysql_error() );
    $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
       $tempTree = "";
       $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 .= " "; }
             $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;

?>

This code would produce something like this

Isn’t that cool!?! Yes!

All credit belongs to hawkee!

Hope this helps someone. Have a happy coding! :)

5 thoughts on “Designing a Parent/Child Tree Category in PHP and MySQL

  1. Pingback: Coding a Navigation Menu Bar with drop down subpages

  2. How about that script above, I think that’s what you’re looking for, just copy and paste it then you’re good to go!

    Cheers! :)

  3. The script above i a bit advanced. Advanced user are not going to look for such help they already know it so however much i would like to say thanks to the author, this is just a comment that he should try and make things a bit easier for learners because its learners who always look for help from good hearted people like this author who are willing to share whatever they know fro free.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>