Solved

Parents and its Child in select box using coldfusion

Posted on 2009-04-05
18
313 Views
Last Modified: 2013-12-16
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

0
Comment
Question by:myselfrandhawa
  • 9
  • 9
18 Comments
 
LVL 16

Expert Comment

by:duncancumming
Comment Utility
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
 
LVL 15

Author Comment

by:myselfrandhawa
Comment Utility
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
 
LVL 16

Expert Comment

by:duncancumming
Comment Utility
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
 
LVL 16

Expert Comment

by:duncancumming
Comment Utility
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
 
LVL 15

Author Comment

by:myselfrandhawa
Comment Utility
Thanks You, How do i call this in select box.
0
 
LVL 16

Expert Comment

by:duncancumming
Comment Utility
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
 
LVL 15

Author Comment

by:myselfrandhawa
Comment Utility
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
 
LVL 15

Author Comment

by:myselfrandhawa
Comment Utility
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
 
LVL 16

Expert Comment

by:duncancumming
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 15

Author Comment

by:myselfrandhawa
Comment Utility
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
 
LVL 16

Expert Comment

by:duncancumming
Comment Utility
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
 
LVL 15

Author Comment

by:myselfrandhawa
Comment Utility
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
 
LVL 16

Expert Comment

by:duncancumming
Comment Utility
QueryTreeSort needs to be in a <cffunction>.
0
 
LVL 15

Author Comment

by:myselfrandhawa
Comment Utility
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
 
LVL 16

Accepted Solution

by:
duncancumming earned 500 total points
Comment Utility
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
 
LVL 15

Author Comment

by:myselfrandhawa
Comment Utility
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
 
LVL 15

Author Closing Comment

by:myselfrandhawa
Comment Utility
Thanks Mate
0
 
LVL 16

Expert Comment

by:duncancumming
Comment Utility
you're welcome.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Today, I was working on some optimization and spam-stopping techniques when I encountered Ben Nadel's post to reduce spam feature using Math (http://www.bennadel.com/blog/197-How-I-Stop-Spammers-On-My-ColdFusion-Blog.htm). While this method is not o…
Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
The purpose of this video is to demonstrate how to prevent comment spam on a WordPress Website. This will be demonstrated using a Windows 8 PC. Plugin Akismet will be used. Go to your WordPress login page. This will look like the following: myw…
The purpose of this video is to demonstrate how to set up an RSS Feed on a WordPress Website. This will be demonstrated using a Windows 8 PC. Feedburner will be used for this demonstration. Go to your WordPress login page. This will look like the…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now