Demos

Query to Ordered/Unordered List

View code as plain text and without comments (.txt) (tested on Railo 3.2)
View code with no cfscript as plain text and without comments (.txt) (Tested on CF8)

First, some dummy data for testing / demo purposes: naturally, this would normally be a cfquery.

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

The Main Function:


<cffunction name="createListFromQuery" returntype="string" access="public" output="no">

<cfargument name="menu" type="query" hint="The Main Query which you want to display as a list">

<cfargument name="type" type="string" default="ul" hint="The list type, should be ol or ul">



<!--- Set some var scoped variables to use--->

<cfset var previousRowDepth=1>

<cfset var nextrowDepth=1>



<!--- I'm putting the output in cfsavecontent so the function can just return the string easily--->

<cfsavecontent variable="output">

<cfoutput>



<!--- Start the list --->


<#arguments.type#>



<!--- Start the loop --->


<cfloop query="menu">



<!--- Query of queries to get the data for the next row, so we can work out whether the current row has children---> 

<cfquery name="query" dbtype="query">

SELECT * FROM arguments.menu WHERE id=<cfqueryparam cfsqltype="cf_sql_integer" value="#(currentrow + 1)#">

</cfquery>



<cfscript>

// I could have referred to this as query.recordcount, but for ease of reading, am using 'nextRowDepth' 

if (query.recordcount == 1){

nextrowDepth=query.depth;

}

if (depth == previousRowDepth && depth == nextrowDepth && currentrow != 1){

writeoutput("</li>");}

if (depth > previousRowDepth){

// By looping, we can deal with multiple levels of li and ul elements 

for(i=1; i <= #(Depth - previousRowDepth)#; i++) {

writeoutput("<#arguments.type#>");}

} 

if (depth < previousRowDepth){

for(i=1; i <= #(previousRowDepth - Depth)#; i++) { 

writeoutput("</#arguments.type#></li>");

}

}

// The main list element: output on every loop pass 

writeoutput("<li><a href=""#link#"">#name# (Depth = #Depth#)</a>");

// Closing list elements: I could probably rewrite this as a single line, but for clarity have left it as 3 if statements

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>");}

}

// Bit of a hack for the last loop line in the recordset - the query won't have data, as there's no next row, but we still need to close the remaining li and ul elements 

else if (currentrow == arguments.menu.recordcount) {

for(i=1; i <= #(previousRowDepth - Depth)#; i++) {

writeoutput("</li></#arguments.type#>");}

}

// Set the current loop rows depth as "previousRowDepth" so we can reference it in the next pass 

previousRowDepth=Depth;

</cfscript>

<!---Close the loop---> 

</cfloop>

</cfoutput>

</cfsavecontent>

<!--- Return the cfsavecontent string---> 

<cfreturn output />

</cffunction>

Usage Example


<cfoutput>#createListFromQuery(menu, 'ul')#</cfoutput>

Or for an ordered list


<cfoutput>#createListFromQuery(menu, 'ol')#</cfoutput>