4 Replies Latest reply on May 31, 2007 1:31 PM by insuractive

    Problem with nested categories

    mgelber
      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.


        • 1. Re: Problem with nested categories
          Dan Bracuk Level 5
          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.
          • 2. Problem with nested categories
            mgelber Level 1
            Solved it myself, I think. I found this: http://www.dreamincode.net/forums/index.php?s=7408f77eb1a86f20d63b797d73181792&showtopic=1 5185

            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.
            • 3. Re: Problem with nested categories
              Dan Bracuk Level 5
              cfsilent and cfsetting are useful for reducing whitespace.
              • 4. Re: Problem with nested categories
                insuractive Level 3
                Don't forget <cffunction output="No"> as well.