What is the best way to show categories (and their children, and their children and those children)

Today I ran by a forum post on EasyCFM.COM (see it here) that asked for help in displaying categories. This can be tricky; especially if you have many parent/child relationships. So I thought I would post a blog entry talking about the best way I have found to do this... Here goes nothing..

First you need to create the database for it... so lets make a table and cll it "categories".

In that table you will need to have the following items:

categoryID int identity (autonumber).
category   nvarchar(50) (string/text)
parentcategoryID int (number)

Then in that database; we will put in a few records (Let's do (3) layers).

CategoryID    Category             ParentCategoryID
1             Automotive           0
2             Real Estate          0
3             Used                 1
4             New                  1
5             For Sale             2
6             For Rent             2
7             Homes                5
8             Apartments           5
9             Homes                6
10            Apartments           6

Now notice that you have two records that are "master" records... Or top level which have a parentID of "0". This means that these are the ones that will show up in bold and are special :)

Now inside of that we created a (3) level one as follows, the other one has only two levels:

Real Estate
   > For Sale
       > Homes
       > Apartments
   For Rent
       > Homes
       > Apartments

Now to show them on a page all the way down, can be tricky if you code it; since it will require you to loop through the ones with a "0" and then do another query and loop through it and then do it again and again... so how do you accommodate for as many levels as you may need.. (imagine one has 40 levels deep)....

This is wehre custom tags really do come in handy... You can do a custom tag that will call itself over and over and over until it doesnt need to anymore... So how do you ask?

Well, let's show you...

First lets create a custom tag and let's call the file "categories.cfm"; on this page lets put this code:

That's pretty much it... now on the main page (where you want the parent categories to always show up) let's call the custom tag with a ParentCategoryID of "0" (again, remember this is the master records).

How easy was that? Now this baby can go 1, 2, 10, 40, 60, 100 levels deep and you don't have to do anyhing else... Sweeet..

If you want to only force it to go "2" levels down... you can do this:

That will only run one time when you call the parent caegories... then the parent's grandchildren will not be show; because when you call that tag again for the third time.. the ParentCategoryID will be higher then zero... make sense?

Let me know your thoughts....

-P