Show number of products after category

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']))
      			);

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']))
      			);

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>

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>

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 */
)