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.
LVL 16
Gurpreet Singh RandhawaWeb DeveloperAsked:
Who is Participating?
 
galadoreConnect With a Mentor Commented:
If you aren't good with JOINs and would rather filter using CFML, try something like this:
<cfquery name="getmaincats" datasource="yourDB">
SELECT * FROM category
WHERE parentID = 0
</cfquery>
<cfquery name="getsecondcats" datasource="yourDB">
SELECT * FROM category
ORDER BY parentID
</cfquery>
 
<cfoutput>
<cfloop query="getmaincats">
<!---  Your main Category formatting here --->
<srong>#getmaincats.category#</strong><br />
<!---  / Your main Category formatting here --->
<cfloop query="getsecondcats">
<cfif getsecondcats.parentID is getmaincats.catID>
<!---  Your secondary formatting here --->
#getsecondcards.category#<br />
<!---  / Your secondary formatting here --->
</cfif>
</cfloop>
</cfloop>
</cfoutput>

Open in new window

0
 
galadoreCommented:
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

0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
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
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
i prefer joins due to speed, if you can show joins example i will be able to finish off quickly
0
 
galadoreCommented:
'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.
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
i try!

but i jump in sea and catch fish and eat .

:-))
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
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

0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
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.
0
 
galadoreCommented:
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.
0
 
Gurpreet Singh RandhawaWeb DeveloperAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.