I'd call you brilliant but you made fun of my chunk of code ;) I cant believe I overlooked repeatstring()! Thanks you thank you thank you.
Unfortunately I'm not the greatest when it comes to complex sql so I never looked at nested set to start with. Care to enlighten? My late night is done! Hoorah! -e -----Original Message----- From: Barney Boisvert [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 17, 2004 1:01 AM To: CF-Talk Subject: Re: category-unlimited subcategory formatted dropdowns First, all those queries are going to be really slow. The code as-is will be making n+m+1 queries to generate the dropdown, where n is the number of individual elements, and m is the number of elements with children. The "+1" is for the initial query for items with no parent. And yes, you can count the same element in both n and m, so worst case scenario would be 2n queries. However, depending on what you're doing with the result, that might not matter. For example, if you're caching it. Or, if it's a low-use item, then it might not be worth the time to optimize. One alternate route would be to use a nested set model. But that's not your question. To solve the dash insertion problem, make another optional attribute named 'depth' (or 'level') which is omitted from the initial call (just like parentItemID) and defaults to zero. Then add #repeatString("-", attributes.depth)# before the category name within the option. Finally, pass a 'depth' attribute on the recursive calls, incrementing by one. So the call with look like this: <cf_makedump parentItemId="#getCurrentCat.catID#" dsn="#attributes.dsn#" depth="#attributes.depth + 1#"> cheers, barneyb On Tue, 16 Nov 2004 23:31:31 -0500, Emmet McGovern <[EMAIL PROTECTED]> wrote: > I have a table that contains categories and unlimited subcategories > referencing and I need to create a formatted drop down list. I've been > messin around with it for a while and now with no luck. I need the list to > go like so... > > Category Name > --Child of category > --Child of category > ----Child of sub category > ------Child of sub sub category > --Child of category > > I can get the data out in the proper order, I just cant seem to get the -- > in there. > > I'm using this code as a custom tag which calls itself back again for the > recursion. > > Thanks for the help > -emmet > > <! --- // Calling the initial tag in the dropdown > <SELECT name="parentID" > <cf_makedrop dsn=#dsn#> > </SELECT> > > <! --- // now the tag makedrop.cfm > > <cfsilent> > <cfparam name="Variables.ParentItemID" default="0"> > <cfif IsDefined("Attributes.ParentItemID")> > <cfset Variables.ParentItemID = Attributes.ParentItemID > > </cfif> > > <CFQUERY NAME="GetCurrentCat" DATASOURCE="#attributes.dsn#"> > SELECT * > FROM category > WHERE ParentID = #Variables.ParentItemID# > ORDER BY name > </CFQUERY> > > </cfsilent> > <cfloop query="GetCurrentCat"> > <cfoutput> > <OPTION value="#catID#" <cfif getcurrentCat.parentID is > 0>class="lightcell"</cfif>>#GetCurrentCat.name#</OPTION> > </cfoutput> > > <cfsilent> > <CFQUERY NAME="CheckForSub" DATASOURCE="#attributes.dsn#"> > SELECT * > FROM category > WHERE parentID = #GetCurrentCat.catID# > </CFQUERY> > </cfsilent> > > <cfif CheckForSub.RecordCount gt 0 > > <cf_makedrop > ParentItemID="#GetCurrentCat.catID#" > dsn="#attributes.dsn#"> > </cfif> > </cfloop> > -- Barney Boisvert [EMAIL PROTECTED] 360.319.6145 http://www.barneyb.com/blog/ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:184525 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
