Parents and its Child in select box using coldfusion

Ok! Experts, i tried finding out various ways how can i make parent/child to appear under select box:

like i have table with following fields:

itemID
parentID
categoryName

I searched  lot and found one tutorial how can i do it by rick osborne.

he had prepared one function but exactly i am not knwing how can i implement that fuctionin my select box. can some one please guide me.
Also with the function below, i am 30% unceratin what exactly it is doing. can anyone guide me.
will this function solve the problem of appearing the subcategory and category as:

apples
    organges
          blue
          green
          red
               dark red
               light red
mangoes
     yes
          why
          why not
    no
          why
          why not


it is below in the code box:

<cffunction name="ListFromQueryTree" returntype="string" output="No">
<cfargument name="Query" type="query" required="Yes">
<cfargument name="TitleColumn" type="string" required="No" default="Name">
<cfargument name="DepthColumn" type="string" required="No" default="TreeDepth">
<cfargument name="DepthPrefix" type="string" required="No" default="depth">
<cfargument name="ListTag" type="string" required="No" default="ul">
<cfset var Ret="">
<cfset var MinDepth=999>
<cfset var Q=Arguments.Query>
<cfset var LastDepth=0>
<cfset var ThisDepth=0>
<cfset var d=0>
<cfloop query="Q">
<cfset ThisDepth=Q[Arguments.DepthColumn][Q.CurrentRow]>
<cfif ThisDepth LT MinDepth>
<cfset MinDepth=ThisDepth>
</cfif>
</cfloop>
<cfset LastDepth=MinDepth-1>
          <cfloop query="Q">
           <cfset ThisDepth=Q[Arguments.DepthColumn][Q.CurrentRow]>
<cfif LastDepth LT ThisDepth>
<cfloop from="#IncrementValue(LastDepth)#" to="#ThisDepth#" index="d">
<cfset Ret=Ret & '<#Arguments.ListTag# class="#Arguments.DepthPrefix##d#">'>
</cfloop>
<cfelse>
<cfif LastDepth GT ThisDepth>
<cfset Ret=Ret & RepeatString("</li></ul>",LastDepth-ThisDepth)></cfif>
<cfset Ret=Ret & "</li>"></cfif><cfset Ret=Ret & "<li>" & HTMLEditFormat(Q[Arguments.TitleColumn][Q.CurrentRow])>
<cfset LastDepth=ThisDepth></cfloop>
<cfif Q.RecordCount GT 0>
<cfset Ret=Ret & RepeatString("</li></ul>",LastDepth-(MinDepth-1))></cfif>
<cfreturn Ret>
</cffunction>

Open in new window

LVL 16
Gurpreet Singh RandhawaWeb DeveloperAsked:
Who is Participating?
 
duncancummingConnect With a Mentor Commented:
One thing you need to be careful with regarding Rick Osborne's tutorial is that you can't just copy-and-paste the code quickly.  Instead you have to go through it step-by-step, making little changes as he introduces them.

Right now it just looks like you've copied the code from the top of part 7 of the tutorial, but that's not going to work.  Instead go down to where it says "First we need to start with a basic function signature:"  and start copying each block of code after that.  

You should end up with:



<cffunction name="QueryTreeSort" returntype="query" output="No">
	<cfargument name="Stuff" type="query" required="Yes">
	<cfargument name="ParentID" type="string" required="No" default="ParentID">
	<cfargument name="ItemID" type="string" required="No" default="ItemID">
	<cfargument name="BaseDepth" type="numeric" required="No" default="0">
	<cfargument name="DepthName" type="string" required="No" default="TreeDepth">
 
<cfset var RowFromID=StructNew()>
	<cfset var ChildrenFromID=StructNew()>
	<cfset var RootItems=ArrayNew(1)>
	<cfset var Depth=ArrayNew(1)>
	<cfset var ThisID=0>
	<cfset var ThisDepth=0>
	<cfset var RowID=0>
	<cfset var ChildrenIDs="">
	<cfset var ColName="">
	<cfset var Ret=QueryNew(ListAppend(Stuff.ColumnList,Arguments.DepthName))>
 
<!--- Set up all of our indexing --->
	<cfloop query="Stuff">
		<cfset RowFromID[Stuff[Arguments.ItemID][Stuff.CurrentRow]]=CurrentRow>
		<cfif NOT StructKeyExists(ChildrenFromID, Stuff[Arguments.ParentID][Stuff.CurrentRow])>
			<cfset ChildrenFromID[Stuff[Arguments.ParentID][Stuff.CurrentRow]]=ArrayNew(1)>
		</cfif>
		<cfset ArrayAppend(ChildrenFromID[Stuff[Arguments.ParentID][Stuff.CurrentRow]], Stuff[Arguments.ItemID][Stuff.CurrentRow])>
	</cfloop>
 
<!--- Find parents without rows --->
	<cfloop query="Stuff">
		<cfif NOT StructKeyExists(RowFromID, Stuff[Arguments.ParentID][Stuff.CurrentRow])>
			<cfset ArrayAppend(RootItems, Stuff[Arguments.ItemID][Stuff.CurrentRow])>
			<cfset ArrayAppend(Depth, Arguments.BaseDepth)>
		</cfif>
	</cfloop>
 
<!--- Do the deed --->
	<cfloop condition="ArrayLen(RootItems) GT 0">
		<cfset ThisID=RootItems[1]>
		<cfset ArrayDeleteAt(RootItems, 1)>
		<cfset ThisDepth=Depth[1]>
		<cfset ArrayDeleteAt(Depth, 1)>
 
<cfif StructKeyExists(RowFromID, ThisID)>
			<!--- Add this row to the query --->
			<cfset RowID=RowFromID[ThisID]>
			<cfset QueryAddRow(Ret)>
			<cfset QuerySetCell(Ret, Arguments.DepthName, ThisDepth)>
			<cfloop list="#Stuff.ColumnList#" index="ColName">
				<cfset QuerySetCell(Ret, ColName, Stuff[ColName][RowID])>
			</cfloop>
		</cfif>
 
<cfif StructKeyExists(ChildrenFromID, ThisID)>
			<!--- Push children into the stack --->
			<cfset ChildrenIDs=ChildrenFromID[ThisID]>
			<cfloop from="#ArrayLen(ChildrenIDs)#" to="1" step="-1" index="i">
				<cfset ArrayPrepend(RootItems, ChildrenIDs[i])>
				<cfset ArrayPrepend(Depth, ThisDepth + 1)>
			</cfloop>
		</cfif>
	</cfloop>
	<cfreturn Ret>
</cffunction>

Open in new window

0
 
duncancummingCommented:
So, you want one select box, with all the nested categories and subcategories, like
<select>
<option>Fruit
<option>-Apples
<option>--Red
<option>---Royal Gala
<option>---Macintosh
<option>--Green
<option>---Granny Smith
<option>-Oranges
<option>--Blood orange
<option>--Florida
<option>--Seville

etc?

I've used Rick Osborne's function before, but don't think I ever tried putting it all into a select box.  Basically that function is creating nested lists.  You aren't nesting anything, although I guess you want some way of indicating what the nested elements are (e.g. how I've used hyphens in my example above).  

I think basically take out all the </li> stuff, at least initially, to simplify it, then see what you need to add back in.

Remind me again what does the RepeatString function do?

Here's a go at rewriting the function.

<cffunction name="ListFromQueryTree" returntype="string" output="No">
	<cfargument name="Query" type="query" required="Yes">
	<cfargument name="TitleColumn" type="string" required="No" default="Name">
	<cfargument name="DepthColumn" type="string" required="No" default="TreeDepth">
	<cfargument name="DepthPrefix" type="string" required="No" default="depth">
	<cfargument name="ListTag" type="string" required="No" default="ul">
	
	<cfset var Ret="">
	<cfset var MinDepth=999>
	<cfset var Q=Arguments.Query>
	<cfset var LastDepth=0>
	<cfset var ThisDepth=0>
	<cfset var d=0>
	
	<cfloop query="Q">
		<cfset ThisDepth=Q[Arguments.DepthColumn][Q.CurrentRow]>
		<cfif ThisDepth LT MinDepth>
			<cfset MinDepth=ThisDepth>
		</cfif>
	</cfloop>
	
	<cfset LastDepth=MinDepth-1>
	
	<cfset Ret = Ret & "<select name='yourSelect'>">
	
	<cfloop query="Q">
		<cfset ThisDepth=Q[Arguments.DepthColumn][Q.CurrentRow]>
		
		<!--- create the initial Option tag --->
		<cfset Ret="<option value='#Q[yourColumn][Q.CurrentRow]#'>">
		
		<cfif LastDepth LT ThisDepth>
		<!--- pad with hyphens or whatever to indicate depth --->
			<cfloop from="#IncrementValue(LastDepth)#" to="#ThisDepth#" index="d">
				<cfset Ret=Ret & "-">
			</cfloop>
		</cfif>
		
		<cfset Ret=Ret & HTMLEditFormat(Q[Arguments.TitleColumn][Q.CurrentRow]) & "</option>">
 
		<cfset LastDepth=ThisDepth>
	</cfloop>
	
	<cfset Ret = Ret & "</select>">
	
	<cfreturn Ret>
</cffunction>

Open in new window

0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
as far as i read it:

it is written something like this for repeat String:

This allows us to do tricks like this back up in the display portion of our loop:
<cfoutput>#RepeatString(,ThisDepth)##Stuff.Name[RowID]#<br /></cfoutput>

You can add in non-breaking spaces, spacer images, empty table cells, or whatever you want instead of the  part to make your display come out right.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
duncancummingCommented:
In that case, I think your function can be made even simpler.

<cffunction name="ListFromQueryTree" returntype="string" output="No">
	<cfargument name="Query" type="query" required="Yes">
	<cfargument name="TitleColumn" type="string" required="No" default="Name">
	<cfargument name="DepthColumn" type="string" required="No" default="TreeDepth">
	
	<cfset var Ret="">
	<cfset var Q=Arguments.Query>
	<cfset var ThisDepth=0>
	
	<cfset Ret = Ret & "<select name='yourSelect'>">
	
	<cfloop query="Q">
		<cfset ThisDepth=Q[Arguments.DepthColumn][Q.CurrentRow]>
		
		<!--- create the initial Option tag --->
		<cfset Ret="<option value='#Q[yourColumn][Q.CurrentRow]#'>">
		
		<!--- pad with hyphens or whatever to indicate depth --->
		<cfset Ret=Ret & RepeatString("-",ThisDepth)>
		
		<cfset Ret=Ret & HTMLEditFormat(Q[Arguments.TitleColumn][Q.CurrentRow]) & "</option>">
	</cfloop>
	
	<cfset Ret = Ret & "</select>">
	
	<cfreturn Ret>
</cffunction>

Open in new window

0
 
duncancummingCommented:
Slight mistake in the above.  Try this instead.


<cffunction name="ListFromQueryTree" returntype="string" output="No">
	<cfargument name="Query" type="query" required="Yes">
	<cfargument name="TitleColumn" type="string" required="No" default="Name">
	<cfargument name="DepthColumn" type="string" required="No" default="TreeDepth">
	
	<cfset var Ret="">
	<cfset var Q=Arguments.Query>
	<cfset var ThisDepth=0>
	
	<cfset Ret = "<select name='yourSelect'>">
	
	<cfloop query="Q">
		<cfset ThisDepth=Q[Arguments.DepthColumn][Q.CurrentRow]>
		
		<!--- create the initial Option tag --->
		<cfset Ret = Ret & "<option value='#Q[yourColumn][Q.CurrentRow]#'>">
		
		<!--- pad with hyphens or whatever to indicate depth --->
		<cfset Ret=Ret & RepeatString("-",ThisDepth)>
		
		<cfset Ret=Ret & HTMLEditFormat(Q[Arguments.TitleColumn][Q.CurrentRow]) & "</option>">
	</cfloop>
	
	<cfset Ret = Ret & "</select>">
	
	<cfreturn Ret>
</cffunction>

Open in new window

0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
Thanks You, How do i call this in select box.
0
 
duncancummingCommented:
Your page might look something like this:

<cfquery name="getCategories">
SELECT itemID, parentID, categoryName, depth
FROM yourTable
</cfquery>

<form name="yourForm" method="POST" action="...">

<cfoutput>
#ListFromQueryTree(
Query=getCategories,
TitleColumn="categoryName",
DepthColumn="depth",
IDColumn="itemID")#
</cfoutput>

</form>


Add in a fourth cfargument to your function:
<cfargument name="IDColumn" type="string" required="No" default="ID">

change this line:
 <cfset Ret = Ret & "<option value='#Q[yourColumn][Q.CurrentRow]#'>">
to:
 <cfset Ret = Ret & "<option value='#Q[arguments.IDColumn][Q.CurrentRow]#'>">
(assuming you want to pass the item ID in your select options).
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
Thanks U but one more thing:

i have following fields in my table:

cid
parentid
title
desc
hits

should i add some extra column like depth in my tabel and if so what will be the default value of it.

Also should i declare this routine of #ListFromQueryTree in a CFC and invoke it. the n i can use the query result and pass to the cfinvoke to get details
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
well i did not defined the depth field, still it worked as i put the parentid in the depth like:

<cfoutput>
#ListFromQueryTree(
Query=getCategories,
TitleColumn="categoryName",
DepthColumn="parentid",
IDColumn="itemID")#
</cfoutput>

Although it did worked but it did not displayed the subcategory under its parent. it displayed the categories first then it started the sub childrens.

Please guide me what i have to play depth field with. i really need to know this
0
 
duncancummingCommented:
My mistake, you don't have to put a depth column in your table.  All you need to do is pass the original query to Rick Osborne's QueryTreeSort function, which returns a new query object that includes the Depth column.

http://rickosborne.org/blog/index.php/2006/01/17/writing-a-parent-child-query-sorter-in-cf-part-7/

<cfquery name="getCategories">
SELECT itemID, parentID, categoryName
FROM yourTable
ORDER BY categoryName
</cfquery>

<cfset NewQuery=QueryTreeSort(getCategories)>

#ListFromQueryTree(
Query=NewQuery,
TitleColumn="categoryName",
IDColumn="itemID")#

Yes,a CFC would make sense, for all related functions (i.e. at least ListFromQueryTree and QueryTreeSort).
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
Mate i am really getting double minded here:
  • one function is ListFromQueryTree which we have defined earlier.
  • I read the link.
  • it created a UDF with cfquery inside it
  • why QueryTreeSort
  • Both functions should be in CFC


  • Please tell me
0
 
duncancummingCommented:
I've got to say, Rick Osborne's tutorial is one of the more complicated bits of ColdFusion I've had to deal with.  If your understanding is less than 100% you should go back and re-read the whole thing a couple of times.  

QueryTreeSort is the magic function that takes your query and returns a slightly different version that includes the heirarchy information you'll need (e.g. the Depth column, and in later parts of the tutorial, the TreeLineage and Children columns).  Without that function nothing works.

ListFromQueryTree is just a function that creates something to display.  It takes the query from QueryTreeSort as it's main parameter, and returns some HTML to output.  Originally a set of nested lists, but for you a single <select> dropdown.

Both functions don't have to be in a CFC, but it might make sense to do so.  That's really up to you and your personal preference.  
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
Ok now what i did, gone through this page again and again and tried something like this:

But what is get is a huge list of categories and subcategories not in a select box but simple listed with - sign against sub childrens.

beneath, it just states the error:


variable QueryTreeSort is undefined


<cffunction name="ListFromQueryTree" returntype="string" output="No">
        <cfargument name="Query" type="query" required="Yes">
        <cfargument name="TitleColumn" type="string" required="No" default="Name">
        <cfargument name="DepthColumn" type="string" required="No" default="TreeDepth">
        <cfargument name="IDColumn" type="string" required="No" default="ID">
        <cfset var Ret="">
        <cfset var Q=Arguments.Query>
        <cfset var ThisDepth=0>
        <cfset Ret = "<select name='category'>">
        <cfloop query="Q">
                <cfset ThisDepth=Q[Arguments.DepthColumn][Q.CurrentRow]>
                <!--- create the initial Option tag --->
                 <cfset Ret = Ret & "<option value='#Q[arguments.IDColumn][Q.CurrentRow]#'>">
                <!--- pad with hyphens or whatever to indicate depth --->
                <cfset Ret=Ret & RepeatString("",ThisDepth)>
                <cfset Ret=Ret & HTMLEditFormat(Q[Arguments.TitleColumn][Q.CurrentRow]) & "</option>">
        </cfloop>
        <cfset Ret = Ret & "</select>">
        <cfreturn Ret>
</cffunction>
<cfinclude template="../QueryTreeSort.cfm">
<cfset NewQuery=QueryTreeSort(getCats)>
 
<cfoutput>#ListFromQueryTree(Query=NewQuery,TitleColumn="title",IDColumn="cid")#</cfoutput>
 
QueryTreeSort.cfm contents:
 
<cfset BaseDepth=0>
<cfquery datasource="#Request.dsn#" name="Stuff">
SELECT * 
FROM grcat
ORDER BY title
</cfquery>
<cfset RowFromID=StructNew()>
<cfset ChildrenFromID=StructNew()>
<cfloop query="Stuff">
  <cfset RowFromID[cID]=CurrentRow>
  <cfif NOT StructKeyExists(ChildrenFromID, ParentID)>
    <cfset ChildrenFromID[ParentID]=ArrayNew(1)>
  </cfif>
  <cfset ArrayAppend(ChildrenFromID[ParentID], cID)>
</cfloop>
<cfset RootItems=ArrayNew(1)>
<cfset Depth=ArrayNew(1)>
<cfloop query="Stuff">
  <cfif NOT StructKeyExists(RowFromID, ParentID)>
    <cfset ArrayAppend(RootItems, cID)>
    <cfset ArrayAppend(Depth, BaseDepth)>
  </cfif>
</cfloop>
 
<cfloop condition="ArrayLen(RootItems) GT 0">
  <cfset ThisID=RootItems[1]>
  <cfset ArrayDeleteAt(RootItems, 1)>
  <cfset ThisDepth=Depth[1]>
  <cfset ArrayDeleteAt(Depth, 1)>
  <cfif StructKeyExists(RowFromID, ThisID)>
    <cfset RowID=RowFromID[ThisID]>
    <cfoutput>#RepeatString("",ThisDepth)##Stuff.title[RowID]#<br />
    </cfoutput>
  </cfif>
  <cfif StructKeyExists(ChildrenFromID, ThisID)>
    <cfset ChildrenIDs=ChildrenFromID[ThisID]>
    <cfloop from="#ArrayLen(ChildrenIDs)#" to="1" step="-1" index="i">
      <cfset ArrayPrepend(RootItems, ChildrenIDs[i])>
      <cfset ArrayPrepend(Depth, ThisDepth + 1)>
    </cfloop>
  </cfif>
</cfloop>

Open in new window

0
 
duncancummingCommented:
QueryTreeSort needs to be in a <cffunction>.
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
Dude this is running me crazy i am just getting mad, pulling my hairs: i wrapped it around cffunction but not worked

<cffunction name="QuerySortTree" returntype="any">
<cfset var Ret="">
<cfset BaseDepth=0>
<cfquery datasource="#Request.dsn#" name="Stuff">
SELECT * 
FROM grcat
ORDER BY title
</cfquery>
<cfset RowFromID=StructNew()>
<cfset ChildrenFromID=StructNew()>
<cfloop query="Stuff">
  <cfset RowFromID[cID]=CurrentRow>
  <cfif NOT StructKeyExists(ChildrenFromID, ParentID)>
    <cfset ChildrenFromID[ParentID]=ArrayNew(1)>
  </cfif>
  <cfset ArrayAppend(ChildrenFromID[ParentID], cID)>
</cfloop>
<cfset RootItems=ArrayNew(1)>
<cfset Depth=ArrayNew(1)>
<cfloop query="Stuff">
  <cfif NOT StructKeyExists(RowFromID, ParentID)>
    <cfset ArrayAppend(RootItems, cID)>
    <cfset ArrayAppend(Depth, BaseDepth)>
  </cfif>
</cfloop>
 
<cfloop condition="ArrayLen(RootItems) GT 0">
  <cfset ThisID=RootItems[1]>
  <cfset ArrayDeleteAt(RootItems, 1)>
  <cfset ThisDepth=Depth[1]>
  <cfset ArrayDeleteAt(Depth, 1)>
  <cfif StructKeyExists(RowFromID, ThisID)>
    <cfset RowID=RowFromID[ThisID]>
    <cfoutput>#RepeatString("",ThisDepth)##Stuff.title[RowID]#<br />
    </cfoutput>
  </cfif>
  <cfif StructKeyExists(ChildrenFromID, ThisID)>
    <cfset ChildrenIDs=ChildrenFromID[ThisID]>
    <cfloop from="#ArrayLen(ChildrenIDs)#" to="1" step="-1" index="i">
      <cfset ArrayPrepend(RootItems, ChildrenIDs[i])>
      <cfset ArrayPrepend(Depth, ThisDepth + 1)>
    </cfloop>
  </cfif>
</cfloop>
<cfreturn Ret>
</cffunction>
<cfset NewQuery=QueryTreeSort(getCats)>

Open in new window

0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
i got it working Thanks Mate.

I think For sure. I got this working when i gone through the code properly and read it again and again to ake it perfect for working.

Thanks Mate for Your Help
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
Thanks Mate
0
 
duncancummingCommented:
you're welcome.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.