Coast Line
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
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
> 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
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
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
yes this can have [subcategories can have subcategories.]
will the above same will work like this:
same
ASKER
anyone experts
ASKER
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
<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>
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>
ASKER
Almost there but little left:
here what i get:
---
egg
---
egg
---
flower
---egg
plant
---egg
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...
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>
ASKER
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
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
ASKER
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
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
http://stannard.net.au/blog/index.cfm/2008/5/30/Nested-Set-Trees-in-ColdFusion
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks
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.
Open in new window