Post by frame » Tue Dec 23, 2008 4:39 am

Hi All,

I wanted to show the number of products after the name of each category (on the left panel in the frontend), so I changed the array in /catalog/extension/module/category.php from:

Code: Select all

$category_data = array();

			$results = $database->getRows("select c.category_id, cd.name from category c left join category_description cd on (c.category_id = cd.category_id) where parent_id = '0' and language_id = '" . (int)$language->getId() . "' order by c.sort_order");

    		foreach ($results as $result) {
      			$category_data[] = array(
       				'name' => $result['name'],
        			'href' => $url->href('category', false, array('path' => $result['category_id']))
      			);
to:

Code: Select all

$category_data[] = array(
				'name' =>$result['name'] ,
				'items' => $database->countRows("select * from product_to_category where category_id = '" . $result['category_id'] . "'"),
   			        'href' => $url->href('category', false, array('path' => $result['category_id']))
      			);
and the /catalog/template/default/module/category.tpl from:

Code: Select all

<div class="box">
  <div class="heading"><?php echo $heading_title; ?></div>
  <div class="category">
    <?php foreach ($categories as $category) { ?>
    <a href="<?php echo $category['href']; ?>"><?php echo $category['name']; ?></a>
    <?php } ?>
  </div>
</div>
to:

Code: Select all

<div class="box">
  <div class="heading"><?php echo $heading_title; ?></div>
  <div class="category">
    <?php foreach ($categories as $category) { ?>
    <a href="<?php echo $category['href']; ?>"><?php echo $category['name']; ?><?php if ($category['items'] <> "0") {echo " (" . $category['items'] . ")";} ?></a>
    <?php } ?>
  </div>
</div>
Everything works fine until I set a sub_category and add a product to it. In this case the category shows less items, as the category_id changed in the product_to_category table (the reason is visible in the category table). My aim is to count all the products in the given sub_categories and add this number to the products in the category. Or is it possible to count them in one step? Unfortunately I am not skilled enough to code it. Could somebody point me in the right direction?
thank you in advance
Frame

New member

Posts

Joined
Sat Apr 26, 2008 7:24 pm

Post by Qphoria » Tue Dec 23, 2008 5:22 am

hmm.. my brain's not working today..

But somehow you have to count all rows from category where the product_to_category.category_id has either the matching ID in the category table, or the matching parent_id in the category table.

But I can't seem to form a simple sentence today, let alone a sql statement. Perhaps someone else can help this along further.

Image
Donate!|OpenCart Basics|GeoZones
Image


User avatar
Administrator

Posts

Joined
Tue Jul 22, 2008 3:02 am

Post by bruce » Tue Dec 23, 2008 10:24 am

The sql statement that counts all of the products in the current category and all of the sub-categories is

Code: Select all

select count(p2c.product_id) from product_to_category p2c
inner join category c
on c.category_id = p2c.category_id
and (c.path = '27'  /* is a parent category */
or c.path like '27_%'   /* is the first category in the tree */
or c.path like '%_27'   /* is the last category in the tree */
or c.path like '%_27_%' /* is an intermediate category in the tree */
)
In the example shown, the category_id was 27

Cheers

Bruce

Active Member

Posts

Joined
Wed Dec 12, 2007 2:26 pm

Post by frame » Tue Dec 23, 2008 4:03 pm

Thank you Bruce!
It works perfectly.
I must learn this inner join trick.
Frame

New member

Posts

Joined
Sat Apr 26, 2008 7:24 pm
Who is online

Users browsing this forum: No registered users and 2 guests