Recursive function to print out category hierarchy

**jameso** · Nov 14, 2004 05:25

Hi everyone.

I've got a mysql table called categories, with a few fields: as shown in the create statement below:

Code:

CREATE TABLE `categories` (
  `catID` int(11) NOT NULL auto_increment,
  `parentID` int(11) default NULL,
  `name` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`catID`)
)

It's for a system that needs to support unlimited subcategories. The root categories have a parentID of NULL, and the others have a parentID matching the catID of it's parent category. I want to write a fuction that prints out the entire category structure like this:

Code:

Web Design
  CSS
  HTML
Web Hosting
  Pre Sales
  Domain Names
  cPanel
    cPanel tutorials
  Email

The only way I can think of doing it is having lots of SELECT queries to find the child categories of each category. Is it possible to do this with one mysql SELECT query, and then manipulate the results in PHP to print an output similar to the one above?

Thanks in advance!

James

**Version0-00e** · Nov 14, 2004 06:03

Well you are lucky aren't you?

I had the same bother myself, and since then have come across a class off the web whereby you do one query, the results are sorted by ascending order of ID.

There after, you can build a tree structure which has some functionality such as to expand to a given level, expand all, collapse, etc. Even creates a breadcrumb path for you

I'm going to upload the original zip for you, enjoy

**jameso** · Nov 14, 2004 07:06

Thanks for that

I'm trying it out now.

Will let you know how I go.

**CapitalWebHost** · Nov 18, 2004 16:15

Just what the doctor ordered..lol. Spent all day working on this very thing..

**jameso** · Nov 19, 2004 22:23

I actually found this article - it's quite useful and I've changed my database structure because of it - http://www.sitepoint.com/article/hie...-data-database.

Its much more efficient than just having the parentID field - saves many mysql queries.

**Version0-00e** · Nov 20, 2004 04:26

Yep, though it does have the disadvantage of being a lot slower during updates. There is a thread started by me on this topic btw you might want to read?

Mkrz also posted his solution which was very convienent and useful

**MiiJaySung** · Nov 20, 2004 08:02

Personally, I would not use Celko's sets. It becomes very slow when you get lots of records for updating. If you want to design for scaleablity use...

http://fungus.teststation.com/~jon/t...eeHandling.htm

It works much better in most cases and you wouldn't have had to change your DB structure as much. I know that the pure DB / SQL geeks out there will say this goes against SQL because there is some redundent data. However with Celko's sets you really need to implement it with the unit of work (DB transaction). This is because if one of the UPDATE queries fails when inserting a node, then you leave your database in a totally inconsistent way. With the other method that I showed, you can stick to your old schema. Because you have an additional path table, you can use this for fast SELECTS. If the path table messes up, it is easy to reconstruct form the main data table.

The other advantage is my way is easier to implement than Celko's sets.

If you go down the Celko route, I would make your left / right values for the root node be as wide as possible, i.e. 0, and (2^32)-1. If you make sure each node has a fairly wide left right value you you do not need to do many update queries to shift the left / right values of nodes (This will be very rare).

There are other ways, if you use Oracle or another DB with good Stored Procs, then look at Tropashko's method if you are any good with Maths