Hi everyone,
In order to maintain an efficient, un-fragmented `products` table in the database, when a feed is imported the process is simply to first DELETE all products associated with the feed (by `filename`), and then to INSERT all products from the feed. This is also the easiest way to ensure that the products table is representative of the latest feeds.
Over the years, many users have had requirements to store additional, non feed sourced information about products, and in order to store this in the `products` table the import process has to be modified to an UPDATE / INSERT / DELETE process, which is less efficient and can, as mentioned above reduce the performance of search due to the fragmented nature of the products table that could result. The benefit of course is that once INSERTed, an `id` value remains with the product until it no longer exists in the associated feed.
One popular modification involving such "meta" data is popular products, typically implemented with the addition of a `views` column to the products table, updated on each product page view, from which popular products can be selected by sorting by views DESC. To implement popular products in a separate "meta data" table, first create the new table using the following code:
<?php
require("includes/common.php");
$sql = "CREATE TABLE `".$config_databaseTablePrefix."productsmeta` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(255) NOT NULL default '',
`views` int(11) NOT NULL default '1',
PRIMARY KEY (`id`),UNIQUE KEY (`name`),KEY (`views`)
) ENGINE=MyISAM DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
";
database_queryModify($sql,$rows);
print "Done.";
?>
Save the above as dbmod.php and upload to the top level folder of your Price Tapestry installation, then browse to /dbmod.php to create the new pt_productsmeta table.
To have an entry for each product created on each product page view, or the `views` column incremented if already exists (this can all be done in a single query using SQL's "ON DUPLICATE KEY" feature), edit products.php and look for the following code around line 62:
$prices["products"] = $rows;...and REPLACE with:
$prices["products"] = $rows;
$sql = "INSERT INTO `".$config_databaseTablePrefix."productsmeta`
SET name='".database_safe($product["products"][0]["name"])."'
ON DUPLICATE KEY UPDATE views = views + 1";
database_queryModify($sql,$result);Popular Products can then be implemented, displayed using the Featured Products HTML module using the following PHP code. Either add to your home page (index.php - in which case omit the PHP tags) or as required within your template, such as within html/header.php or html/footer.php
<?php
unset($featured);
$sql = "SELECT name FROM `".$config_databaseTablePrefix."productsmeta` ORDER BY views DESC LIMIT 3";
if (database_querySelect($sql,$rows))
{
$names = array();
$sequence = 1;
$sqlCase = "CASE name";
foreach($rows as $row)
{
$names[] = "'".database_safe($row["name"])."'";
$sqlCase .= " WHEN '".database_safe($row["name"])."' THEN ".$sequence++;
}
$sqlCase .= " END AS sequence";
$in = implode(",",$names);
$sql = "SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice,
COUNT( id ) AS numMerchants, ".$sqlCase." FROM `".$config_databaseTablePrefix."products`
WHERE name IN (".$in.") GROUP BY name ORDER BY sequence";
database_querySelect($sql,$rows);
$featured["products"] = $rows;
foreach($featured["products"] as $k => $product)
{
$featured["products"][$k]["productHREF"] = tapestry_productHREF($product);
$featured["products"][$k]["reviewHREF"] = tapestry_reviewHREF($product);
}
if (isset($featured)) require("html/featured.php");
}
?>
Cheers!
David
--
PriceTapestry.com
Hi David,
May I ask if this can be done for top searches too?
Cheers
S
Hi Steve,
Top searches is normally implemented in a standalone table anyway (`querylog`) so doesn't have any impact on performance at all.
Cheers,
David.
--
PriceTapestry.com
Great work David!!!
Thank you very much!!