Solved

Listings categories and subcategories only one/two level deep

Posted on 2009-05-08
10
359 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
  • 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 15

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
 
LVL 15

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

 
LVL 15

Author Comment

by:Gurpreet Singh Randhawa
ID: 24345266
i try!

but i jump in sea and catch fish and eat .

:-))
0
 
LVL 15

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 15

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 15

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Google Maps 7 72
wordpress email form 23 72
Coldfusion loop through a list of pairs name  -  value 3 36
Public IP Address Amazon Servers 2 41
Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
Objective of This Article In 1990’s, when I was a budding software professional, I had a lot of confusion about which stream or technology, I had to choose to build my career. In those days, I had lot of confusion like whether to choose System so…
The purpose of this video is to demonstrate how to connect a WordPress website to Google Analytics. This will be demonstrated using a Windows 8 PC Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php :…
The purpose of this video is to demonstrate how to integrate Mailchimp with Facebook. This will be demonstrated using a Windows 8 PC. Mailchimp and Facebook will be used. Log into your Mailchimp account. : Click on your name. Go to Account Setti…

939 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now