The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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:

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?

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