Creating a category tree from mysql database

PHP
1

Hi! I am building a php classified listing website and would like some help on how to create a category and subcategory tree to guide my visitors to the correct category they would like to view.

Database details:
I intend to use only one table - CATEGORIES TABLE with the following fields.

| ID | name | ParentID |

±---------------±--------|

| 1 | Cat1 | 0 |

| 2 | Cat2 | 0 |

| 3 | Cat3 | 0 |

| 4 | Subcat1 | 1 |

| 5 | Subcat2 | 2 |

| 6 | Subcat3 | 1 |

| 7 | Subcat4 | 3 |

| 8 | Subcat5 | 3 |

etc

This is the result i’m looking for:

Your help would be much appreciated.

Halfdogg

2

use recursion. pass the id as parameter. check if that id is a parent of other category. if yes call the function or stop.

3

I’m not that experienced in php. I need someone to walk me thru. Thanks.

4

Are your Categories to be polyhierarchical?

Would the same article appear in say, Business, Regional and Computers at the same time?

5

Hi cups,
No, they are not polyhierarchical. The main idea is that a subcategory is linked to only one main category so the category is listed as the head then it’s children listed below it separated by commas.

6 
<?php
function multilevel($id)
{
	$rs1=mysql_query("select * from category where pid='$id'");
	if(mysql_num_rows($rs1)==0) return;
	echo "<ul>";
	while($row1=mysql_fetch_array($rs1))
	{
	echo "<li>".$row1['name']."";
	multilevel($row1['id']);
	}
		echo "</ul>";


}
mysql_connect("*********","****","****");
mysql_select_db("test");
$rs=mysql_query("select * from category where pid='0'");
echo "<ul>";
while($row=mysql_fetch_array($rs))
{
	echo "<li>".$row['name']."";
	multilevel($row['id']);
}
echo "</ul>";
?>
7

What is the advantage/disadvantage of this method (ie 1 db table) vs having a table of Categories and a SubCategories table…

I normally use 2 table -eg Links and Link Types - I select the link details (url etc) and also the title of the link type and order by the link type…Then loop through all results only echoing the Link type heading if the link type is different to the last row that was processed…

What do people think?

8

suppose you r building a shopping cart and and you don’t know the exact number of subcategories then this technique comes handy. it’s specially for multiple sub-categories.

9

Jito,

can you ellaborate a little more? I am not sure that I understand :frowning:

Thanks

10

think of a situation when you dont know how many sub category are there. if you want to leave the control in the hands of administrator, when administrator can add categories, subcategories, its difficult to create a table each time specially when you r creating a subcategory under a subcategory.
i hope this explains, or you can question if u want something else.

11

Recursing a function that collects data from the database like that is - imo - a big no no. You should read this article:

12

yes i know it’s slow but i don’t know any alternative solution.

13

Jito - thanks for that - originally i thought that you meant the number of subcategories, but in fact you are actually referring to the levels in the hierarchy - categories, subcategories, sub-subcategories etc.

I understand - thanks again.

I will take a look at the article suggested by wysiwyg now.

Cheers

14

yes, recursion can be slow

have a look at this article – Categories and Subcategories

the mockup shown in post #1 will require only one LEFT OUTER JOIN, with GROUP_CONCAT to provide the comma-delimited list of subcategories