How to manage multi level categories in PHP and MySQL

For the websites it is common to manage articles, products and much more thing by categories. And these categories have child categories and these child also have child and so in.

So this article will show you that how to manage the Multi-level categories tree in PHP and MySql.

First of all we need a Database table to store all categories.

 CREATE TABLE `tblcategory` (  
 `id` int(10) NOT NULL auto_increment,  
 `pid` int(10) NOT NULL default '0',  
 `title` varchar(255) NOT NULL,  
 PRIMARY KEY (`id`)  
 );  

Multilevel categories tree in database table.



Now time to get list of all categories with parent and their child, grand child and so on. There are two way to find all categories.

1. First solution that all knows, Recursion  

In Recursion, first get all main parents categories and then start a loop and fetch all subcategories with their parent id's.

 function showCategories($cat_id = 0, $dashes = ''){  
      $dashes .= '--';  
      $rsSub = mysql_query("SELECT id, pid, title FROM tblcategory WHERE pid = " . $cat_id) or die(mysql_error());  
      if(mysql_num_rows($rsSub) > 0){  
           while($rows_sub = mysql_fetch_array($rsSub)){  
                echo $dashes . $rows_sub['title'] . "</br>";  
                if($rows_sub['pid'] != 0){  
                     showSubCategories($rows_sub['id'], $dashes);  
                }  
           }  
      }  
 }  

2. Second solution, We can also get all categories by PHP REFERENCES.

By this method we fetch all categories at a time and set them with their parents with the help of Reference.

 $refs = array();  
 $list = array();  
 $sql = "SELECT item_id, parent_id, name FROM items ORDER BY name";  
 $result = mysql_query($sql);  
 while($data = @mysql_fetch_assoc($result)) {  
      $thisref = &$refs[ $data['item_id'] ];  
      $thisref['parent_id'] = $data['parent_id'];  
      $thisref['name'] = $data['name'];  
      if ($data['parent_id'] == 0) {  
           $list[ $data['item_id'] ] = &$thisref;  
      } else {  
           $refs[ $data['parent_id'] ]['children'][ $data['item_id'] ] = &$thisref;  
      }  
 }