Could i somehow put a top ten of the most viewed or clicked items on the front page?
Kind Regards,
Anders
Hi Anders,
If you use David's method above you will lose the view count every time you import your feeds. This may or may not be OK with you as if you just import feeds every week then you can have 'Top 10 Products This Week' as the header. Alternatively you could create a separate table that stores the product name and view count and update that from your products.php. This would then have persistent data. However, you can then end up having orphaned products being displayed from your 'popular' table that no longer exist.
Alternatively, you could combine the two methods. This would involve altering the products table and file to have the new 'views' field as David has said. What you could then do is alter the feed import function in admin.php so that before emptying your products table any record with a 'views' value > 0 is copied to, in effect, a temporary table. You would only need id, name and views in this table.
After all the feeds have been imported, loop through the 'temporary table' and update matching product names in the products table with the stored 'views' value.
Does this make sense? I haven't done this myself so can't paste any code but it is on my list of things to do! David, do you reckon this is the best way of persisting this type of data?
Cheers.
Keeop
Looks like I have done this! I think I have too many PT sites on the go and am not spending enough time keeping any changes synchronised. Might have to get SourceSafe or something installed!
Anyway, have a look at this code:
function admin_importClicks()
{
global $config_databaseTablePrefix;
$sql = "UPDATE `".$config_databaseTablePrefix."products` SET clicks='0'";
database_queryModify($sql,$insertId);
$sql = "SELECT * FROM `".$config_databaseTablePrefix."popular` WHERE clicks > '0' GROUP BY name";
if (database_querySelect($sql,$rows))
{
foreach($rows as $clicks)
{
$sql = "UPDATE `".$config_databaseTablePrefix."products` SET clicks='".$clicks["clicks"]."' WHERE name='".database_safe($clicks["name"])."'";
if (database_queryModify($sql,$insertId))
{}
else
{
$sql = "DELETE FROM `".$config_databaseTablePrefix."popular` WHERE name='".$clicks["name"]."'";
database_queryModify($sql,$insertId);
}
}
}
// ******** Remove from Featured while we're here *********
$sql = "SELECT * FROM `".$config_databaseTablePrefix."featured` GROUP BY name";
if (database_querySelect($sql,$rows))
{
foreach($rows as $clicks)
{
$sql = "SELECT name FROM `".$config_databaseTablePrefix."products` WHERE name='".database_safe($clicks["name"])."'";
if (database_querySelect($sql,$insertId))
{}
else
{
$sql = "DELETE FROM `".$config_databaseTablePrefix."featured` WHERE name='".$clicks["name"]."'";
database_queryModify($sql,$insertId);
}
}
}
}It assumes you have a new table called 'popular' which counts views/clicks - whatever you want to use - and a new field 'clicks' in your product table. It also tidies up any orphaned records in your 'featured' table while we're at it.
Dump this function in includes\admin.php and then call it from admin\feeds_import.php after admin_importreviews() so you have something like:
admin_import($filename,$limit,"callback");
print "<p>Backfilling Reviews...</p>";fl();
admin_importReviews();
print "<p>Backfilling Clicks...</p>";fl();
admin_importClicks();
print "<p>Done.</p>";Being as I forgot I even did this and I found it in a project I'm currently working on, I can't gurantee it works as I'm not in a position to test it at the moment so please be careful as records can be deleted by this code.
Cheers.
Keeop
Hi David,
I've tried this mod but a problem arises when there is more then one retailer that has the same product. Do you know if i can use a DISTINCT or GROUP BY clause so only one instance of one product is shown if there is more than one? Thanks
Allan
Hi Allan,
Have a go with:
$sql = "SELECT *,SUM(views) AS totalviews FROM `".$config_databaseTablePrefix."products` GROUP BY name ORDER BY totalviews DESC LIMIT 10";
Cheers,
David.
--
PriceTapestry.com
Hello David,
I show random products on my homepage with this code:
$sql = "SELECT name,1 AS sequence FROM `".$config_databaseTablePrefix."products` ORDER BY RAND() LIMIT 12";
Is it possible to change this in most viewed?
Thank you very much.
Greetings Jan Roel
Hello Jan,
Sure - have a go with;
$sql = "SELECT name,1 AS sequence FROM `".$config_databaseTablePrefix."products` ORDER BY views DESC LIMIT 12";Cheers,
David.
--
PriceTapestry.com
Hello David,
I get this error:
Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in /home/voordeelzoeken/public_html/includes/database.php on line 38
Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in /home/voordeelzoeken/public_html/includes/database.php on line 43
Thanks JR
Hello Jan,
You can double check by temporarily enabling database debug mode by changing line 6 in config.advanced.php as follows;
$config_databaseDebugMode = TRUE;However it sounds like `views` columns has not yet been added to pt_products table so that might be all it is if the above mods from this comment have not yet been applied; however if that's all in place and still not working let me know and I'll check it out further for you...
Cheers,
David.
--
PriceTapestry.com
Hello David
When we Update feed by manual or by cron Job ,
we will lost view count or product popularity on site for that product name ($q)
For that case what we should do ??
Hi,
I more recently posted an alternative implementation that stores meta data e.g. `views` in a separate table so you can leave the import process as it is and keep views data...
Cheers,
David.
--
PriceTapestry.com
Hello David
When I have added views count script huge number of count increases form most of the product
I may be google crowl or any other search engine crowl
Can we add script only on client side where we will get actual product view counts
Hi,
One thing to do would be skip the views increment code if the user-agent looks like a bot / crawler, so in the modified products.php where you have the following code (assuming implementation from node/5662)
$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);...REPLACE with:
if (
(stripos($_SERVER["HTTP_USER_AGENT"],"bot")===FALSE)
&&
(stripos($_SERVER["HTTP_USER_AGENT"],"crawl")===FALSE)
&&
(stripos($_SERVER["HTTP_USER_AGENT"],"index")===FALSE)
)
{
$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);
}Hope this helps!
Cheers,
David.
--
PriceTapestry.com
Thanks David,
Its working fine, Now Getting Actual products views
Hello,
can I present the top 10 products the same as the top products (with image and link info)?
regards
Marco
Hi Marco,
Sure - you can use products table / views as the basis for Featured Products selection (and therefore displayed using html/featured.php) for example;
unset($featured);
$sql = "SELECT name,1 AS sequence FROM `".$config_databaseTablePrefix."products` ORDER BY views DESC LIMIT 8";
if (database_querySelect($sql,$rows))
{
$sqlNames = array();
$sqlCase = "CASE normalised_name";
foreach($rows as $featured)
{
$featured["name"] = tapestry_normalise($featured["name"]);
$sqlNames[] = "'".$featured["name"]."'";
$sqlCase .= " WHEN '".database_safe($featured["name"])."' THEN ".$featured["sequence"];
}
$sqlCase .= " END AS sequence";
$sqlIn = implode(",",$sqlNames);
$sql = "SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, ".$sqlCase." FROM `".$config_databaseTablePrefix."products` WHERE normalised_name IN (".$sqlIn.") GROUP BY normalised_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");
unset($featured);Whilst an index on the `views` column would assist the above query, I don't think that is good idea since views is updated every page view and that would result in an index update every page view.
Instead, if the above is or becomes too slow to be acceptable it would be no problem, for example to add some code to the end of cron.php that automatically set Featured Products to be the top 10 products - just let me know if you wanted to apply that...
Cheers,
David.
--
PriceTapestry.com
Hello
I do not know exactly what you mean.
I think a changing content is good for google & co ... so you always see what is being adapted?
I would like to have listed the TOP 8 supremacy of Featured Product. It did not work with your code.
regards
Marco
Hi Macro,
Did you get an error using the above code? You could try with database debug temporarily enabled at line 6 of config.advanced.php
$config_databaseDebugMode = TRUE;Have you confirmed that `views` is being incremented as expected - one thing you could do is display it on the product page to check for example in html/product.php
<p>Views: <?php print $product_main["views"]; ?></p>Cheers,
David.
--
PriceTapestry.com
Hello
The views work, but where do I have to put the code above to show them before or after the Featured Product?
regards
Marco
Hi Marco,
The code above is basically the same as the main Featured Products code in index.php.
I've just modified the above to add unset($featured); at the end as well as the start so you can insert it before or after the Features Products code in that file.
So for top viewed products before Featured Products, put the code after line 10:
require("html/banner.php");Or for top viewed products after Featured Products, put the code after line 47:
if (isset($featured)) require("html/featured.php");Cheers,
David.
--
PriceTapestry.com
Hello
Now I understand it and it works :-)
Thank you!
Hello Anders,
This is quite easy but does involve making some database changes. The main change is you need to add a "views" field to the products table. The following dbmod.php script will add this field:
<?phprequire("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.";
?>
Then, in products.php, look for the following code beginning at line 8:
if ($q){
...and then INSERT the following code immediately AFTER this point (inside the { bracket):
$sql = "UPDATE `".$config_databaseTablePrefix."products` SET views = views + 1 WHERE name = '".database_safe($q)."'";database_queryModify($sql,$result);
That will update views every time a product page is displayed, so now it is easy to add code to display the top 10 most viewed products anywhere you want (i.e. on index.php with this code):
$sql = "SELECT * FROM `".$config_databaseTablePrefix."products` ORDER BY views DESC LIMIT 10";if (database_querySelect($sql,$products))
{
print "<h4>Top 10 Viewed Products</h4>";
print "<ul>";
foreach($products as $product)
{
if ($config_useRewrite)
{
$href = $config_baseHREF."product/".tapestry_hyphenate($product["name"])."/";
}
else
{
$href = $config_baseHREF."products.php?q=".urlencode($product["name"]);
}
print "<li><a href='".$href."'>".$product["name"]."</a></li>";
}
print "</ul>";
}
Cheers,
David.