how to fetch subcategories of main category
I have table:
catID
catName
parentID
i am trying to use the cfselect statement to bind the subcategories to main category.
catID
catName
parentID
i am trying to use the cfselect statement to bind the subcategories to main category.
i have something like this:
<cfquery name="stuff">
select * from category where parentID = 0
</cfquery>
<select name="category" class="textfield_effect" id="category">
<cfoutput query="Stuff">
<option value="#catID#" <cfif stuff.catID eq #ccat#>selected</cfif>>#catName#</option>
</cfoutput>
</select>
now i use this:
<cfselect name="subcat" class="textfield_effect" bindOnLoad="yes" id="subcat" bind="cfc:#request.cfcPath#.statecity.getsubcat({category})" display="catName" value="catID"></cfselect>
<cfquery name="myset" datasource="#request.dsn#" username="#request.user#" password="#request.pass#">
SELECT catname
FROM categories
WHERE catID = <cfqueryparam cfsqltype="cf_sql_numeric" value="#arguments.category#">
</cfquery>
i know i need some kinds of joins to make it work completely how i do. it can be n level deep
how i load it it in my query can anyone show me a way.
i think i asked this stuff lot many times but never able to get it properly.
ASKER
select all the children of the selected category<input id="gwProxy" type="hidden"><!--Session data--><input onclick="jsCall();" id="jsProxy" type="hidden">
so you need to select the categories who are the children of arguments.category
these are your fields...
catID
catName
parentID
.. and your data looks like this...
CatID CatName ParentID
1 Food 0
2 Fruit 1
3 Veggies 1
4 Sweets 1
5 Clothing 0
6 Suits 5
Let's say you want to find the children of Food, which is catID 1, so you pass in arguments.category of 1
select * from categories
where....
ASKER
???????????????????? i did not understood
<input id="gwProxy" type="hidden"><!--Session data--><input onclick="jsCall();" id="jsProxy" type="hidden">
<input id="gwProxy" type="hidden"><!--Session data--><input onclick="jsCall();" id="jsProxy" type="hidden">
I was trying to show you how the categories table works.
Do you understand this listing of the data (of course this is not your data, some i thought of)
CatID CatName ParentID
1 Food 0
2 Fruit 1
3 Veggies 1
4 Sweets 1
5 Clothing 0
6 Suits 5
In your application, the user select the main category
select * from categories where parentID = 0
This gives all the top level categories. Let's say he select FOOD, which is catID = 1
Now, in your second select box you need to show the sub categories of FOOD.
How do you select the categories whose parent is catID = 1 (FOOD) ?
Do you understand this listing of the data (of course this is not your data, some i thought of)
CatID CatName ParentID
1 Food 0
2 Fruit 1
3 Veggies 1
4 Sweets 1
5 Clothing 0
6 Suits 5
In your application, the user select the main category
select * from categories where parentID = 0
This gives all the top level categories. Let's say he select FOOD, which is catID = 1
Now, in your second select box you need to show the sub categories of FOOD.
How do you select the categories whose parent is catID = 1 (FOOD) ?
ASKER
>> How do you select the categories whose parent is catID = 1 (FOOD) ?
Exactly what i am looking for
Exactly what i am looking for
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
what if subcategory has a subcategory will inner joins come up there<input id="gwProxy" type="hidden"><!--Session data--><input onclick="jsCall();" id="jsProxy" type="hidden">
> what if subcategory has a subcategory will inner joins come up there
This is a list of children of the catID. It is showing only the immediate children, not the children of the children. So if there the subcategory has subcategories, it will not appear on this select.
The question is, what do you want it to do?
If you want it to show subcategories of subcategories, how would you expect these to look in the SELECT tag? Do you expect to show some hierarchy? (show how one is a child of another?)
A select tag shows a list...
Veggies
Fruit
Sweets
This is a list of children of the catID. It is showing only the immediate children, not the children of the children. So if there the subcategory has subcategories, it will not appear on this select.
The question is, what do you want it to do?
If you want it to show subcategories of subcategories, how would you expect these to look in the SELECT tag? Do you expect to show some hierarchy? (show how one is a child of another?)
A select tag shows a list...
Veggies
Fruit
Sweets
ASKER
Thanks for all. Actually your previous post logic help question helped me a lot in revising the tables and code but due to that functionality i am completely overdone here maybe due to my unexpectd databse structure.
change occured same at a time.
if u look at my this question hope u get idea what exactly i am trying to do.
https://www.experts-exchange.com/questions/24428765/My-cffunction-contains-three-queries.html
Please Please Please have a look and Guide me Regards
<input id="gwProxy" type="hidden"><!--Session data--><input onclick="jsCall();" id="jsProxy" type="hidden">
change occured same at a time.
if u look at my this question hope u get idea what exactly i am trying to do.
https://www.experts-exchange.com/questions/24428765/My-cffunction-contains-three-queries.html
Please Please Please have a look and Guide me Regards
<input id="gwProxy" type="hidden"><!--Session data--><input onclick="jsCall();" id="jsProxy" type="hidden">
SELECT catname
FROM categories
WHERE catID = <cfqueryparam cfsqltype="cf_sql_numeric"
You are taking the passed in arguments.category (which is actually the categoryID)
and fetching from the categories table where the catID = this category
I suspect that this will return one record, the same record as the category ID.
Is that what you want? To fetch just the one category in the second select?
Or did you want to get a list of all the children of that categoryID ? If so, you have to change the where clause to select the children of category....