query to unordered list

Ketan Jetty
enthusiasm for technology

query to unordered list

Convert a hierarchy query to an unordered list

I would like to dedicate this solution to Tom King [House of Fusion ref: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:61844]. This is a very good concept and most programmers may need it. I did similar work for a recent project and thought that it would be a good idea to share this solution with the coldfusion community. I used the sample hierarchy query code from Tom King and added a simple cfloop within cfloop logic to convert the hierarchy query to unordered list.
Query to Unordered list

Code to convert hierarchy query to unordered list [ul li]
<!--- sample hierarchy query --->
<cfscript>
	counter=1;
	menuQ=queryNew("ID,menuItemName,link,depth,parentID");
	queryAddRow(menuQ);
	querySetCell(menuQ, "ID", 1, counter);
	querySetCell(menuQ, "menuItemName", "One", counter);
	querySetCell(menuQ, "link", "/one/", counter);
	querySetCell(menuQ, "depth", 1, counter);
	querySetCell(menuQ, "parentID", "0", counter);
	
	counter=(counter+1);
	queryAddRow(menuQ);
	querySetCell(menuQ, "ID", 2, counter);
	querySetCell(menuQ, "menuItemName", "Two", counter);
	querySetCell(menuQ, "link", "/two/", counter);
	querySetCell(menuQ, "depth", 2, counter);
	querySetCell(menuQ, "parentID", "1", counter);
	
	counter=(counter+1);
	queryAddRow(menuQ);
	querySetCell(menuQ, "ID", 3, counter);
	querySetCell(menuQ, "menuItemName", "Three", counter);
	querySetCell(menuQ, "link", "/three/", counter);
	querySetCell(menuQ, "depth", 2, counter);
	querySetCell(menuQ, "parentID", "1", counter);
	
	counter=(counter+1);
	queryAddRow(menuQ);
	querySetCell(menuQ, "ID", 4, counter);
	querySetCell(menuQ, "menuItemName", "Four", counter);
	querySetCell(menuQ, "link", "/four", counter);
	querySetCell(menuQ, "depth", 1, counter);
	querySetCell(menuQ, "parentID", "0", counter);
	
	counter=(counter+1);
	queryAddRow(menuQ);
	querySetCell(menuQ, "ID", 5, counter);
	querySetCell(menuQ, "menuItemName", "Five", counter);
	querySetCell(menuQ, "link", "/five/", counter);
	querySetCell(menuQ, "depth", 2, counter);
	querySetCell(menuQ, "parentID", "4", counter);
	
	counter=(counter+1);
	queryAddRow(menuQ);
	querySetCell(menuQ, "ID", 6, counter);
	querySetCell(menuQ, "menuItemName", "Six", counter);
	querySetCell(menuQ, "link", "/six", counter);
	querySetCell(menuQ, "depth", 3, counter);
	querySetCell(menuQ, "parentID", "5", counter);
	
	counter=(counter+1);
	queryAddRow(menuQ);
	querySetCell(menuQ, "ID", 7, counter);
	querySetCell(menuQ, "menuItemName", "Seven", counter);
	querySetCell(menuQ, "link", "/seven/", counter);
	querySetCell(menuQ, "depth",  4, counter);
	querySetCell(menuQ, "parentID", "6", counter);
	
	counter=(counter+1);
	queryAddRow(menuQ);
	querySetCell(menuQ, "ID", 8, counter);
	querySetCell(menuQ, "menuItemName", "Eight", counter);
	querySetCell(menuQ, "link", "/eight/", counter);
	querySetCell(menuQ, "depth", 1, counter);
	querySetCell(menuQ, "parentID", "0", counter);
</cfscript>

<!--- generate the unordered list [ul li] :: PH is place holder --->
<cfset mainString = "<!---PH0--->" />

<cfquery name="qsParentIDs" dbtype="query">
	select distinct parentID from menuQ order by parentID
</cfquery>

<cfloop query="qsParentIDs">
	<cfquery name="qsTemp" dbtype="query">
		select * from menuQ where parentID = #qsParentIDs.parentID#
	</cfquery>
	
	<cfset temp = "<ul>" />
	<cfloop query="qsTemp">
		<cfset temp = temp & "<li>#qsTemp.menuItemName# <!---PH#qsTemp.ID#---> </li>" />
	</cfloop>
	<cfset temp = temp & "</ul>" />
	
	<cfset mainString = Replace(mainString, "<!---PH#qsParentIDs.parentID#--->", temp) />
</cfloop>

<!--- display the query dump and unordered list --->
<table>
<tr>
	<td><cfdump var="#menuQ#" label="hierarchy query" /></td>
	<td><cfoutput>#mainString#</cfoutput></td>
</tr>
</table>

coldfusion


CF Quick Reference


Ginger CMS
the future of cms, a simple, easy and intutive content management system ... more


CFTurbine
cf prototyping engine, generates boilerplate code and ... more


Jrun monitor
monitor and timely auto-restart to avoid Jrun hang ... more


Inheritance Config.
uses OOPs inheritance to create configuration file ... more


Real Estate App.
complete real estate application using data from MLS ... more


Search Engine Lite
create your own search engine for your web site ... more