Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Listings categories and subcategories only one/two level deep

Posted on 2009-05-08
10
Medium Priority
?
378 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 1000 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

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

Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
What You Need to Know when Searching for a Webhost Provider
The purpose of this video is to demonstrate how to Test the speed of a WordPress Website. Site Speed is an important metric of a site’s health. Slow site speed can result in viewers leaving your site quickly and not seeing your content. This…
The purpose of this video is to demonstrate how to integrate Mailchimp with WordPress, by placing a Mailchimp signup form on a WordPress Page or Post. This will be demonstrated using a Windows 8 PC. Mailchimp will be used. Log into your Mailchi…

578 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