Link to home
Start Free TrialLog in
Avatar of Coast Line
Coast LineFlag for Canada

asked on

Listing categories and Subcategories

Hi experts. I have a table as:

catID
category
ParentID

I have one select box and it lists all the categories.

my categories come as:

flower
plants
egg
egg

now egg is appearing under flower and plants too. can anyone please tell how can i put the one like as:

flower
---egg
plant
---egg
Avatar of duncancumming
duncancumming
Flag of United Kingdom of Great Britain and Northern Ireland image

use grouped output.  

You don't specify if a subcategory can have its own subcategories, i.e. a tree structure, or if there is only one level of depth; category-subcategory.  However I'll assume the latter, because it makes this much simpler.

Your query I'm guessing just now is simply doing SELECT * FROM CATEGORIES.  You're going to have to make that a bit more complex.  We'll join the table to itself, on the catID-parentID columns.  I've done left outer join, as I assume some categories don't have subcategories.


<cfquery name="getCats" datasource="dsn">
	SELECT c1.catID, c1.category, c2.catID AS SubCatID, c2.category AS SubCategory
	FROM Categories c1
		LEFT OUTER JOIN Categories c2
		ON c1.catID = c2.ParentID
	ORDER BY c1.catID, c2.catID
</cfquery>
 
<cfoutput query="getCats" group="catID">
	#getCats.category#<br>
	
	<cfoutput>
		--- #getCats.SubCategory#<br>
	</cfoutput>
</cfoutput>

Open in new window

Avatar of _agx_
> ORDER BY c1.catID, c2.catID

Ducan has the right idea.  Though you will probably want to order the results by category "names" rather than just id's:

ORDER BY c1.category, c1.catID, c2.category, c2.catID

Avatar of Coast Line

ASKER

>> You don't specify if a subcategory can have its own subcategories, i.e. a tree structure
yes this can have [subcategories can have subcategories.]

will the above same will work like this:

same
anyone experts
i ran this code and got this:

<selectname="test">
 
<cfoutputquery="getCats" group="cID">
<option>#getCats.title#<br>
       
       <cfoutput>
               --- #getCats.SubCategory#<br>
       </cfoutput></option>
</cfoutput>
 
 </select>
and what i got is like this:


flower ---
plant ---
flower ---egg
plant -egg

I've no idea what's up with all the <op></op> stuff in your code...  However, I forgot you were wanting all this in a select box.  So let's modify my original code.

This will put every category and sub-category in its own <option></option>

<select name="test">
<cfoutput query="getCats" group="catID">
        <option value="#getCats.catID#">#getCats.category#</option>
        
        <cfoutput>
                 <option value="#getCats.SubCatID#">--- #getCats.SubCategory#</option>
        </cfoutput>
</cfoutput>
</select>

Open in new window

Almost there but little left:

here what i get:

---
egg
---
egg
---
flower
---egg
plant
---egg


Please provide a dump of your query
because it's an outer join, the category may not have a subcategory

So, simply don't show the child options if the parent has no child...
<select name="test">
<cfoutput query="getCats" group="catID">
        <option value="#getCats.catID#">#getCats.category#</option>
        <cfoutput>
        <cfif val(getCates.subCatID)>
           <option value="#getCats.SubCatID#">--- #getCats.SubCategory#</option>
        </cfif>
        </cfoutput>
</cfoutput>
</select>

Open in new window

You Guys rock!!
One last question this lists only if i have one subcat, what if i have subcats under subcats.. this query fails over that thing.

can this be elaborated more on this.

Regards
Well this also makes a double entry. like it lists the subcat under a category and then it display it as category too:

like:

flower
---egg
egg

what is this
If you have multiple nested subcategories, the solution becomes much more complicated and involves recursion and 'nested set trees' (google that).  Luckily someone has written what looks like a very handy CFC to deal with just that:
http://stannard.net.au/blog/index.cfm/2008/5/30/Nested-Set-Trees-in-ColdFusion

ASKER CERTIFIED SOLUTION
Avatar of duncancumming
duncancumming
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks