Solved

categories and subcategories

Posted on 2009-05-14
6
359 Views
Last Modified: 2013-12-24
i have a table with catID, parentID, catname

i have relationship in table which is nested very deep. but i want to show to the end user, only one level deep.

like if parentID is 0, another subcategory has parentID of 1. that's it, no more level deep. can experts here tell me that my query is right or wrong, if right, then it is not fetching right results:


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

ORDER BY c1.catName, c1.catID, c2.catName, c2.catID

Open in new window

0
Comment
Question by:myselfrandhawa
  • 3
  • 2
6 Comments
 
LVL 1

Expert Comment

by:boodyguard
ID: 24389948
Hi,

Not so easy without seeing your db. But if I undertand correctly,PARENT_ID=0 is your root.
 You want to select only your root level ?
Or you want to select your root level and first sublevel ?
0
 
LVL 13

Expert Comment

by:usachrisk1983
ID: 24391887
Your query is going to select all categories, even if they are not at the root.  The query below should select only records that are either at the root (parentID = 0) or are children of the first level of the parent.

If instead of 0 you have null as the parentID of root level cateogires, use "where parent.parentid is null" instead of "= 0".  If you want to only include parents when there are children, change the "left join" to just "join".

select parent.catID

     , parent.catVisibility

     , parent.catName

     , children.catID as subCatID

     , children.catName as subCategory

  from categories parent

  left join categories children

    on children.parentID = parent.catid

 where parent.parentid = 0

Open in new window

0
 
LVL 15

Author Comment

by:myselfrandhawa
ID: 24392332
Thanks It works. but only one issue.

When i try to get it by separing parents and its one level deep subcategories. it does not show up all categories.

i tried something like this:

i tried to use the group attribute so that it show relevant subcategories under its main categories, but it shows only a single record. the image is attched with visual presentation. if i omit group. that image is also attached. please check if i am doing something wrong.


<input id="gwProxy" type="hidden"><!--Session data--><input onclick="jsCall();" id="jsProxy" type="hidden">
<script type="text/javascript">

// <![CDATA[

var myMenu;

myMenu.speed = 3;                     // Menu sliding speed (1 - 5 recomended)

myMenu.remember = true;               // Store menu states (expanded or collapsed) in cookie and restore later

myMenu.oneSmOnly = true;             // One expanded submenu at a time

myMenu.markCurrent = true;            // Mark current link / page (link.href == location.href)

window.onload = function() {

myMenu = new SDMenu("my_menu");

myMenu.init();

};

// ]]>

</script>

</head>
 

<body>

<div id="my_menu" class="sdmenu">

<cfoutput query="show" group="catName">

<div>

<span>#CATNAME#</span>

<cfif subcatID IS "">

<cfelse>

<a href="">#SUBCATEGORY#</a>

</cfif>

</div>

</cfoutput>

</body>

</html>

Open in new window

9000.GIF
9001.GIF
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 15

Author Comment

by:myselfrandhawa
ID: 24393363
my every approach is going wromg, i tried like this:<input id="gwProxy" type="hidden"><!--Session data--><input onclick="jsCall();" id="jsProxy" type="hidden">
<div id="my_menu" class="sdmenu">

<cfloop query="show">

<cfoutput>

<div>

<span>#catName#</span>

<a href="http://tools.dynamicdrive.com/imageoptimizer/">#subcategory#</a>

</div>

</cfoutput>

</cfloop>

</div>

Open in new window

0
 
LVL 15

Author Comment

by:myselfrandhawa
ID: 24393392
i tried something like this:

here is my sdmenu
<input id="gwProxy" type="hidden"><!--Session data--><input onclick="jsCall();" id="jsProxy" type="hidden">
<div id="my_menu" class="sdmenu">

<cfoutput query="show" group="catName">

<div>

<span>#catName#</span>

<a href="http://tools.dynamicdrive.com/imageoptimizer/">#subcategory#</a>

</div>

</cfoutput>

</div>
 

only single one record under a category. while i have 10 subcategories records under 1 category
 
 

div.sdmenu {

	width: 150px;

	font-family: Arial, sans-serif;

	font-size: 12px;

	padding-bottom: 10px;

	background: url(bottom.gif) no-repeat  right bottom;

	color: #fff;

}

div.sdmenu div {

	background: url(title.gif) repeat-x;

	overflow: hidden;

}

div.sdmenu div:first-child {

	background: url(toptitle.gif) no-repeat;

}

div.sdmenu div.collapsed {

	height: 25px;

}

div.sdmenu div span {

	display: block;

	padding: 5px 25px;

	font-weight: bold;

	color: white;

	background: url(expanded.gif) no-repeat 10px center;

	cursor: default;

	border-bottom: 1px solid #ddd;

}

div.sdmenu div.collapsed span {

	background-image: url(collapsed.gif);

}

div.sdmenu div a {

	padding: 5px 10px;

	background: #eee;

	display: block;

	border-bottom: 1px solid #ddd;

	color: #066;

}

div.sdmenu div a.current {

	background : #ccc;

}

div.sdmenu div a:hover {

	background : #066 url(linkarrow.gif) no-repeat right center;

	color: #fff;

	text-decoration: none;

}

Open in new window

0
 
LVL 13

Accepted Solution

by:
usachrisk1983 earned 500 total points
ID: 24394482
When you use the GROUP parameter inside a CFOUTPUT tag, there needs to be a nested CFOUTPUT tag so that it knows what to do with the subcategories.  You'll also need to ORDER your SQL statement by the catName for this to work, so add:

order by parent.catName asc

to your SQL statement.  You can do a secondary order on the subcategory - but you don't need to.  I don't have CF running in front of me, but try something like the code snippet (once your SQL is updated).  

What's happening there is that it'll output the catName, and then loop over the subcategories that have that catName and output the subcategory.  For this to work your query muts be ordered by the catName, otherwise you'll get multiple catNames if they're out of order.

<body>

<div id="my_menu" class="sdmenu">

  <cfoutput query="show" group="catName">

     <div>

       <span>#catName#</span>

       <cfoutput>

          <a href="">#show.subcategory#</a><br>

       </cfoutput>

     </div> 

  </cfoutput>

</div>

</body>

Open in new window

0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
The purpose of this video is to demonstrate how to create a Printer Friendly PDF on a WordPress Page. This will be demonstrated using a Windows 8 PC. Tools Used are Photoshop, Awesome Screenshot” Google Chrome Extension, and SmallPDF.com Log…
The purpose of this video is to demonstrate how to set up an RSS Feed on a WordPress Website. This will be demonstrated using a Windows 8 PC. Feedburner will be used for this demonstration. Go to your WordPress login page. This will look like the…

706 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

19 Experts available now in Live!

Get 1:1 Help Now