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. ======================================================
