Outputting sub categories under parent from database

Hello,

My current coldfusion website has a left hand navigation bar that I would like to have sub categories flyout with javascript that I will use. I am having trouble querying the database to have the subcategories line up under their parents.

My first query is below, this grabs the parent or first level of the left hand side bar:

<cfquery name="CATEGORY_LIST" datasource="#datasource#">
SELECT cID,CategoryName
FROM CATEGORY
WHERE Visible = 1
AND ParentCategory = 1
ORDER BY CategoryName ASC
</cfquery>



I have a second query to grab the children or second level:

<cfquery name="CATEGORY_LIST2" datasource="#datasource#">
SELECT cID,CategoryName,ParentCategory
FROM CATEGORY
WHERE ParentCategory > 1
AND Visible = 1
ORDER BY CategoryName ASC
</cfquery>


The first level of menu items have a ParentCategory that equals 1
The second level of menu items have a ParentCategory that equals whatever the Category Id of the first level is. Soo for example electronics has a ParentCategory of 1 and a cID of 22. Both iPods and TVs have ParentCategorys of 22.

Electronics
iPods
TVs

I loop through the first query and output and then i have another loop inside the main loop that outputs the children. I think I just need to somehow say that the second loop runs only when the Category ID (cID) equal the ParentCategory.



I have a loop as you will see in my below code that has a condition but it doesn't seem to work, nothing happens, like the code inside the loop is ignored. I believe I just need some condition like this:

<cfset one = CATEGORY_LIST.cID>
<cfset two = CATEGORY_LIST2.ParentCategory>
<cfloop condition="one EQ two">



Here is my code:


<cf_accelerate NoCache="False"
cachedWithin="#createTimeSpan(0,0,IEXP_PREFS.DatabaseCaching,0)#"
StripWhitespace="true"
primarykey="cNavMenu">

<cfquery name="CATEGORY_LIST" datasource="#datasource#">
SELECT cID,CategoryName
FROM CATEGORY
WHERE Visible = 1
AND ParentCategory = 1
ORDER BY CategoryName ASC
</cfquery>

<cfquery name="CATEGORY_LIST2" datasource="#datasource#">
SELECT cID,CategoryName,ParentCategory
FROM CATEGORY
WHERE ParentCategory > 1
AND Visible = 1
ORDER BY CategoryName ASC
</cfquery>

<!-- Define Layer Name -->
<div id="Category"
style="
position : absolute;
<cfif ShowTab IS "Cat">
visibility : visible;
<cfelse>
visibility : hidden;
</cfif>
left : 0px;
top : 250px;
height : auto;
width : 150;
">
<table border="0" cellspacing="0" cellpadding="0">
<cfoutput>
<cfset Counter = 1>
<cfloop query="CATEGORY_LIST">
<cfif CATEGORY_LIST.cID IS 100000>
<tr>
<td valign="middle"><img src="#WebPath#/images/spacer.gif" alt="" border="0" width="5" height="10"><img name="CatArrow#Counter#" src="#WebPath#/images/spacer.gif" width="6" height="9" alt="" border="0">&nbsp;&nbsp;</td>
<td><a class="navlist" href="#IEXP_PREFS.BaseURL##IEXP_PREFS.WebPath#/store/shields-intro.cfm"
onMouseOver="SwapLayer('#WebPath#/images/red_arrow.gif','Category','CatArrow#Counter#')"
onMouseOut="SwapLayer('#WebPath#/images/spacer.gif','Category','CatArrow#Counter#')">&nbsp;#CATEGORY_LIST.CategoryName#</a></td>
</tr>
<cfelse>
<tr>
<td valign="middle"><img src="#WebPath#/images/spacer.gif" alt="" border="0" width="5" height="10"><img name="CatArrow#Counter#" src="#WebPath#/images/spacer.gif" width="6" height="9" alt="" border="0">&nbsp;&nbsp;</td>
<td><a class="navlist" href="#ses_category_url(CATEGORY_LIST.cID,CATEGORY_LIST.CategoryName,"")#"
onMouseOver="SwapLayer('#WebPath#/images/red_arrow.gif','Category','CatArrow#Counter#')"
onMouseOut="SwapLayer('#WebPath#/images/spacer.gif','Category','CatArrow#Counter#')">&nbsp;#CATEGORY_LIST.CategoryName#</a></td>
</tr>

<cfset one = CATEGORY_LIST.cID>
<cfset two = CATEGORY_LIST2.ParentCategory>
<cfloop condition="one EQ two">
<cfloop query="CATEGORY_LIST2">

<tr style="margin-left:5px;background-color:ff0000;">
<td valign="middle"><img src="#WebPath#/images/spacer.gif" alt="" border="0" width="5" height="10"><img name="CatArrow#Counter#" src="#WebPath#/images/spacer.gif" width="6" height="9" alt="" border="0">&nbsp;&nbsp;</td>
<td><a class="navlist" href="#ses_category_url(CATEGORY_LIST2.cID,CATEGORY_LIST2.CategoryName,"")#"
onMouseOver="SwapLayer('#WebPath#/images/red_arrow.gif','Category','CatArrow#Counter#')"
onMouseOut="SwapLayer('#WebPath#/images/spacer.gif','Category','CatArrow#Counter#')">&nbsp;#CATEGORY_LIST2.CategoryName#</a></td>
</tr>

</cfloop>

</cfloop>
</cfif>
<cfset Counter = Counter + 1>
</cfloop>
</cfoutput>
</table>
</div>


Any help would be much appreciated!!
Thanks,
Jonah

 

Its done by using parent child system.
Look at : http://rickosborne.org/blog/index.php/2006/04/20/parent-child-queries-enhancement-stacked-numbering/ the provide detail guide

 

i think i would change what you're doing here, since you're already in a loop for category_list

<cfloop query="CATEGORY_LIST2">
	<cfif category_list2.parentCategory EQ category_list.cID>
		<tr style="margin-left:5px;background-color:ff0000;">
			<td valign="middle">
				<img src="#WebPath#/images/spacer.gif" alt="" border="0" width="5" height="10"><img name="CatArrow#Counter#" src="#WebPath#/images/spacer.gif" width="6" height="9" alt="" border="0">&nbsp;&nbsp;
			</td>
			<td>
				<a class="navlist" href="#ses_category_url(CATEGORY_LIST2.cID,CATEGORY_LIST2.CategoryName,"")#"
											onMouseOver="SwapLayer('#WebPath#/images/red_arrow.gif','Category','CatArrow#Counter#')"
											onMouseOut="SwapLayer('#WebPath#/images/spacer.gif','Category','CatArrow#Counter#')">&nbsp;#CATEGORY_LIST2.CategoryName#</a>
			</td>
		</tr>
							
	</cfif>
								
</cfloop>

Code (markup):

 

Thanks guys!