This has been bugging me for ages – how to take a query object, and loop it into an unordered/ordered list, with the script working out when the various ‘LI’ and ‘UL’ elements should be closed or opened.

I didn’t want to specify a parent item in the query, all I wanted to do is specify the depth – i.e how many levels from the top the LI element is.

So here, for the greater good (or bad, depending if you like/agree with my code) is my solution….

<cfsilent>
<cfscript>
	menu=querynew("id,name,link,depth,sortorder", "integer,varchar,varchar,integer,integer");
    queryaddrow(menu, 10);
	
        counter=1;
        QuerySetCell(menu, "id", counter, counter);
        QuerySetCell(menu, "name", "Home", counter);
        QuerySetCell(menu, "link", "/", counter);
        QuerySetCell(menu, "depth", 1, counter);
		QuerySetCell(menu, "sortorder", counter, counter);
         counter=counter+1;
        QuerySetCell(menu, "id", counter, counter);
        QuerySetCell(menu, "name", "Home", counter);
        QuerySetCell(menu, "link", "/", counter);
        QuerySetCell(menu, "depth", 1, counter);
		QuerySetCell(menu, "sortorder", counter, counter);
         counter=counter+1;
        QuerySetCell(menu, "id", counter, counter);
        QuerySetCell(menu, "name", "Home", counter);
        QuerySetCell(menu, "link", "/", counter);
        QuerySetCell(menu, "depth", 2, counter);
		QuerySetCell(menu, "sortorder", counter, counter);
         counter=counter+1;
        QuerySetCell(menu, "id", counter, counter);
        QuerySetCell(menu, "name", "Home", counter);
        QuerySetCell(menu, "link", "/", counter);
        QuerySetCell(menu, "depth", 2, counter);
		QuerySetCell(menu, "sortorder", counter, counter);
         counter=counter+1;
        QuerySetCell(menu, "id", counter, counter);
        QuerySetCell(menu, "name", "Home", counter);
        QuerySetCell(menu, "link", "/", counter);
        QuerySetCell(menu, "depth", 3, counter);
		QuerySetCell(menu, "sortorder", counter, counter);
         counter=counter+1;
        QuerySetCell(menu, "id", counter, counter);
        QuerySetCell(menu, "name", "Home", counter);
        QuerySetCell(menu, "link", "/", counter);
        QuerySetCell(menu, "depth", 1, counter);
		QuerySetCell(menu, "sortorder", counter, counter);
         counter=counter+1;
        QuerySetCell(menu, "id", counter, counter);
        QuerySetCell(menu, "name", "Home", counter);
        QuerySetCell(menu, "link", "/", counter);
        QuerySetCell(menu, "depth",2, counter);
		QuerySetCell(menu, "sortorder", counter, counter);
         counter=counter+1;
        QuerySetCell(menu, "id", counter, counter);
        QuerySetCell(menu, "name", "Home", counter);
        QuerySetCell(menu, "link", "/", counter);
        QuerySetCell(menu, "depth", 3, counter);
		QuerySetCell(menu, "sortorder", counter, counter);
         counter=counter+1;
        QuerySetCell(menu, "id", counter, counter);
        QuerySetCell(menu, "name", "Home", counter);
        QuerySetCell(menu, "link", "/", counter);
        QuerySetCell(menu, "depth", 2, counter);
		QuerySetCell(menu, "sortorder", counter, counter);
         counter=counter+1;
        QuerySetCell(menu, "id", counter, counter);
        QuerySetCell(menu, "name", "Home", counter);
        QuerySetCell(menu, "link", "/", counter);
        QuerySetCell(menu, "depth", 1, counter);
		QuerySetCell(menu, "sortorder", counter, counter);
    </cfscript> 
    
<cffunction name="createListFromQuery" returntype="string" access="public" output="no">
    <cfargument name="menu" type="query">
    <cfargument name="type" type="string" default="ul">
    <cfset var previousRowDepth=1>
    <cfset var nextrowDepth=1>
    <cfsavecontent variable="output">
	<cfoutput> 
    <#arguments.type#>
    <cfloop query="menu">
    <cfquery name="query" type="query">
    SELECT * FROM arguments.menu WHERE id=<cfqueryparam cfsqltype="cf_sql_integer" value="#(currentrow + 1)#">
    </cfquery>
     
         <cfscript>
		 if (query.recordcount == 1){
		 	nextrowDepth=query.depth;
		 }
	 	if (depth == previousRowDepth && depth == nextrowDepth && currentrow != 1){
      		writeoutput("</li>");}
		if (depth > previousRowDepth){
			for(i=1; i <= #(Depth - previousRowDepth)#; i++) {
      			writeoutput("<#arguments.type#>");}
      	} 		
		if (depth < previousRowDepth){
			for(i=1; i <= #(previousRowDepth - Depth)#; i++) {		
				writeoutput("</#arguments.type#></li>");
			 }
		}
		writeoutput("<li><a href=""#link#"">#name# (Depth = #Depth#)</a>");
		 if (query.recordcount ==1) {
		  if (depth > previousrowDepth && depth >= nextrowDepth){
			writeoutput("</li>");}
		  if (previousRowDepth > depth && depth > nextrowDepth){
			writeoutput("</li>");}
		  if (nextrowDepth = depth && currentrow == 1){
			writeoutput("</li>");}
		  }
		  else if (currentrow == arguments.menu.recordcount) {
			  for(i=1; i <= #(previousRowDepth - Depth)#; i++) {
					writeoutput("</li></#arguments.type#>");}
					}			 
		  previousRowDepth=Depth;
		  </cfscript> 
       
    </cfloop>
	</cfoutput>
    </cfsavecontent>
    <cfreturn output />
</cffunction>
</cfsilent>
<cfoutput> 
<html>
<head>List Demo</head>
<body> 
#createListFromQuery(menu, 'ul')# 
</body>
</html> 
</cfoutput>

Ideas on how I might improve this to be more efficient welcomed!