Solved

Listings categories and subcategories only one/two level deep

Posted on 2009-05-08
10
363 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 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SPLUNK REST  API call to Splunk to create and index? 2 122
Coldfusion RegEx 8 63
exchange 2010 turning off 3des ciphers 2 199
Stream live video from Raspberry Pi camera 22 161
I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
Introduction In this tutorial, I'll explain how to create an animated progress meter in a wireframe prototype developed using Axure RP 7.0 - a leading prototyping tool for designing web sites and software. (For more information about Axure and gett…
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…

809 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