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.


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.

Open in new window

LVL 16
Gurpreet Singh RandhawaCEOAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gdemariaCommented:
In this select....

SELECT catname
    FROM categories
    WHERE catID = <cfqueryparam cfsqltype="cf_sql_numeric" value="#arguments.category#">

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....  




0
Gurpreet Singh RandhawaCEOAuthor Commented:
select all the children of the selected category<input id="gwProxy" type="hidden"><!--Session data--><input onclick="jsCall();" id="jsProxy" type="hidden">
0
gdemariaCommented:

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....
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Gurpreet Singh RandhawaCEOAuthor Commented:
???????????????????? i did not understood



<input id="gwProxy" type="hidden"><!--Session data--><input onclick="jsCall();" id="jsProxy" type="hidden">
0
gdemariaCommented:
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)  ?

0
Gurpreet Singh RandhawaCEOAuthor Commented:
>> How do you select the categories whose parent is catID = 1 (FOOD)  ?

Exactly what i am looking for
0
gdemariaCommented:

 To select the categories whose parent is FOOD (catID=1)

 You need to use this as your where clause...


    WHERE ParentID = 1


 It's important that you understand WHY, or you will be lost for the entire project..

<cfquery name="myset" ..>
  SELECT catname
  FROM categories
  WHERE ParentID = <cfqueryparam cfsqltype="cf_sql_numeric" value="#arguments.category#">
</cfquery>

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gurpreet Singh RandhawaCEOAuthor Commented:
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">
0
gdemariaCommented:
> 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
 
0
Gurpreet Singh RandhawaCEOAuthor Commented:
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.

http://www.experts-exchange.com/Web_Development/Software/ColdFusion_Studio/Q_24428765.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">
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.