Tony,
The others have shown you quite good ways of doing what you asked, but here
is my personal slant on the topic - through a method that I think works
much more efficiently due to only requiring one query.
Basically you will require an additional field in your table, a 'level'
field. eg, Boss would be 1, Manager 2, Employee 3, Coffee Maker 4 ..etc.
This way you can have a 'looping' sql query that knows how many levels to
search out due to the 'level'. Ps. in your table the first entry (with ID 1)
is a dummy entry.
<cfparam name="url.id" default="1">
<cfparam name="url.lvl" default="1">
<Cfset iID = url.id>
<cfset iLvl = url.lvl>
<Cfquery name="GetCategories" datasource="#attributes.datasource#">
SELECT
<cfloop from="1" to="#iLvl#" index="i">
C#i#.category_id as c#i#_id,
C#i#.category_name as c#i#_name,
C#i#.category_level as c#i#_lvl,
C#i#.category_parentid as c#i#_parentid
<cfif i NEQ iLvl>,</cfif>
</cfloop>
FROM
<cfloop from="1" to="#iLvl#" index="i">
category_details C#i#<cfif i NEQ iLvl>, </cfif>
</cfloop>
WHERE
c1.category_parentid = 1
<cfif iLvl GT 1>
<cfset i2 = iLvl - 1>
AND C#i2#.category_id = #iID#
<cfloop from="2" to="#iLvl#" index="i">
<Cfset i2 = i - 1>
AND c#i#.category_parentid = c#i2#.category_id
</cfloop>
</cfif>
ORDER BY
<cfloop from="1" to="#iLvl#" index="i">
C#i#.category_name<cfif i NEQ iLvl>, </cfif>
</cfloop>
</cfquery>
<Cfquery name="GetCategory" datasource="#attributes.datasource#">
SELECT C1.category_name as c1_name, C1.category_parentid as c1_pid,
C1.category_level as c1_lvl, C1.category_id as c1_id
FROM category_details C1 WHERE c1.category_id = #iID#
</cfquery>
<table border=1 bordercolordark="#dfdfdf" bordercolorlight="#9a9a9a"
cellpadding=2 cellspacing=0 width=500 align="center">
<tr align=middle bgcolor="#c8c8c8">
<td bgcolor="#c8c8c8" align="center" colspan="2">
<font color="white" face="Arial, Helvetica, sans-serif" size="2" class="
formtitle">
Categories in '<cfoutput>#GetCategory.c1_name#</cfoutput>'
</font>
<table border=1 bordercolordark="white"
bordercolorlight="silver" cellpadding=3 cellspacing=0 width="100%">
<tr>
<td bgcolor="#DEDEDE" width="100%" align="left" valign="top"
colspan="2">
<table width="100%" cellspacing="0" cellpadding="0">
<tr>
<Td><font color="white" size="2" face="arial"
class="fieldname">Categor<cfif getcategories.recordcount GT
1>ies<cfelse>y</cfif></font></td>
<td align="right">
<cfif iID NEQ 1>
<cfoutput>
<a href="main.cfm?action=Edit-C&cpar=#GetCategory.c1_pid#">Up
Level</a>
<font size="1" color="silver">|</font>
<a href="main.cfm?action=Edit-C">Home</a>
</cfoutput>
</cfif>
</td>
</tr>
</table>
</td>
</tr>
<cfloop from="1" to="#ilvl#" index="i">
<cfoutput group="c#i#_name" query="getcategories">
<cfscript>
c_name = evaluate("c" & i & "_name");
c_id = evaluate("c" & i & "_id");
c_lvl = evaluate("c" & i & "_lvl") + 1;
</cfscript>
<tr>
<td bgcolor="##E8E8E8" align="center" valign="top">
#i#
</td>
<td bgcolor="##E8E8E8" align="left" valign="top" width="93%">
<a href="createcategorylist2.cfm?id=#c_id#&lvl=#c_lvl#"
class="storelink">#c_name#</a></font>
</td>
</tr>
</cfoutput>
</cfloop>
</table>
</td>
</tr>
</table>
> Date: Thu, 17 May 2001 09:08:55 -0400
> From: "Dean H. Saxe" <[EMAIL PROTECTED]>
> To: [EMAIL PROTECTED]
> Subject: RE: Help
> Message-ID: <a05100900b7297c7450a4@[66.32.88.36]>
>
> Joseph and Tony,
>
> There is quite an elegant solution to what is one of the more complex
> data structures you will encounter in a relational DB. Joe Celko has
> described the "nested set model" which uses relational DB concepts to
> describe the structure of a tree (which is what you have in this
> case, one parent leads to 1..N childrend which each have 1..N
> children ad infinitum) in his book SQL for Smarties. I won't
> describe it here, it would take me too long to type it out. Instead,
> look at this article written by Joe himself.
> http://www.intelligententerprise.com/001020/celko.shtml
>
> Is it confusing? Yes! At first it took me a few hours to understand
> the model and how it works. Many of my coworkers were confounded by
> this representation when I first presented it to them. However, it
> works in terms of ease of use and performance once you understand the
> basic premise. I actually think this is one of the coolest DB tricks
> I have seen!
>
> Hope that helps!
>
> -dhs
> --
> Dean H. Saxe
> [EMAIL PROTECTED]
> http://www.FullFrontalNerdity.com/
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
