Count total items in category tree

erikhillis

Hello

I've been looking around for a while for a method of doing this, but haven't turned up anything. What I'm looking to do is count the number of items in the sub-categories of a main category, for example:

Main Category (1 total in category, 5 total in "tree")
- A (2 total in category, 4 total in tree)
-- A,B (1 total in category, 2 total in tree)
--- B,C (1 total in category)

Where the main category displays the count of the number of items in the main category itself, and in addition adds up the number of items in each sub-category, including the sub-category of the sub-category. Where the -'s represent a the level of the sub-category.

It's difficult to explain, but if anyone knows what I am talking about and can point me to something that may be helpful, please do.

Thanks

Protato

I have a category table that lists category_id, parent category, and other data. Then a table containing the items with a field for the category_id they belong to.

This code is a little dirty (old code), and you probably want to edit it to not use the globals array, but it will build you an array of all your categories. You can then loop through the array and grab the item count for each category_id. At the very least I hope this points you in the right direction.

   //on first run pass the parent cat to both vars
   function dbReturnSubCats($parent_cat, $cat_id){
      //reset the globals var on a new run.
      if($parent_cat == $cat_id){
         unset($GLOBALS[cat]);
         $GLOBALS[cat][] = $parent_cat;
      }


  $sql = "Select * FROM `faq_category` WHERE `parent` = '$cat_id'";
  mysql_select_db($GLOBALS[db2]);
  $result = mysql_query($sql) or die("Failed to match sub-cats for category $cat_id".mysql_error());

  if(mysql_num_rows($result) > 0){
     while($row = mysql_fetch_array($result)){
        $GLOBALS[cat][] = $row[website_category_id];
        $this->dbReturnSubCats($parent_cat, $row[website_category_id]);
     }
  }
return true;  

}

cgraz

are these categories stored in a database, array, textfile? Can you post more info?

erikhillis

The categories are in a database.

Inside the category table, the rows are:
cat_id
category (as in the name)
subcat, which is either "yes" or "no"
and parent_id

Main category example:

cat_id = 1,
category = Main Category,
subcat = no,
parent_id = 0

Subcat Example:
cat_id = 2,
category = Some name,
subcat = yes,
parent_id = 1

cgraz

Hmm...I'm not quite sure how you'd go about doing that without multiple queries or some kind of recursion in php. Have you looked at this article on managing hierarchical data in MySQL?

There's a query just past the halfway mark that does just what you're looking for.

erikhillis

The tree display is fine, I just don't know how to go about the counting.

Protato

erikhillis wrote:
The tree display is fine, I just don't know how to go about the counting.

My code example does what you are asking, is there a part I can elaborate on to help you out a little more?

After you push your parent cat to the function and build the subcat's you could do something like this to get the article count.

<?php

//get subcats in global array
$parent_cat = 1;
dbReturnSubCats($parent_cat, $parent_cat);

//this will hold our article count.
$article_count = 0;

//loop through parent and it's sub cats
foreach($GLOBALS[cat] as $k => $v){
   //get article count for current cat_id
   $sql = "Select count(article_id) as `article_count` FROM `faq_main` WHERE `cat_id` = '$v';
   $result = mysql_query($sql) or die("Failed to get article count for cat_id $v");
   $row = mysql_fetch_array($result);
   //add this cat count to total count
   $article_count += $row['article_count'];
}

?>

cgraz

I understand you're only looking for a count - but the type of count you're looking for is a little more complicated than that. Not only do you want the count for pages in a category, but want to include the count in the pages beneath it. The link I gave you will make this request and most other tree-related queries a breeze.

Protato: Your code likely works, but you're running a loop and initiating a new query each time. What happens if the loops runs 100-200 queries per page load? You'd be using up a significant amount of resources, and it would become disastrous as more visitors came to the site. That's without including any other queries that are already on the page.

If the SQL query for this is possible, it's well above my head. You might be able to try something along these lines:

SELECT c1.cat_id, c1.parent_id, COUNT(c1.cat_id) AS count 
FROM categories AS c1
LEFT JOIN categories AS c2
	ON c2.parent_id = c1.cat_id 
LEFT JOIN categories AS c3 
	ON c3.parent_id = c2.cat_id
LEFT JOIN categories AS c4 
	ON c4.parent_id = c3.cat_id
GROUP BY c1.parent_id, c1.cat_id WITH ROLLUP;

But you are going to need to do a lot of work in PHP to get your results. Some sort of recursive function with the use of if statements. You're also limited here to 4 levels - so if you have anything deeper than that, you're going to have to adjust the query.

That's why I recommended you take a look at that article if you really need this functionality.