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:
<!--- this is a custom tag that will loop over itself and show categories and their children --->
<!--- first let's allow you to pass in a variable that will tell the system which categories to show --->
<cfparam name="attributes.ParentCategoryID" default="0" />
<!--- now let's hit the database and call the records --->
<cfquery name="qGetCategories" datasource="#request.dsn#">
select categoryID, category
from categories
where ParentCategoryID = #val(attributes.ParentCategoryID)#
</cfquery>
<!--- now let's show the categories --->
<cfoutput query="qGetCategories">
<a href="apage.cfm?categoryID=#val(categoryID)#">#category#</a><br />
<!--- now here goes teh magic... here let's have it call itself (the same page) but with a different ParentCategoryID ID value so it gets this records children --->
<cf_categories ParentCategoryID="#categoryID#">
</cfoutput>
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).
<cf_categories ParentCategoryID="0">
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:
<!--- now let's show the categories --->
<cfoutput query="qGetCategories">
<a href="apage.cfm?categoryID=#val(categoryID)#">#category#</a><br />
<!--- now here goes teh magic... here let's have it call itself (the same page) but with a different ParentCategoryID ID value so it gets this records children --->
<cfif attributes.ParentCategoryID EQ 0>
<cf_categories ParentCategoryID="#categoryID#">
</cfif>
</cfoutput>
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
