The recursive function looks like it may have too much logic. Try the
following:
<cfscript>
function showtree(thiskey,level){
writeoutput(repeatstring("-",level) & thiskey & "<br>");
thisquery = cfquery("select * from pResults where supervisor_id
= '" & thiskey & "'");
if(thisquery.recordcount gt 0){//this individual supervises
someone...
for (i=1; i lte thisquery.recordcount; i=i+1){//loop
through thiskey's subordinates...
showtree(thisquery.personid[i],level+1);
}
}
return true;
}//end of function
</cfscript>
The problem may also be that the "thisquery" variable is global and not
local. If this is the case, the query is not retaining the original
values.
You may try adding "var" in front of "thisquery": var thisquery =
cfquery("select * from pResults where supervisor_id = '" & thiskey &
"'");
This should create a local variable to the function.
Randy Ellis
Senior Programmer Analyst
City of Overland Park
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Bruce Dunwiddie
Sent: Tuesday, July 13, 2004 5:56 PM
To: [EMAIL PROTECTED]
Subject: RE: [KCFusion] recursive function help needed
I can't see anything obviously wrong with your logic, other than needing
to arrest you for defenseless database abuse, but I do have an idea for
you to track down the problem. You're currently assuming that you don't
need an
else statement for the end of the block " if(thisquery.recordcount
gt
0){//this individual supervises someone...", which that if statement
shouldn't even be needed, but I would suggest you put in an else
statement on that block that says writeout( "we shouldn't ever get
here.<br>" ) and see if that's where your recursion loop is ending up.
Next, I would a line directly under the line "for (i=1; i lte
thisquery.recordcount; i=i+1){//loop through thiskey's subordinates..."
that says writeout( "i:" + i + " recordcount:" + thisquery.recordcount +
"<br>" ) since those are your looping dependencies, and you should
notice something obviously wrong. The correct output should be like i:1
recordcount:3 i:2 recordcount:3 i:3 recordcount:3 i:1 recordcount:3 i:2
recordcount:3 i:3 recordcount:3 i:1 recordcount:1
based on your test data. The next thing I would suggest is running the
code in debug and just stepping through it.
Bruce Dunwiddie
Ticket Technology
P: 866.543.3331
F: 913.451.1786
[EMAIL PROTECTED]
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Behalf Of doug boude
Sent: Tuesday, July 13, 2004 4:30 PM
To: [EMAIL PROTECTED]
Subject: [KCFusion] recursive function help needed
I've spent a couple of days on this and am at a dead end, so I appeal to
the wise sages of the CF council for advice and enlightenment.
Objective: take a hierarchical query and output it in a tree format.
Incoming query structure:
ID Parent_ID
hdb4239 dre8714
pkj4567 dre8714
kxj4231 dre8714
lat9876 hdb4239
sat7787 lat9876
At this point, I want to do absolutely nothing fancy, and am trying just
to get it to work in its bare-bones form, which is to simply write text
to the page in the form of:
top level person
-second level person1
--third level person1
--third level person2
-second level person2
etc.
My approach:
1. Create an initial query containing the set of hierarchical data i
want to output (in this case, I'm faking it)
<cfscript>
//making a fake query to turn into a tree
pResults = querynew("personid,supervisor_id");
tmp = queryaddrow(pResults,7);
tmp = querysetcell(pResults,"personid","hdb4239",1);
tmp = querysetcell(pResults,"supervisor_id","dre8714",1);
tmp = querysetcell(pResults,"personid","jpk7656",2);
tmp = querysetcell(pResults,"supervisor_id","dre8714",2);
tmp = querysetcell(pResults,"personid","fxj6767",3);
tmp = querysetcell(pResults,"supervisor_id","dre8714",3);
tmp = querysetcell(pResults,"personid","adj2345",4);
tmp = querysetcell(pResults,"supervisor_id","hdb4239",4);
tmp = querysetcell(pResults,"personid","sgv1234",5);
tmp = querysetcell(pResults,"supervisor_id","hdb4239",5);
tmp = querysetcell(pResults,"personid","lat3456",6);
tmp = querysetcell(pResults,"supervisor_id","hdb4239",6);
tmp = querysetcell(pResults,"personid","ren9876",7);
tmp = querysetcell(pResults,"supervisor_id","lat3456",7);
</cfscript>
2. "functionalize" the cfquery functionality to allow me to use cfquery
from within a cfscript function
<cffunction name = "cfquery" returnType = "query" access = "private"
output = "yes" displayName = "cfquery"> <cfargument name="SQLString"
type="string" required="Yes">
<cfquery dbtype="query"
name="theseresults">#preservesinglequotes(SQLString)#</cfquery>
<cfreturn theseresults>
</cffunction>
3. Create my treemaker function
<cfscript>
function showtree(thiskey,level){
writeoutput(repeatstring("-",level) & thiskey & "<br>");
thislevel = level+1;
thisquery = cfquery("select * from pResults where supervisor_id
= '" & thiskey & "'");
if(thisquery.recordcount gt 0){//this individual supervises
someone...
for (i=1; i lte thisquery.recordcount; i=i+1){//loop
through thiskey's subordinates...
haskids = cfquery("select * from pResults where
supervisor_id = '" & thisquery.personid[i] & "'");
if(haskids.recordcount gt 0){//the subordinate
of thiskey has subordinates themselves, so feed their id to the showtree
function...
showtree(thisquery.personid[i],thislevel);
}
else{//this subordinate of thiskey had no
subordinates themselves...just output their value
writeoutput(repeatstring("-",thislevel)
& thisquery.personid[i] & "<br>");
}
}
}
return true;
}//end of function
</cfscript>
4. Call the function, feeding it a starting person id
<cfset tmptree = showtree("dre8714",0)>
Okay, that's it. Now, here's what it's spitting out for me:
dre8714
-hdb4239
--adj2345
--sgv1234
--lat3456
---ren9876
These results are correct; However, even though it successfully drilled
down to the innermost level and output with proper indentation, it
refuses to drill back up again and pick up where it left off in the
function call immediately above the most inner one (next line after the
last one printed should have been "-jpk7656"). I suspect it has
something to do with the correct usage of a "return", but I've put
returns all over the place and not gotten any better output than what is
copied above.
I know this is such a simple thing and that one of my peers out there
will spot right away what the issue is. Thanks for taking a few minutes
to lend to my challenge.
Doug :0)
_________________________________________________________________
FREE pop-up blocking with the new MSN Toolbar - get it now!
http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/
=========================================================
Kansas City ColdFusion User Group's website & listserv is hosted through
the generous support of Clickdoug.com To send email to the list, email
[EMAIL PROTECTED] To (un)subscribe, email [EMAIL PROTECTED] with
your request. For hosting solutions http://www.clickdoug.com Featuring
Win2003 Enterprise, RedHat Linux, CFMX 6.1.
======================================================
=========================================================
Kansas City ColdFusion User Group's website & listserv is
hosted through the generous support of Clickdoug.com
To send email to the list, email [EMAIL PROTECTED]
To (un)subscribe, email [EMAIL PROTECTED] with your request. For hosting
solutions http://www.clickdoug.com Featuring Win2003 Enterprise, RedHat
Linux, CFMX 6.1.
======================================================
========================================================Kansas City ColdFusion User
Group's website & listserv is
hosted through the generous support of Clickdoug.com
To send email to the list, email [EMAIL PROTECTED]
To (un)subscribe, email [EMAIL PROTECTED] with your request.
For hosting solutions http://www.clickdoug.com
Featuring Win2003 Enterprise, RedHat Linux, CFMX 6.1.
=====================================================
