Coast Line
asked on
Listings categories and subcategories only one/two level deep
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.
catID, parentID, category
i want to display the categories and subcategories under categories with 2 level deep only, how can i do so.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
i prefer joins due to speed, if you can show joins example i will be able to finish off quickly
'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.
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.
ASKER
i try!
but i jump in sea and catch fish and eat .
:-))
but i jump in sea and catch fish and eat .
:-))
ASKER
here it is:
i catched the fish and ate it :P
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
ASKER
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.
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.
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.
ASKER
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 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();
}}
coool.JPG
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.
Open in new window