Doug :0)
From: "Bruce Dunwiddie" <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: <[EMAIL PROTECTED]> Subject: RE: [KCFusion] recursive function help needed Date: Tue, 13 Jul 2004 17:56:13 -0500
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. ======================================================
_________________________________________________________________
Get tips for maintaining your PC, notebook accessories and reviews in Technology 101. http://special.msn.com/tech/technology101.armx
=========================================================
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. ======================================================
