Problem with nested categories

[Guest]

I'm trying to create a database-driven menu bar. I'm new to XML, CF, and Spry, so I was hoping someone could point me to a reference.

I have no problem creating a one layer menu like this:
Category1
Category2
Category3

using this:
<ul id="MenuBar1" class="MenuBarHorizontal">
<cfloop query="DataBaseCategories">
<cfoutput>
<li><a href="##">#name#</a></li>
</cfoutput>
</cfloop>
</ul>

But I can't figure out how to handle the nested sub-menus.

My database table has the following columns:
CategoryID
Name
ParentID

With first-level categories having a parentID of 0

Any suggested references would be great, I'm sure this is a common task but I don't know how to handle.

I have a feeling that the solution lies in exporting the recordset to properly formatted XML and using the XML as the source for the menu bar (instead of using the recordset direclty) would make the problem trivial. Any suggestions on script that will export nested recordsets like this to XML? "Export Recordset As XML" in the Dreamweaver Developer Toolbox doesn't seem to do it.

[Dan_Bracuk]

The group attribute of the cfoutput tag might work. Details are in the cfml reference manual. If you don't have one, the internet does.

[Guest]

Solved it myself, I think. I found this: http://www.dreamincode.net/forums/index.php?s=7408f77eb1a86f20d63b797d73181792&showtopic=15185

The code shown there required some rejiggering for the menubar formatting.


First I created this .cfc file:


<cfcomponent displayname="categories" bindingname="categories">
<cffunction name="getCats" access="public" returntype="string">
<cfargument name="parentid" type="numeric" required="no" default="0">

<cfquery name="qCats" datasource="DSN">
SELECT catid, name, parentid FROM tablecategory WHERE parentid = #arguments.parentid# ORDER BY name
</cfquery>

<!--- Create Output --->
<cfset output = "">
<!--- Loop over all items --->
<cfloop query="qCats">
<cfset childoutput = "">

<!--- Check Children of this item --->
<cfif qCats.recordcount GT 0>
<cfset childoutput = "#CreateObject('component', 'categories').getCats(qCats.catid)#">
</cfif>

<cfif #childoutput# CONTAINS "<a">
<cfset output = "#output# <li><a class=""MenuBarItemSubmenu"" href=##>#qCats.name#</a><ul>#childoutput#</ul></li>">
<cfelse>
<cfset output = "#output# <li><a>#qCats.name#</a></li>">
</cfif>
</cfloop>
<cfreturn output />
</cffunction>
</cfcomponent>

In my .cfm file I inserted a menu bar and changes the code to:
<ul id="MenuBar1" class="MenuBarHorizontal">
<cfoutput>
#CreateObject('component', 'categories').getCats(0)#
</cfoutput>
</ul>

Seems to work fine except it creates a ton of whitespace. Not sure why, but I'm sure I can find a solution.

[Dan_Bracuk]

cfsilent and cfsetting are useful for reducing whitespace.

[insuractive]

Don't forget <cffunction output="No"> as well.