Solved

Listings categories and subcategories only one/two level deep

Posted on 2009-05-08
10
368 Views
Last Modified: 2013-12-20
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.
0
Comment
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 4

Expert Comment

by:galadore
ID: 24344876
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
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 24344963
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
 
LVL 4

Accepted Solution

by:
galadore earned 250 total points
ID: 24345159
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
Free NetCrunch network monitor licenses!

Only on Experts-Exchange: Sign-up for a free-trial and we'll send you your permanent license!

Here is what you get: 30 Nodes | Unlimited Sensors | No Time Restrictions | Absolutely FREE!

Act now. This offer ends July 14, 2017.

 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 24345198
i prefer joins due to speed, if you can show joins example i will be able to finish off quickly
0
 
LVL 4

Expert Comment

by:galadore
ID: 24345240
'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
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 24345266
i try!

but i jump in sea and catch fish and eat .

:-))
0
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 24345343
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
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 24345426
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
 
LVL 4

Expert Comment

by:galadore
ID: 24346125
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
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 24346778
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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article provides a case study on how our local youth baseball league deployed a new website, including the platform selection, implementation and benefits to the league.
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
The purpose of this video is to demonstrate how to create a Printer Friendly PDF on a WordPress Page. This will be demonstrated using a Windows 8 PC. Tools Used are Photoshop, Awesome Screenshot” Google Chrome Extension, and SmallPDF.com Log…
The purpose of this video is to demonstrate how to reset a WordPress password if you are locked out and cannot reset the password. A typical use would be if you cannot access the email to which WordPress would send the password recovery email to…

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question