• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Hi Guys, a Recusion is Cutting me Off

Explorer ,
Jan 31, 2009 Jan 31, 2009

Copy link to clipboard

Copied

I have a PHP code which uses recursion to get categories and subcategories.

I am trying to convert it with coldfusion but getting into trouble:

here is the code:

$result = mysql_query("select title,parentid from cat where cid=$catid");
list($title,$parentid)=mysql_fetch_row($result);
$title=getparentlink($parentid,$title);
TOPICS
Advanced techniques

Views

2.3K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 31, 2009 Jan 31, 2009

Copy link to clipboard

Copied

The first line runs a query.
The second converts the query to a list.
The third one runs a function that appears to run a query for the parentid.

If this is recursion, this code should be in a loop.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 31, 2009 Jan 31, 2009

Copy link to clipboard

Copied

> I have a PHP code which uses recursion to get categories and subcategories.
>
> I am trying to convert it with coldfusion but getting into trouble:
>

Could you possibly post the code you're having trouble with, and explain
what the trouble actually is?

--
Adam

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Feb 01, 2009 Feb 01, 2009

Copy link to clipboard

Copied

Hi Guys, Here is the Code, I am trying to Do:

i get query results as: select * from cat where parentid = 0

now i use this query as:

<cfoutput query="results">
<cfquery datasource="#request.dsn#" name="a">
select catID, category from
categories
where
parentid = <cfqueryparam cfsqltype="cf_sql_numeric" value="#Trim(results.catID)#">
</cfquery>
<td height="30" valign="middle"><strong><a href="index.cfm?catID=#URLEncodedFormat(catID)#">#category#</a></strong><br>
<cfloop query="a">
<cfquery datasource="#request.dsn#" name="showanother">
select * from table
where catID=<cfqueryparam cfsqltype="cf_sql_numeric" value="#Trim(a.catID)#">
</cfquery>
<a href="index.cfm?catID=#URLEncodedFormat(catID)#">#Category#</a>
</cfloop>
</cfoutptut>

Bur it is appearing like:

Home: CategoryName

and if i select another category it is appearing as:

Home: SubCategoryName

But I want Something like this:

Home: CategoryName: SubCategoryName

Hope u understand what i am trying to do here:

Regards

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Feb 01, 2009 Feb 01, 2009

Copy link to clipboard

Copied

OK the problem here is that you're not actually doing anything recursively,
you're just looping over a query.

What you need to do is to write a function that takes an ID which queries
for either the descendants or ancestors depending on what you're trying to
do; and for each of those, call the same function again with that record's
ID as the ID you pass in. That will have you traversing into or out of the
hierarchy. What you do as you traverse is up to you: looks like you need to
build up a recordset of IDs & category labels, which ultimately gets passed
back to the calling code.

Two things:
* if you're doing this, you need to read up on how recursion works, because
you don't seem to be getting it. There's plenty of stuff around that
Google will dig up for you.
* the ID / parent-ID (adjacency model) method of doing hierarchies is a
pretty poor performer, specifically because one needs to do all this
recursion. I recommend you look up "nested set", and re-implement your
hierarchy using that model, if possible.

--
Adam

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Feb 02, 2009 Feb 02, 2009

Copy link to clipboard

Copied

Thanks, Pointing me out that i am not doing anything recursive. well I used queries inside a query to get count the records of categories and subcategories undeneath it..

but how i display it like a breadcrumb on main link.

Please show me a point Guys.

Cheers

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Feb 03, 2009 Feb 03, 2009

Copy link to clipboard

Copied

> Thanks, Pointing me out that i am not doing anything recursive. well I used
> queries inside a query to get count the records of categories and subcategories
> undeneath it..
>
> but how i display it like a breadcrumb on main link.
>
> Please show me a point Guys.

Well I kinda wish you'd followed my pointer to "read up on recursion", and
try working it out for yourself. That's the best way to learn.

You could at leats have used Google to search for "breadcrumbs recursive
coldfusion" (which yields some results). Or *something*.


However I find myself in an uncharacteristically helpful (if not polite.
But then I'm rarely polite) mood tonight, so here's some sample code.

<cfscript>
// this is in lieu of a DB table
qData = queryNew("catId,parentId,label");
queryAddRow(qData); qData.catId[1] = 1; qData.parentId[1] = "";
qData.label[1] = "My Site";
queryAddRow(qData); qData.catId[2] = 2; qData.parentId[2] = 1;
qData.label[2] = "News";
queryAddRow(qData); qData.catId[3] = 3; qData.parentId[3] = 1;
qData.label[3] = "Products";
queryAddRow(qData); qData.catId[4] = 4; qData.parentId[4] = 1;
qData.label[4] = "Support";

queryAddRow(qData); qData.catId[5] = 5; qData.parentId[5] = 3;
qData.label[5] = "Software";
queryAddRow(qData); qData.catId[6] = 6; qData.parentId[6] = 3;
qData.label[6] = "Hardware";

queryAddRow(qData); qData.catId[7] = 7; qData.parentId[7] = 6;
qData.label[7] = "PCs";
queryAddRow(qData); qData.catId[8] = 8; qData.parentId[8] = 6;
qData.label[8] = "Macs";
</cfscript>


<!--- say we're on the Macs page --->
<cfset pageId = 8>

<cfset lBreadcrumbs = getBreadcrumbs(id=pageId)>
<cfoutput>#listChangeDelims(lBreadcrumbs, " &rsaquo; ")#</cfoutput>


<cffunction name="getBreadcrumbs" returntype="string" output="false">
<cfargument name="id" type="numeric" required="true">

<cfset var qThis = "">
<cfset var lReturn = "">
<!--- get the record for this page --->
<cfquery name="qThis" dbtype="query" maxrows="1">
select parentId, label
from qData
where catId = <cfqueryparam value="#arguments.id#"
cfsqltype="CF_SQL_INTEGER">
</cfquery>
<cfif len(qThis.parentId[1])><!--- if it's got a parent, go grab it --->
<cfset lReturn = getBreadcrumbs(id=qThis.parentId[1])><!--- THIS IS THE
RECURSIVE BIT --->
</cfif>
<!--- once we've grabbed the parent, append this node's label, and we will
have the labels for the parent, followed by itself. Return that. --->
<cfreturn listAppend(lReturn, qThis.label[1])>
</cffunction>

It was a rush job, but it shows how to use recursion to extract breadcrumbs
from an adjacency list. And I was rather pleased it worked on first pass.
Not even any typos!

Make an effort to understand it, and report back with any questions. By
far the easiest way of understanding recursive stuff like this is to use
pencil and paper to keep track of the values being passed in and out of the
function, and watch what happens to them. Really: pencil and paper.

--
Adam

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Feb 04, 2009 Feb 04, 2009

Copy link to clipboard

Copied

Hi Thanks!!!

I tried my code to be bit easy. and remove some contents in now i now i am simply using this to make try the breadcrumsbut it is not working: i think i am missing something big here:

Here is query very simple instead.

select * from cat where parentid = 0


<cfoutput query="myResult">
<table cellpadding="5" border="0" width="100%">
<tr align="center">
<td valign="middle" align="left"><p align="left"> <strong><a href="index.cfm">Home</a></strong>  <strong><big>&raquo;</big> #Category# (#subcat.total#)</strong></p></td>
</tr>
<tr>
<td>All Users</td>
</tr>
</table>
</cfoutput>

well this is my subcat query :

SELECT count(ID) AS total, count(cat.catID) as totalcat
FROM table1 LEFT JOIN cat ON table1.catID = cat.catID
WHERE table1.catID = cat.catID
AND cat.parentid= <cfqueryparam cfsqltype="cf_sql_numeric" value="#trim(arguments.catID)#">
or cat.catID = <cfqueryparam cfsqltype="cf_sql_numeric" value="#trim(arguments.catID)#">


so it works simple way:

home: categoryName

and then i choose subcat:

home:subcatName

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Feb 04, 2009 Feb 04, 2009

Copy link to clipboard

Copied

> I tried my code to be bit easy. and remove some contents in now i now i am
> simply using this to make try the breadcrumsbut it is not working: i think i am
> missing something big here:

Yes you are.

Your code will always ever only display "home > PARENT-OF-CURRENT-NODE >
CURRENT-NODE", because you're only ever doing one lookup: getting the
parent of the node. To get all the generations from the child up the tree
back to the top (eg: Home > GREAT-GRANDPARENT > GRANPARENT > PARENT >
NODE), you NEED to do the recursion, so that you look up each preceding
generation. The code I gave you is about as simple as you can make it,
with it fulfilling the complete requirement.

--
Adam

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Feb 08, 2009 Feb 08, 2009

Copy link to clipboard

Copied

Hi, I tried your way:

as i did some changes in my code: used asquery as:

<cffunction name="showcats" returntype="string" output="false">
<cfargument name="catid" type="numeric" required="true">
<cfargument name="recurse" type="any" required="no">
<cfset var qThis = "">
<cfset var lReturn = "">
<cfquery name="qThis" datasource="#request.dsn#">
select * from categories
where catId = <cfqueryparam value="#arguments.catid#" cfsqltype="CF_SQL_INTEGER">
</cfquery>
<cfif len(qThis.parentId[1])>
<cfset lReturn = showcats(catid=qThis.parentId[1])>
</cfif>
<cfreturn listAppend(lReturn, qThis.category[1])>
</cffunction>

then on my page is use something like thsi:

<cfinvoke component="core.cfc.show" method="showcats" recurse="1" catID="#url.catID#" returnvariable="myResult"/>
<cfoutput query="myResult">
<table cellpadding="5" border="0" width="100%">
<tr align="center">
<td valign="middle" align="left"><p align="left">
<strong><a href="index.cfm">Home</a></strong> 
<cfset lBreadcrumbs = myResult(catid=pageId)>
<cfoutput>#listChangeDelims(lBreadcrumbs, " &rsaquo; ")#</cfoutput> </p>
<!---<strong><big>&raquo;</big> #Category# (#subcat.total#)</strong></p>--->
</td>
</tr>
</table></cfoutput>
but it is not displaying:

it is providing me an error of:

Complex object types cannot be converted to simple values.
The expression has requested a variable or an intermediate expression result as a simple value, however, the result cannot be converted to a simple value. Simple values are strings, numbers, boolean values, and date/time values. Queries, arrays, and COM objects are examples of complex values.

The most likely cause of the error is that you are trying to use a complex value as a simple one. For example, you might be trying to use a query variable in a cfif tag.

The error occurred in C:\Inetpub\wwwroot\projects\core\cfc\show.cfc: line 46
Called from C:\Inetpub\wwwroot\projects\showall.cfm: line 5
Called from C:\Inetpub\wwwroot\projects\index.cfm: line 128
Called from C:\Inetpub\wwwroot\projects\index.cfm: line 1
44 : <cfset lReturn = showcats(catid=myResult.parentId[1])>
45 : </cfif>
46 : <cfreturn listAppend(lReturn, myResult.category[1])>
47 : <cfelse>
48 : <cfreturn myResult>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Feb 08, 2009 Feb 08, 2009

Copy link to clipboard

Copied

> <cffunction name="showcats" returntype="string" output="false">

[...]
> <cfinvoke component="core.cfc.show" method="showcats" recurse="1"
> catID="#url.catID#" returnvariable="myResult"/>
> <cfoutput query="myResult">

showcats() returns a string. You're trying to treat it as a query.


> <cfset lBreadcrumbs = myResult(catid=pageId)>

And here you're treating it like it's a function!

I think you need to pay slightly closer attention to what you're doing.

--
Adam

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Feb 08, 2009 Feb 08, 2009

Copy link to clipboard

Copied

Cool! Thanks I go it working..

Only Thing occuring is when it creates a node like:

home > category > subcategory > subsubcategory

how i can link the previous parentID like:

home > category > subcategory > subsubcategory

i mean wanna link the category, sub category so when i click on the category and subcategory i should be able to go back..

is some way to get this working

Regards

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Feb 09, 2009 Feb 09, 2009

Copy link to clipboard

Copied

> how i can link the previous parentID like:
>
> home > category > subcategory > subsubcategory
>
> i mean wanna link the category, sub category so when i click on the category
> and subcategory i should be able to go back..
>
> is some way to get this working


Yes, there is. I think the time has come for you to try to get it sorted
YOURSELF, without referring back here.

Try to come up with a solution, and if you have problems refer back here.
Don't ask us to do your work for you in the first instance.

--
Adam

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Feb 09, 2009 Feb 09, 2009

Copy link to clipboard

Copied

Definattely

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Aug 31, 2009 Aug 31, 2009

Copy link to clipboard

Copied

Hi, I know this post is a little old, but I am having some trouble making the code work, and I would appreciate any advice! Right now, I am using the following function to create the list of breadcrumbs:

<!--- generate breadcrumbs --->
    <cffunction name="getBreadcrumbs" returntype="string" access="public" output="false">
        <cfargument name="pageID" required="yes" type="numeric">
       
        <cfset var vBreadcrumbs = '' />
       
        <cfquery name="qGetPageInfo" datasource="#application.db#" maxrows="1">
            SELECT
                *
            FROM
                admin_navigation
            WHERE
                id = <cfqueryparam value="#arguments.pageID#" cfsqltype="cf_sql_integer">
        </cfquery>
       
        <!--- 0 is a top level item --->

        <cfif qGetPageInfo.parentID NEQ 0>
            <cfset vBreadcrumbs = getBreadcrumbs(qGetPageInfo.parentID) />
        </cfif>

        <cfset vBreadcrumbs = listAppend(vBreadcrumbs, qGetPageInfo.pageName) />
       
        <cfreturn vBreadcrumbs />
    </cffunction>

This is being used to display the breadcrumb function:

<cfset getBreadcrumbsQuery = application.adminObject.getBreadcrumbs(pageID) />
#listChangeDelims(getBreadcrumbsQuery, " &raquo; ")#

However, what I get is a list with the correct number of levels, but the page name that is displayed is always the top-level breadcrumb that should be called. For example, I should see Courses >> Core Course Management for the page I am testing on, and instead I just get Courses >> Courses.

I would appreciate any pointers in the right direction! Thanks.

KC

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Aug 31, 2009 Aug 31, 2009

Copy link to clipboard

Copied

You shouldn't really hijack someone else's thread (even a long dead one) with your own question.  Etiquette is generally "one question per thread".

First things first, you need to VAR all your variables in your function.  This is especially critical when recursing, because otherwise each new call will overwrite the variables from the previous call.

Get that sorted out first.

Second - and this is unrelated to your immediate issue, but it's just poor form - never use SELECT * unless you actually want all columns in the result set.  Even then, it's still better to list all the columns.  For your purposes here, you only need a couple of columns, so only fetch those columns.

Third - the adjacency list model  (ID / PARENT_ID) for representing a hierarchy is not very performant, and does not scale.  I recommend using the nested set model instead.

Ooh... fourth... what DB system are you using?  Depending on your answer you can probably dispense with the recursion and get the DB to get the results with a single query.

--

Adam

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Sep 01, 2009 Sep 01, 2009

Copy link to clipboard

Copied

LATEST

Sorry about that, I have reposted here: http://forums.adobe.com/thread/485755

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Feb 01, 2009 Feb 01, 2009

Copy link to clipboard

Copied

You are running way too many queries. You only need one as far as I can see.

select yourfields
from cat c join category ca on c.catid = ca.catid
join table t on ca.catid = t.catid
order by categoryname, subcategoryname

Then use the group attibute of cfoutput to organize your display.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Feb 02, 2009 Feb 02, 2009

Copy link to clipboard

Copied

A single query with query of queries to get the counts would be much more efficient than using queries inside a query.

As far as the breadcrumb goes, what have you accomplished so far, and what is it you want to display?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Feb 02, 2009 Feb 02, 2009

Copy link to clipboard

Copied

I have worked such as:

Home: CategoryName

and if i select subcategory it replace the above as:

Home: Subcatname

but i want it to appear it as:

Home: categoryName: subcatName

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Feb 03, 2009 Feb 03, 2009

Copy link to clipboard

Copied

What is the code that generates those displays?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation