Don't know if this will help you....it's a similar (but not exact) app.
I'ts purpose is to find all the subordinates to a manager within a company
using the same db structure that you have (ID, parentID). Perhaps you can
modify it (?)
The ID is "userID", the category is "username" and the parentID is
"reportstouserID".
<cfparam name=repIDlist default="#cookie.userID#"><!--this will be the list
of ID's-->
<cfparam name=repnamelist default="#cookie.SMname#"><!--this will be the
list of names-->
<cfparam name=thiscount default=1>
<cfquery name=getreports datasource=#dsn#><!--get the first level down-->
select userID, username from roles where reportstouserID = #cookie.userID#
</cfquery>
<cfset thiscount=getreports.recordcount>
<cfset newlist=valuelist(getreports.userID)>
<cfset repnamelist=listappend(repnamelist,valuelist(getreports.username))>
<cfset repIDlist=listappend(repIDlist,newlist)>
<cfloop condition="thiscount gt 0"><!--as long as records are found-->
<cfquery name=getreports datasource=#dsn#>
select username, userID from roles
where cast(reportstouserID as varchar) IN (#newlist#)
</cfquery>
<cfset thiscount=getreports.recordcount>
<cfset newlist=valuelist(getreports.userID)>
<cfif newlist is not "">
<cfset
repnamelist=listappend(repnamelist,valuelist(getreports.username))>
<cfset repIDlist=listappend(repIDlist,newlist)>
</cfif>
</cfloop>
Then, in outputs I use (for example a selectbox):
<cfset count=0>
<cfloop list="#repIDlist#" index="ii"><cfset count=count+1>
<option value=#ii#>#listgetat(repnamelist,count)#
</cfloop>
Hope this is of some help.
D
*************
Diana Nichols
Webmistress
http://www.lavenderthreads.com
770.434.7374
"One man's magic is another man's engineering." ---Lazarus Long
-----Original Message-----
From: Tony Hicks [mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 17, 2001 7:52 AM
To: SQL
Subject: Help
I have passed this question through so many cf communities... and it has
still left me
<cf_dumbfounded>
Suppose you have a db with a table called categories..
The fields are:
ID: Each Category's Unique ID
Parent_ID: This field is the ID of the Parent Category
Category_Name: The text to display to represent the category...
So when you click a link like this: <a href="browse.cfm?parent_id=5>Test
Category</a> (The unprocessed CF for that is <a
href="browse.cfm?parent_id=#id#>#Category_Name#</a> with a query that tells
it to select just from the parent_id..)...
Well that all works fine but I want to display the Level history at the top
like so:
Companies >> Brick and Mortar >> United States >> Arkansas >> Sevier County
And if they were on the page listing all the counties in Arkansas it would
be:
Companies >> Brick and Mortar >> United States >> Arkansas
Hopefully you all get the idea... does anyone know what the proper cf code
would be to display this? Allaire Forums have helped me the best they can
but the code people give me either errors out or loops into itself, thus
locking up the server...
Thanks,
Tony Hicks
I'd be glad to provide as much information as needed.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
