Display multi level category from mysql

PHP
1

Hi All,

I have a category table in mysql like that:
id (unique id of category)
pid (parent id of category, if 0 that means it is a parent category)
title (title of category)

Now from this table I want to display records in tree form (bcoz there may be as many levels as user wants)

  • Top Category
    – Child Category
    – Child Category
    — Grand Child Category
    — Grand Child Category
  • Top Category

How can I iterate through this category structure? Bcoz I cannot write many whiles inside whiles e.g.
while(){
while(){
while(){
AND SO ON …
}
}
}

What is the solution?

2

I would do it like this:
Table Structure:


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`)                                     
             );

PHP Code would be like this:


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

mysql_connect("localhost", "root", "raju") or die(mysql_error());
mysql_select_db("test");

# Get parent (main) categories parent_id=0;
$rsMain = mysql_query("SELECT id, pid, title FROM tblcategory WHERE pid=0") or die(mysql_error());
if(mysql_num_rows($rsMain) >= 1){
	while($rows_main = mysql_fetch_array($rsMain)){
		echo $rows_main['title'] . "<br />";
		showSubCategories($rows_main['id']);
	}
}
3

“there may be as many levels as user wants”

is this really true, or is there some upper limit, like, say, 15 levels deep?

if there is truly no maximum number of levels, then you have two choices: recursion (rajug’s solution) or the nested set data model

if, however, you have a realistic number of subcategories, then perhaps this article might help you – Categories and Subcategories