You are here:  » Categories incl. product count in sidebar


Categories incl. product count in sidebar

Submitted by IG on Fri, 2014-05-30 10:09 in

Hi David

I would like to show all categories in the sidebar of my new site {link saved}. Ideally in brackets it will show the product count, something like this:

Beauty (2)
Games (1)
Food (1)
Music (3)
Toys (1)

The site will have only about 50 products in total, so the server load shouldn't be an issue.

I am sure this can be done, but I am rather clueless how.

Kind regards,
IG

Submitted by support on Fri, 2014-05-30 10:27

Hello IG,

No problem! Here's the code to do exactly that:

  $sql = "SELECT category,COUNT(id) AS categoryCount
            FROM `".$config_databaseTablePrefix."products`
            WHERE category <> '' GROUP BY category ORDER BY category";
  if (database_querySelect($sql,$rows))
  {
    print "<ul>";
    foreach($rows as $row)
    {
      if ($config_useRewrite)
      {
        $href = $config_baseHREF."category/".urlencode(tapestry_hyphenate($row["category"]))."/";
      }
      else
      {
        $href = $config_baseHREF."search.php?q=category:".urlencode($row["category"]);
      }
      print "<li><a href='".$href."'>".$row["category"]."</a> (".$row["categoryCount"].")</li>";
    }
    print "</ul>";
  }

Cheers,
David.
--
PriceTapestry.com

Submitted by richard on Tue, 2016-05-24 11:02

Hi David,

I have modified the above code to work with the search filter in v15/09 (thread http://www.pricetapestry.com/node/5473) so that I have a count figure in brackets.

      if ($parts[0] != "spinspeed")
      {
        $sql1 = "SELECT DISTINCT(spinspeed) FROM `".$config_databaseTablePrefix."products` WHERE ".$where.$priceWhere." AND spinspeed <> '' ORDER BY spinspeed";
$sql1 = "SELECT spinspeed,COUNT(id) AS spinspeedCount FROM `".$config_databaseTablePrefix."products` WHERE spinspeed <> '' GROUP BY spinspeed ORDER BY spinspeed";
        if (database_querySelect($sql1,$rows1))
        {
          print "<div class='small-12 medium-2 columns'>";
          print "<label>".translate("Spinspeed")."<br />";
          print "<select onchange='JavaScript:this.form.submit();' name='spinspeedFilter'>";
          print "<option value=''>".translate("All")."</option>";
          foreach($rows1 as $row)
          {
            $selected = ($spinspeedFilter==$row["spinspeed"]?"selected='selected'":"");
            print "<option value='".htmlspecialchars($row["spinspeed"],ENT_QUOTES,$config_charset)."' ".$selected.">".$row["spinspeed"]." (".$row["spinspeedCount"].")"."</option>";
          }
          print "</select>";
          print "</label>";
          print "</div>";
        }
      }

I would like this count figure to update whevever another filter is selected.

I would appreciate your thoughts on how I should amend the above code to achieve this.

Many thanks,

Richard

Submitted by support on Tue, 2016-05-24 11:07

Hi Richard,

The alternative $sql1 value doesn't include $where and $priceWhere - that should be all it is - have a go with:

$sql1 = "SELECT spinspeed,COUNT(id) AS spinspeedCount FROM `".$config_databaseTablePrefix."products` WHERE ".$where.$priceWhere." AND spinspeed <> '' GROUP BY spinspeed ORDER BY spinspeed";

Hope this helps!

Cheers,
David.
--
PriceTapestry.com

Submitted by richard on Tue, 2016-05-24 12:24

Works a treat :)

Thank you