Solved

Listings categories and subcategories only one/two level deep

Posted on 2009-05-08
10
356 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
Question by:myselfrandhawa
  • 6
  • 4
10 Comments
 
LVL 4

Expert Comment

by:galadore
Comment Utility
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:myselfrandhawa
Comment Utility
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
Comment Utility
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:myselfrandhawa
Comment Utility
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
Comment Utility
'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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 15

Author Comment

by:myselfrandhawa
Comment Utility
i try!

but i jump in sea and catch fish and eat .

:-))
0
 
LVL 15

Author Comment

by:myselfrandhawa
Comment Utility
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:myselfrandhawa
Comment Utility
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
Comment Utility
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:myselfrandhawa
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
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 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…
The purpose of this video is to demonstrate how to add AdSense Ads to a WordPress Website, and how to set up WordPress to automatically place Ads in Sidebars. This will be demonstrated using a Windows 8 PC. Log into your AdSense account. : Cli…

763 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

6 Experts available now in Live!

Get 1:1 Help Now