[skyhawk133]

Just a quick little tutorial on how to create display nested categories from a database using recursion and components. This is the simplest way I've found to accomplish recursion in ColdFusion, but feel free to comment if you know a better way.

In this tutorial, we are working with a single table in a SQL database with 3 columns: id, parent, name.

Here is the database structure:

categories
-----------------------------------
id (int, auto-increment, primary key)
parent (int)
name (nvarchar)

Create the component

Create a new component called categories.cfc, start with this structure:

<cfcomponent name=&#34;Categories&#34;&#62;
	<cffunction name=&#34;getCats&#34;&#62;
	
  <!--- Parent ID ---&#62;
  <cfargument name=&#34;parent&#34; type=&#34;numeric&#34;&#62;
  
  
	</cffunction&#62;
</cfcomponent&#62;

As you may already know, recursion simply calls the same function from within itself. The beauty of using components is there is no need to number or name our function or objects because each call creates a new instance of the object.

Setup the query

In the code above, we've created a function called getCats, it accepts 1 argument: parent. We will use this argument to query for child categories.

<cfcomponent name=&#34;Categories&#34;&#62;
	<cffunction name=&#34;getCats&#34;&#62;
	
  <!--- Parent ID ---&#62;
  <cfargument name=&#34;parent&#34; type=&#34;numeric&#34;&#62;

  <!--- Query for Children of Parent ---&#62;
  <cfquery name=&#34;qCats&#34; datasource=&#34;YOUR_DATASOURCE&#34;&#62;
  SELECT * FROM categories WHERE parent = '#arguments.parent#'
  </cfquery&#62;	
  
	</cffunction&#62;
</cfcomponent&#62;

Create the output

Now that we've setup a query to get all the nested (child) categories, we should setup the output. We'll do this by setting text to a output variable which will be returned. Remember, you should never need to display anything from inside your functions, it should be returned using cfreturn.

<cfcomponent name=&#34;Categories&#34;&#62;
	<cffunction name=&#34;getCats&#34;&#62;
	
  <!--- Parent ID ---&#62;
  <cfargument name=&#34;parent&#34; type=&#34;numeric&#34;&#62;

  <!--- Query for Children of Parent ---&#62;
  <cfquery name=&#34;cats&#34; datasource=&#34;YOUR_DATASOURCE&#34;&#62;
  SELECT * FROM categories WHERE parent = '#arguments.parent#'
  </cfquery&#62;	

  <!--- Create Output ---&#62;
  <cfset output = &#34;<ul&#62;&#34;&#62;
  
  <!--- Loop over all items ---&#62;
  <cfloop query=&#34;cats&#34;&#62;
	  <cfset output = &#34;#output# <li&#62;#cats.name#</li&#62;&#34;&#62;
	  
	<!--- Check Children of this item ---&#62;
	<cfif cats.recordcount GT 0&#62;
		<cfset output = &#34;#output# #CreateObject&#40;'component', 'categories'&#41;.getCats&#40;cats.id&#41;#&#34;&#62;
	</cfif&#62;
	
  </cfloop&#62;
  <cfset output = &#34;#output#</ul&#62;&#34;&#62;
  
  <cfreturn output /&#62;


	</cffunction&#62;
</cfcomponent&#62;

We did a lot in that step, but it's really pretty simple. We've looped over the query appending text to our output variable and using #CreateObject('component', 'categories').getCats(cats.id)# we call the component and function from within itself to get child categories for the category the query is currently on. After the loop is finished, we return the output variable.

Putting it on a page

Save your categories.cfc and create a new .cfm file. This is where you'll call your recursive function and display your categories in a nicely organized list.

<cfoutput&#62;
#CreateObject&#40;'component', 'categories'&#41;.getCats&#40;0&#41;#
</cfoutput&#62;

We instantiate the categories component we just made and call the getCats function. When you created your table to query categories from, all of your root (top-level) categories should have a parent of 0, passing 0 to the parent argument starts at the very top of your nesting and begins to recurse down.

You should see something like this:

• Parent 1
• Child 1.1
• Child 1.2
• Child 1.2.1
• Child 1.2.1.1
• Child 1.2.1.1.1
• Child 1.2.1.2
• Child 1.2.1.3
• Child 1.2.1.3.1
• Parent 2

[sadara]

thanks for this tutorial! after hours of banging my head against a brick wall, i have a working script.

[billybrag]

hello there,
i tried this and it works ok, but produces lots of empty UL's

please can you help me to sort them - an eg of this in action is here... www.organiclinker.com/test.cfm

thanks
Mike

This post has been edited by billybrag: 23 August 2006 - 03:55 AM

[skyhawk133]

You may need to add some code in the output to check for an empty value. Not sure why it would be outputting empty UL's. Do you have anything in your database that might cause this?!

[billybrag]

skyhawk133, on 23 Aug, 2006 - 06:54 AM, said:

You may need to add some code in the output to check for an empty value. Not sure why it would be outputting empty UL's. Do you have anything in your database that might cause this?!

No - not that i can see - all i have changed from your code is the variable names to match my db?

it seems to be a problem with the logic?

[monizzle]

I made a few changes to skyhawk133's code so that it produces valid html. (see this url for an explanation of proper list nesting: http://www.w3schools...xhtml_html.asp)

In skyhawk's original code the child ul's were not nested within the parent li's. There may be a more elegant way to do what I have done, so please chime in if you've got one.

skyhawk133: Thanks for the original post!

<!--- Create Output --->
<!--- Monizzle: The following IF wrapper makes sure an empty <ul> isn't returned if no children are found with the default parent id  --->
  <cfif qCats.recordcount GT 0> 
  <cfset output = "<ul>">
  
  <!--- Loop over all items --->
  <cfloop query="cats">
	<!--- Monizzle: Removed closing </li> from output below to properly nest child <ul>s  --->
	  <cfset output = "#output# <li>#cats.name#">
	  
	<!--- Check Children of this item --->
	<cfif cats.recordcount GT 0>
		<cfset output = "#output# #CreateObject('component', 'categories').getCats(cats.id)#">
	</cfif>
	<!--- Monizzle: Add closing </li> to the output  ---> 		
	<cfset output = "#output#</li>">
  </cfloop>
  <cfset output = "#output#</ul>">
  
  <cfreturn output />
<!--- Monizzle: close IF wrapper --->
</cfif>

[dduck1934]

instead of Creating a new Object everytime, couldnt you change this line..

<cfset output = "#output# #CreateObject('component', 'categories').getCats(cats.id)#">


to just <cfset output = "#output# #getCats(cats.id)#">

[ravi bhalgami]

Misty, on 06 May 2010 - 09:15 AM, said:

dduck1934, on 13 April 2008 - 05:09 PM, said:

instead of Creating a new Object everytime, couldnt you change this line..

<cfset output = "#output# #CreateObject('component', 'categories').getCats(cats.id)#">


to just <cfset output = "#output# #getCats(cats.id)#">

Weel Its quite late i am making a reply here, but even using the updated code, i still get empty records and only one of my category display nothing else:

Here is what i am trying to do!

<cffunction name="getCats" returntype="any">
  <cfargument name="parentid" type="numeric" default="0">
  <cfquery name="qCats" datasource="#request.dsn#" username="#request.user#" password="#request.pass#">
  		SELECT * FROM categories 
        WHERE parentID = <cfqueryparam cfsqltype="cf_sql_numeric" value="#arguments.parentid#">
  		</cfquery>
  <cfif qCats.recordcount GT 0>       
  <cfset output = "<ul>">
  <cfloop query="qCats">
    <cfset output = "#output# <li>#qCats.Category#">
    <cfif qCats.recordcount GT 0>
      <cfset output = "#output# #getCats(qcats.catID)#">  
    </cfif>
    <cfset output = "#output#</li>">
  </cfloop>
  <cfset output = "#output#</ul>">
  <cfreturn output>
  </cfif>
</cffunction>

Calling it like This:

<cfset tools = CreateObject('component', '#cfcPath#.categories')>
<cfset getAll = #tools.getCats(parentID=0)#>
<cfoutput>#getAll#</cfoutput>

it is showing like this:

<ul> <li>Business <ul> <li>Directories </li> <li></li></ul></li> <li></li> <li></li> <li></li> <li></li> <li></li> <li></li> <li></li> <li></li> <li></li></ul>