top categories

Submitted by mgmedia on Mon, 2011-09-05 19:44 in Price Tapestry

Hi David ,

I'm trying to work out how I can get a top 10 categories if it's at all possible. What I would like is in my side bar to have the top 10 categories so that they are easily accessible to users. I assume this is possible to do... I have a top 10 categories on the site which I have also implemented into my admin section so that I can see how many times an item is clicked on, but for some reason I can't work it for categories. The main thing I need is them on the home page to help users. Could you help me please

Thanks

Ben

Hello Ben, It should be

Submitted by support on Tue, 2011-09-06 11:51

Hello Ben,

It should be reasonably straight forward with a small change to the SQL that you're using for top products. Assuming that you've added a `clicks` column to your Products Table, the SQL and code to select top 10 category would be something like this:

$sql = "SELECT category,totalclicks AS (SUM(clicks)) FROM `".$config_databaseTablePrefix."products` WHERE category <> '' GROUP BY category ORDER BY totalclicks DESC LIMIT 10";

if (database_querySelect($sql,$rows))

{

  print "<h4>Top Categories</h4>";

  print "<ul>";

  foreach($rows as $row)

  {

    print "<li><a href='".$config_baseHREF."search.php?q=category:".urlencode($row["category"])."'>".$row["category"]."</a></li>";

  }

  print "</ul>";

}

Hope this helps!

Cheers,
David.
--
PriceTapestry.com

Thanks David. That actually

Submitted by mgmedia on Tue, 2011-09-06 19:15

Thanks David. That actually solved a top products problem I was having as I couldn't get the WHERE to work with that. I have a problem with getting the counts added to the database for categories though. How do I get it to count clicks on a category?

Thanks

Ben

Hi Ben, As there is no

Submitted by support on Tue, 2011-09-06 20:05

Hi Ben,

As there is no independent table for categories (a category list / index is only ever derived from the products table) where you want to display the counts for a category you would basically use the same code as above but modified to display totalclicks; of course you use whatever (or no) LIMIT as required; e.g.

$sql = "SELECT category,totalclicks AS (SUM(clicks)) FROM `".$config_databaseTablePrefix."products` WHERE category <> '' GROUP BY category ORDER BY totalclicks DESC LIMIT 10";

if (database_querySelect($sql,$rows))

{

  print "<h4>Top Categories</h4>";

  print "<ul>";

  foreach($rows as $row)

  {

    print "<li><a href='".$config_baseHREF."search.php?q=category:".urlencode($row["category"])."'>".$row["category"]."</a> (".$row["totalclicks"].")</li>";

  }

  print "</ul>";

}

...or as a complete page to run inside /admin/

topcat.php

<?php
  require("../includes/common.php");
  require("../includes/widget.php");
  $admin_checkPassword = TRUE;
  require("admin_header.php");
  require("admin_menu.php");
  print "<h2>Top Categories</h2>";
  $sql = "SELECT category,totalclicks AS (SUM(clicks)) FROM `".$config_databaseTablePrefix."products` WHERE category <> '' GROUP BY category ORDER BY totalclicks DESC";
  if (database_querySelect($sql,$rows))
  {
    foreach($rows as $row)
    {
      print "<p><a href='".$config_baseHREF."search.php?q=category:".urlencode($row["category"])."'>".$row["category"]."</a> (".$row["totalclicks"].")</p>";
    }
  }
  require("admin_footer.php");
?>

Cheers,
David.
--
PriceTapestry.com