Sagewire Logo

Outputting sub categories under parent from database

2 Message(s) by 2 Author(s) originally posted in cfml database access


From: pajonas Date:   Friday, October 26, 2007
Hello,

My current Cold Fusion website has a left hand navigation bar that I'd like
to have sub categories flyout with JAVAscript that I'll use. I am having
trouble query ing the data base to have the subcategories line up under their
parent s.

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'll see in my below code that has a condition but it
does not 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#Cou
nter#')"

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#Cou
nter#')"

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#Cou
nter#')"

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'd be much appreciated!!
Thanks


From: William At FAA Date:   Friday, October 26, 2007
Hi pajonas,

My take on this is that the easiest thing to do is hit your database one time
and get all the necessary data and then use query of queries to get the
subcategories as necessary. This is good for performance reasons (more trips to
the DB is always a pain). Note that this works for the 2-level deep scheme you
seem to have now, if you need to recurse further this strategy would've to be
adjusted. This isn't necessarily a highly scalable solution , but something I
think is a good solution given your expertise level.

I have attached a simple example with nested lists so we can easily see that we
are nesting properly.

Again, if you need [i]n[/i] levels deep of subcategories (as I imagine most
implementations will) we'd need an alternate approach, but if you're
looking for a quick and dirty two deep scheme, this will do. Let me know if you
have questions on this.
<CFQUERY name="qGetCategoryList" datasource="#datasource#">
SELECT c.cId, c.categoryName, c.parentCategory, c.visible
FROM category c
WHERE c.visible = 1
ORDER BY c.categoryName ASC
</CFQUERY>

<ul>
<CFOUTPUT query="qGetCategoryList">
<!--- Only output at this level if it has no parent --->
<CFIF qGetCategoryList.parentCategory EQ 0>
<li>#qGetCategoryList.categoryName#
<CFQUERY name="qGetSubCategory" dbtype="query">
SELECT cId, categoryName, parentCategory
FROM qGetCategoryList
WHERE visible = 1
AND parentCategory = <CFQUERYPARAM cfsqltype="cf_sql_integer"
value="#qGetCategoryList.cId#" />

ORDER BY categoryName ASC
</CFQUERY>
<CFIF qGetSubCategory.recordCount GT 0>
<ul>
<CFLOOP query="qGetSubCategory">
<li>#qGetSubCategory.categoryName#</li>
</CFLOOP>
</ul>
</CFIF>
</li>
</CFIF>
</CFOUTPUT>
</ul>



Next Message: Problem returning REF CURSOR from Oracle


Blogs related to Outputting sub categories under parent from database

Solar do Cicero foto 09
... diagram pyc import dll in python carl larsson singoalla praktupplaga zwischen himmel und erde musical elisabeth database and search and mdb zastava cz cytonia lodge oid osx mrtg beniaminek-kwiat physical examination ucla villeroy et ...

click here python untrusted interpreter delicate sound of thunder ...
... women window media player 11crack macroglossia and polycystic kidneys cni40 vs tvazteca logo lijst dedecker protectores tipo mondragon ejemplos resueltos transformada de laplace challenge python database api date time principles of ...

Comprehensive news articles from the magazine. Updated daily.
ready air Sir archie, william ransom johnson fianzierung morris family, bolton, lancashire, england wedding design planning sketches rabbit language. authors database. horse racing, 1823 great match who is considered one of the most ...

The magazine, published each week inside the paper.
omeda quicj connect coupler raised bed planter penns landing and movie under the stars and kicking 4 41162 2178 ahha night new bedford if you remove a flywheel will you loose oil? cb750 tank bubblegum arcade vanet ...

Click here to get all the information you need
nocturnal night club former chief sub of the bulawayo chronicle janet jackson pets airborne irig-b input/output video inserter overall structure of dbms furcifer mutondwe chinese lunar calendar phill burtt cheap hotels in buffalo ny ...

Dj
... water ski adjustment spinoff dj competition san francisco a2 w/adjustable cheek piece gimp adjustable brush adjustment for 1bbl carburator 6 speed shifter shaft adjustment harley cfdj editorial board coldfusion developer s journal ...


Programming | Sports | Autos

copyright 2006
Valid XHTML 1.0 Transitional