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

asked on

Listings categories and subcategories only one/two level deep

Hi experts, i have parentID, catID in my table categories.

catID, parentID, category

i want to display the categories and subcategories under categories with 2 level deep only, how can i do so.
Avatar of galadore
galadore
Flag of United States of America image

This is really a SQL question, but you could also filter the query results in CFML.  

Guessing that the parentID is null for your main categories you could do it a few different ways.  Here's a quick and dirty way without doing a join.  This is slower for sure since you're running new queries for each main category, but if you don't have many results it shouldn't be too noticeable.

If you have a lot of results, look at a JOIN as an option next.  It kinda depends on teh database you're using too, so be sure to post that too.

<cfquery name="getmaincats" datasource="yourDB">
SELECT * FROM category
WHERE parentID is Null
</cfquery>
 
<cfloop query="getmaincats">
<cfquery name="getsecondcats" datasource="yourDB">
SELECT * FROM category
WHERE parentID = #catID#
</cfquery>
<cfoutput query="getsecondcats">
<!---  Your output formatting here --->
#category#<br />
<!---  / Your output formatting here --->
</cfoutput>
</cfloop>

Open in new window

Avatar of Coast Line

ASKER

very thanks.,

but records tend to a lot. also i start my parentID with 0 as the base. i mean 0 represents main category.


if you provid me a little help using join, that nice of you
ASKER CERTIFIED SOLUTION
Avatar of galadore
galadore
Flag of United States of America 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
i prefer joins due to speed, if you can show joins example i will be able to finish off quickly
'teach a man to fish..'

At least attempt to code something for yourself and I'll gladly take a look at it, but I can't do it all for you.  Since you prefer joins, you must already be familiar with the way they work.
i try!

but i jump in sea and catch fish and eat .

:-))
here it is:

i catched the fish and ate it :P

SELECT c1.catID, c1.catvisibility, c1.catName, c2.catID AS SubCatID, c2.catName AS SubCategory
        FROM Categories c1 LEFT OUTER JOIN Categories c2 ON c1.catID = c2.ParentID
        WHERE 
        c1.catvisibility = <cfqueryparam cfsqltype="cf_sql_numeric" value="1">
        ORDER BY c1.catName, c1.catID, c2.catName, c2.catID

Open in new window

i hope you might have looked at the query i created using a left outer join.

now issue i have is i have one category whose catID is 1 and parentID is 0.

all other category have a parentID of 1 as they are the children of the above listed category.

so this does not me proper results, can u put some light on it. what is going on.
You really need to provide all the code.  From what you're describing, it could be the query or it could be how you reference the query results in the output.
the thing is once i run this query, i get results like this.

i have it attached here.

what i want is i have one main category FOOD and want that all its subcategories should appear under Food as subcategories.

i think the diagram litsed here will help you in checking what i want to do.
<input id="gwProxy" type="hidden"><!--Session data--><input onclick="jsCall();" id="jsProxy" type="hidden">
i have to use this code in the javascript sliding menu as:
 
var s0=awmCreateMenu(0,0,0,0,1,0,0,0,0,10,10,0,0,0,1,1,0,n,n,100,1,0,0,0,200,-1,1,200,200,0,0,0,"0,0,0",n,n,n,n,n,n,n,n,0,0,0,0);
<cfoutput query="show">
it=s0.addItemWithImages(1,2,n,"#catname#",n,"","",n,n,n,3,3,3,0,0,1,"",n,n,n,n,n,0,0,2,n,5,6,n,7,8,1,1,1,0,0,n,n,n);
<cfif show.SUBCATID IS not "">
var s1=it.addSubmenu(0,0,0,1,3,0,0,0,1,1,0,n,n,100,0,3,0,-1,1,200,200,0,0,"0,0,0",0,"1,0,0,1,0,0,15,0,1");
it=s1.addItemWithImages(3,4,n,"#subcategory#",n,"","",n,n,n,3,3,3,n,n,n,"",n,n,n,n,n,0,0,2,n,n,n,n,n,n,0,0,0,0,0,n,n,n);
<cfelse>
</cfif>
</cfoutput>
s0.pm.buildMenu();
}}

Open in new window

coool.JPG