You are here:  » Popular Products Modification

Active Forum Topics


Popular Products Modification

Submitted by support on Tue, 2014-11-04 13:24 in

Hi everyone,

I'm just re-documenting the Popular Products modification for the latest distribution as there are numerous older threads covering the same topic so this will help new users find the instructions in one place.

To track popular products by way of page views, first of the all the UPDATE / INSERT modification must be applied so that product records are preserved in the pt_products table and therefore a new custom `views` field can be incremented by product page view and continue to increment without being deleted / reset as feeds are updated / re-imported.

Next, create and run the following dbmod.php script to add the new `views` column to pt_products table, and also index the column:

<?php
  
require("includes/common.php");
  
$sql "ALTER TABLE `".$config_databaseTablePrefix."products` ADD `views` INT(11) NOT NULL";
  
database_queryModify($sql,$result);
  
$sql "CREATE INDEX views ON `".$config_databaseTablePrefix."products` (views)";
  
database_queryModify($sql,$result);
  print 
"Done.";
?>

Now to increment the `views` column per product page view, edit products.php and look for the following code beginning at line 22:

    if ($numRows)
    {

...and REPLACE with:

    if ($numRows)
    {
      $sql = "UPDATE `".$config_databaseTablePrefix."products` SET views = views + 1 WHERE normalised_name = '".database_safe($q)."'";
      database_queryModify($sql,$result);

Finally, wherever within your template you wish to display, for example Top 3 most viewed products, start with the following basic example, which will display a link to the top 3 products as an HTML unordered list:

  $sql = "SELECT DISTINCT(name),normalised_name FROM `".$config_databaseTablePrefix."products` ORDER BY views DESC LIMIT 3";
  if (database_querySelect($sql,$rows))
  {
    print "<ul>";
    foreach($rows as $row)
    {
      print "<li><a href='".tapestry_productHREF($row)."'>".$row["name"]."</a></li>";
    }
    print "</ul>";
  }

Cheers,
David
--
PriceTapestry.com