Hi,
I was thinking about keeping the history of product price to show it in a graph on a product-detail page. Do you have any idea how to solve this out? I don't need the specific code, just an idea of steps towards this goal, because I know that PT deletes DB with every import (in my case every 24 hours).
Thanks for any ideas,
David
One word... wow:-) Perfect support. Thanks very much for idea and showing how to move on.
Hi David
I tried to implement this feature, but my new table pt_pricehistory remains empty after import?
this is what the datbase debugger is reporting:
[INSERT INTO `pt_products` SET filename='ride4fun_frempind.xml', name='Edge alu frempind',description='',image_url='',buy_url='http://ride4fun.dk/product.asp?product=661',price='249.00',category='Frempind',brand='Edge',original_name='Edge alu frempind',merchant='ride4fun', search_name='Edgealufrempind', normalised_name='Edge alu frempind', dupe_hash='5025139ac0331bd66a194c41b2e3c19e' ][Duplicate entry '5025139ac0331bd66a194c41b2e3c19e' for key 2][INSERT INTO `pt_products` SET filename='ride4fun_frempind.xml', name='Edge carbon/alu frempind',description='',image_url='',buy_url='http://ride4fun.dk/product.asp?product=1033',price='349.00',category='Frempind',brand='Edge',original_name='Edge carbon/alu frempind',merchant='ride4fun', search_name='Edgecarbon/alufrempind', normalised_name='Edge carbonalu frempind', dupe_hash='9e0723bf86fad28c532ea828cc8dedfa' ][Duplicate entry '9e0723bf86fad28c532ea828cc8dedfa' for key 2][INSERT INTO `pt_products` SET filename='ride4fun_frempind.xml', name='Token Mono-Q frempind MTB / Race',description='',image_url='',buy_url='http://ride4fun.dk/product.asp?product=1810',price='529.00',category='Frempind',brand='Token',original_name='Token Mono-Q frempind MTB / Race',merchant='ride4fun', search_name='TokenMono-QfrempindMTB/Race', normalised_name='Token Mono Q frempind MTB Race', dupe_hash='bc9fa78b06f4ab14aec3cb3e26ce52aa' ][Duplicate entry 'bc9fa78b06f4ab14aec3cb3e26ce52aa' for key 2][INSERT INTO `pt_products` SET filename='ride4fun_frempind.xml', name='Truvative team frempind',description='',image_url='',buy_url='http://ride4fun.dk/product.asp?product=2572',price='199.00',category='Frempind',brand='Truvative',original_name='Truvative team frempind',merchant='ride4fun', search_name='Truvativeteamfrempind', normalised_name='Truvative team frempind', dupe_hash='b368acdd00dc466e3f492e29f43979d9' ][Duplicate entry 'b368acdd00dc466e3f492e29f43979d9' for key 2]
Warning: Cannot modify header information - headers already sent by (output started at /home/www/mtbpriser.dk/pt/includes/database.php:55) in /home/www/mtbpriser.dk/pt/admin/feeds_import.php on line 31I'm using the 12/10A version and the code is implemented like this:
if (database_queryModify($sql,$insertId))
{
$admin_importProductCount++;
}
//http://www.pricetapestry.com/node/3001
$currentPrice = $record[$admin_importFeed["field_price"]];
$previousPrice = 0.00;
$sql = sprintf("SELECT * FROM pt_pricehistory WHERE
merchant='%s'
AND
name='%s'
",
database_safe($admin_importFeed["merchant"]),
database_safe($record[$admin_importFeed["field_name"]]));
if (database_querySelect($sql,$result))
{
$previousPrice = $result[0]["price"];
}
if ($previousPrice <> $currentPrice)
{
$sql = sprintf("INSERT INTO pt_pricehistory SET
merchant='%s',
name='%s',
price='%s',
date='%s'
",
database_safe($admin_importFeed["merchant"]),
database_safe($record[$admin_importFeed["field_name"]]),
database_safe($record[$admin_importFeed["field_price"]]));
time();
database_queryModify($sql,$result);
}
if ($admin_importCallback)
{the sql export of the new table is like this:
-- phpMyAdmin SQL Dump
-- version 3.3.5
-- http://www.phpmyadmin.net
--
-- Vært: -- Genereringstid: 17. 12 2010 kl. 07:42:47
-- Serverversion: 5.0.32
-- PHP-version: 5.2.6-1+lenny9
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
--
-- Database: ``
--
-- --------------------------------------------------------
--
-- Struktur-dump for tabellen `pt_pricehistory`
--
CREATE TABLE IF NOT EXISTS `pt_pricehistory` (
`id` int(11) NOT NULL auto_increment,
`merchant` varchar(255) NOT NULL,
`name` varchar(255) NOT NULL,
`price` decimal(10,2) NOT NULL,
`date` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `merchant` (`merchant`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
--
-- Data dump for tabellen `pt_pricehistory`
--Do you have an idea to what's wrong?
//Brian
Hi Brian,
Later versions of Price Tapestry use $importRecord["field"] in place of $record[$admin_importFeed["field_name"]] and also $merchant in place of $admin_importFeed["merchant"] (since merchant can now be registered as a field) - have a go with:
if (database_queryModify($sql,$insertId))
{
$admin_importProductCount++;
}
//http://www.pricetapestry.com/node/3001
$currentPrice = $importRecord["price"];
$previousPrice = 0.00;
$sql = sprintf("SELECT * FROM pt_pricehistory WHERE
merchant='%s'
AND
name='%s'
",
database_safe($merchant),
database_safe($importRecord["name"]));
if (database_querySelect($sql,$result))
{
$previousPrice = $result[0]["price"];
}
if ($previousPrice <> $currentPrice)
{
$sql = sprintf("INSERT INTO pt_pricehistory SET
merchant='%s',
name='%s',
price='%s',
date='%s'
",
database_safe($merchant),
database_safe($importRecord["name"]),
database_safe($importRecord["price"]),
time());
database_queryModify($sql,$result);
}
if ($admin_importCallback)
{Hope this helps!
Cheers,
David.
--
PriceTapestry.com
Hi David
Almost there - only the merchant columm is still empty - any idea?
thx
//Brian
Hi David
this one is working...
$currentPrice = $importRecord["price"];
$previousPrice = 0.00;
$sql = sprintf("SELECT * FROM `".$config_databaseTablePrefix."pricehistory` WHERE
merchant='%s'
AND
name='%s'
",
database_safe($importRecord["merchant"]),
database_safe($importRecord["name"]));
if (database_querySelect($sql,$result))
{
$previousPrice = $result[0]["price"];
}
if ($previousPrice <> $currentPrice)
{
$sql = sprintf("INSERT INTO `".$config_databaseTablePrefix."pricehistory` SET
merchant='%s',
name='%s',
price='%s',
date='%s'
",
database_safe($importRecord["merchant"]),
database_safe($importRecord["name"]),
database_safe($importRecord["price"]),
time());
database_queryModify($sql,$result);
}//Brian
That's great Brian, let me know if you still need any help...
Cheers,
David.
--
PriceTapestry.com
Hi everyone,
Sorry to dig up a old post, but i'd be interested to have this feature!!
I have the most recent PT release, can anyone give me instructions to include this 'price history' feature on my website on each product page?
Cheers
Matt
Hi Matt,
I'll re-document this for the latest version for you; however bear in mind that the above only concerns the recording of price history not a visual display which the above poster developed independently. It's quite a complex development and only really recommended for very niche sites (a large general site would quickly overwhelm the database) but if you could let me know once the rewrite issues are resolved I'm concerned at advising too many changes to your site all at once; but once you're up and running with re-write let me knw and I'll revisit this for you think it would be appropriate for you site...
Cheers,
David.
--
PriceTapestry.com
Hi David,
Thats no problem, the rewrite issue has now been solved.
Cheers
Matt
technoarenasol
Hi David I added this price graph code into my script.Its working fine when I am use manually import or slow import .but In cron job this function not calling...so how to called this function cron job ?.
Thanx
Amin
Hi Amin,
At the suggested point in includes/admin.php that the above code is added there shouldn't be any distinction between import method but if you'd like to email me me your modified file I'll check it out for you...
Cheers,
David.
--
PriceTapestry.com
Hi David,
I hope all is well. I'm interested in adding this modification. I have a niche site where most products are mapped but some aren't. Do you know how I can modify the pricehistory query so that history is only recorded for products that exist in pt_productsmap only? Thanks
Allan
Hello Allan,
Sure - simply wrap the price history code within the following code / IF statement to make it conditional upon mapped products only:
if (in_array($importRecord["name"],$admin_importProductMappings))
{
// price history code here
}Cheers,
David.
--
PriceTapestry.com
Hi David,
I've implemented the code and it all works. The only issue is duplicate entries. I noticed the duplicates don't exist in pt_products table. Do you know what is the best way of stopping them from being inserted into the price history table? Thanks
Hi Allan,
The `pricehistory` table should be intentionally building up duplicate rows with the same name but only when the price changes, but if you're seeing duplicate rows with the same price that would indicate that something isn't quite right- if you could post the exact code that you added if you're still not sure i'll check it out further for you...
Cheers,
David.
--
PriceTapestry.com
Hi David,
Here's the code I use:
if (in_array($importRecord["name"],$admin_importProductMappings))
{
$sql = sprintf("INSERT INTO pt_pricehistory SET
merchant='%s',
name='%s',
price='%s',
date='%s'
",
database_safe($importRecord["merchant"]),
database_safe($importRecord["name"]),
database_safe($importRecord["price"]),
time());
database_queryModify($sql,$result);
}
As my site is niche I decided to record each price daily with each import. The duplicates appear with different prices which is strange and I think could be two mapped products with similar names. thanks
Hi Allan,
Ah - since the actual price comparison can't be known until the import has completed (I assume you want just lowest price and the corresponding merchant for each product) it would need to be performed as a separate process after the full import.
It's probably best to create a stand alone script to do this, which you can then chain along with your existing import all CRON job (you can separated multiple commands in a single CRON job using semi-colon) for example
cd /path/to/scripts;/usr/bin/php import.php @ALL;/usr/bin/php updatepricehistory.phpAnd here's the code for scripts/updatepricehistory.php, based on your code from above:
<?php
require("../includes/common.php");
$sql1 = "SELECT name,MIN(price) AS minPrice FROM `".$config_databaseTablePrefix."products` WHERE name IN (SELECT name FROM `".$config_databaseTablePrefix."productsmap`)";
database_querySelect($sql1,$rows1);
foreach($rows1 as $row1)
{
$sql2 = "SELECT merchant FROM `".$config_databaseTablePrefix."products` WHERE name='".database_safe($row1["name"])."' ORDER BY price ASC limit 1";
database_querySelect($sql2,$rows2);
$sql3 = sprintf("INSERT INTO `".$config_databaseTablePrefix."pricehistory` SET
merchant='%s',
name='%s',
price='%s',
date='%s'
",
database_safe($rows2[0]["merchant"]),
database_safe($row1["name"]),
database_safe($row1["minPrice"]),
time());
database_queryModify($sql3,$result);
}
print "Done.";
?>
Cheers,
David.
--
PriceTapestry.com
Thanks David, I'm back to using the automation and slow import tools in PT admin as its easier for me as I don't have that many feeds. I will add this to admin section and test it out. Thanks for all your help as always!
Morning David, I implemented the script above but only one product is inserted when the query has run. I did a test and removed the minPrice to:
$sql1 = "SELECT name FROM `".$config_databaseTablePrefix."products` WHERE name IN (SELECT name FROM `".$config_databaseTablePrefix."productsmap`)";
This time round all products that were matched were inserted but obviously the price wasn't there. I've tried a few other alterations but can't get the price and insert to work at the same time. Any help is appreciated. Thanks
Sorry Allan, price shouldn't have been in the initial query - have a go with:
<?php
require("../includes/common.php");
$sql1 = "SELECT name FROM `".$config_databaseTablePrefix."products` WHERE name IN (SELECT name FROM `".$config_databaseTablePrefix."productsmap`)";
database_querySelect($sql1,$rows1);
foreach($rows1 as $row1)
{
$sql2 = "SELECT merchant,price FROM `".$config_databaseTablePrefix."products` WHERE name='".database_safe($row1["name"])."' ORDER BY price ASC limit 1";
database_querySelect($sql2,$rows2);
$sql3 = sprintf("INSERT INTO `".$config_databaseTablePrefix."pricehistory` SET
merchant='%s',
name='%s',
price='%s',
date='%s'
",
database_safe($rows2[0]["merchant"]),
database_safe($row1["name"]),
database_safe($rows2[0]["price"]),
time());
database_queryModify($sql3,$result);
}
print "Done.";
?>
Cheers,
David.
--
PriceTapestry.com
Thanks David, query and insert works. The only issue is the multiple entries for the same product from the same merchant.
Hello Allan,
In fact, the first query can pull directly from the `productsmap` table now, which will solve the duplicate case, REPLACE with just:
$sql1 = "SELECT name FROM `".$config_databaseTablePrefix."productsmap`";Cheers,
David.
--
PriceTapestry.com
Cool, that's sorted the duplicate issue. The only thing is records with no merchant and price = 0.00. I was thinking about aphp if statement, would that be the most efficient way or can this be done in SQL?
Hi Allan,
Not currently active products would return no result from $sql2 - have a go with:
<?php
require("../includes/common.php");
$sql1 = "SELECT name FROM `".$config_databaseTablePrefix."productsmap`";
database_querySelect($sql1,$rows1);
foreach($rows1 as $row1)
{
$sql2 = "SELECT merchant,price FROM `".$config_databaseTablePrefix."products` WHERE name='".database_safe($row1["name"])."' ORDER BY price ASC limit 1";
if (database_querySelect($sql2,$rows2))
{
$sql3 = sprintf("INSERT INTO `".$config_databaseTablePrefix."pricehistory` SET
merchant='%s',
name='%s',
price='%s',
date='%s'
",
database_safe($rows2[0]["merchant"]),
database_safe($row1["name"]),
database_safe($rows2[0]["price"]),
time());
database_queryModify($sql3,$result);
}
}
print "Done.";
?>
Cheers,
David.
--
PriceTapestry.com
Thanks David, I didn't realise they were non active products, all seems to be working now!
Hi David.
Is this code valid for last distribution? Also, I think you it would be more accuracy checking for same URL rather than name, so we avoid duplicates.
Hello sirmanu and welcome to the forum!
Above is still valid, but early posts in this thread refer to a much earlier distribution of Price Tapestry, so have a good read through before implementing anything - updated code that is current can be found in later posts - let me know if you're not sure of anything of course.
(merchant+product name) is the source of the de-duplication key on the products table so would be fine to use - and actually forms the URL.
Cheers,
David.
--
PriceTapestry.com
Hi David.
I tried to add to PT, the latest version, the history of product prices.
I have proceeded with the creation of the table and the code for recall data values (price, merchant, data).
I have difficulty, however, to create data loop for each price change for same product. The following code shows only the latest updated value/price.
<?php
$sql11 = "SELECT id,merchant,name,price,date FROM `".$config_databaseTablePrefix."pricehistory`";
database_querySelect($sql11,$rows);
foreach($rows as $priceh);
$timestamp = $priceh['date'];
?>
<p class='font-italic' style='font-size:10px;'>
<?php
if ($priceh["name"] == $product_main["name"]) {
print translate("NEW PRICE ");
print $priceh["merchant"]."\n";
print $priceh["price"]."\n";
print $priceh['name']."\n";
print gmdate("d m Y H:i:s", $timestamp). "<br />";
}
else
{
print translate("PRICE NO CHANGE");
}
?>
</p>I tried to use foreach, but without the right results.
Do you have any solution / advice to offer?
Many thank's. Giuseppe
Hello Giuseppe,
You can use a WHERE clause in the initial query to restrict the results to $product_main["name"], and "ORDER BY `date` DESC" will show the price history in order of most recent change first. In the code you posted the ";" would terminate the foreach loop immediately - it should be followed by a curly brackets enclosed code block to loop over $rows - also note use of > 1 to either show the price history, or "PRICE NO CHANGE" - have a go with something like;
<?php
$sql11 = "SELECT `id`,`merchant`,`name`,`price`,`date` FROM `".$config_databaseTablePrefix."pricehistory` WHERE name='".database_safe($product_main["name"])."' ORDER BY `date` DESC";
if (database_querySelect($sql11,$rows) > 1)
{
foreach($rows as $priceh)
{
print "<p>";
print translate("NEW PRICE ")." - ";
print $priceh["merchant"]." - ";
print $priceh["price"]." - ";
print $priceh['name']." - ";
print gmdate("d m Y H:i:s",$priceh['date']);
print "</p>";
}
}
else
{
print translate("PRICE NO CHANGE");
}
?>
Hope this helps!
Cheers,
David.
--
PriceTapestry.com
Hi David,
This code works but inserts a new set of data regardless of whether the price has changed or not. Am I right in thinking that if the price stays the same no additional entries should be made?
Thanks,
Simon
Hi Simon,
That is correct for the examples above but would be an easy modification to only create an entry if the price has changed. Can you post the code you are using from the above for reference and I'll advise how to modify accordingly...
Cheers,
David.
--
PriceTapestry.com
So something like this?
<?php
require("../includes/common.php");
$sql1 = "SELECT name FROM `".$config_databaseTablePrefix."productsmap`";
database_querySelect($sql1,$rows1);
foreach($rows1 as $row1)
{
$sql2 = "SELECT merchant,price FROM `".$config_databaseTablePrefix."products` WHERE name='".database_safe($row1["name"])."' ORDER BY price ASC limit 1";
if (database_querySelect($sql2,$rows2))
{
foreach($rows2 as $row2)
{
$currentPrice = $row2["price"];
}
}
$sql3 = "SELECT price FROM `".$config_databaseTablePrefix."price_history` WHERE name='".database_safe($row1["name"])."' ORDER BY date DESC limit 1";
if (database_querySelect($sql3,$rows3))
{
foreach($rows3 as $row3)
{
$previousPrice = $row3["price"];
}
}
if ($previousPrice <> $currentPrice)
{
$sql3 = sprintf("INSERT INTO `".$config_databaseTablePrefix."price_history` SET
merchant='%s',
name='%s',
price='%s',
date='%s'
",
database_safe($rows2[0]["merchant"]),
database_safe($row1["name"]),
database_safe($rows2[0]["price"]),
time());
database_queryModify($sql3,$result);
}
}
print "Done.";
?>
Thanks,
Simon
Looks pretty much spot on, Simon!
If not working as expected let me know and I'll check it out further with you...
Cheers,
David.
--
PriceTapestry.com
Hi David, it's working for me thanks.
Do you have any suggestions for the actual graph itself. I've been looking at a few php scripts and they all seem pretty similar in what they do.
charts.js is looking like a likely contender. Any advice would be appreciated.
Thanks Simon
Hi Simon,
I've never used anything of that nature but there is also the Google Chart API that might be of interest (although I've never used that either..!)
https://developers.google.com/chart
Cheers,
David.
--
PriceTapestry.com
Hi David, I figure out how to make price history for my site thanks to your examples in this thread and its working great. But dont know how to keep last 10 dates and delete older per product name, just to keep mysql clean :) I have everything set, using chartjs for price history. I am putting product normalised_name inside price history name because chartjs wont show anything if the product name contains special characters :)
Thanks :)
Ok, I am having something like this and seems its working:
require("../includes/common.php");
$sql1 = "SELECT name, COUNT(id) FROM `".$config_databaseTablePrefix."pricehistory` GROUP BY name HAVING COUNT(id) > 10";
database_querySelect($sql1,$rows1);
foreach($rows1 as $row1)
{
$sql2 = sprintf("DELETE FROM `".$config_databaseTablePrefix."pricehistory` WHERE name = '%s' AND id NOT IN (SELECT id FROM (SELECT id FROM `".$config_databaseTablePrefix."pricehistory` WHERE name = '%s' ORDER BY date DESC LIMIT 10) AS deletehistory)", database_safe($row1["name"]), database_safe($row1["name"]));
database_queryModify($sql2,$result);
}
print "Price history deletion: DONE.";What do you think? Do you have better or more optimised solution?
I have 2 of database_safe($row1["name"]) and dont know if I could use 1 and dont know if this is right way to do this :) Dont have lot of knowledge about php and mysql :)
Hi David,
The most straight forward way would be to store price history per merchant per product; however it could become quite large quite quickly - but bear in mind that you only need to record a price change - if there's no change there's probably no need to add a new history row - simply record the price and date.
So the fields in "pricehistory" might look like
id INT(11)
merchant VARCHAR(255)
name VARCHAR(255)
price DECIMAL(10,2)
date INT(11)
(I prefer to store dates as a UNIX time value as an INT rather than use the nateive date/time data type. The UNIX time value is returned by PHP's time() function, and can be easily displayed as required using the date() function...)
Then, the trick would be to INSERT price changes at import time rather just before deleting and re-inserting.
The code would go within the admin__importRecordHandler function in includes/admin.php; perhaps just after the main product insert, or in other words immediately BEFORE the following line:
if ($admin_importCallback)The logic would be something like:
$currentPrice = $record[$admin_importFeed["field_price"]];$previousPrice = 0.00;
$sql = sprintf("SELECT * FROM pricehistory WHERE
merchant='%s'
AND
name='%s'
",
database_safe($admin_importFeed["merchant"]),
database_safe($record[$admin_importFeed["field_name"]]));
if (database_querySelect($sql,$result))
{
$previousPrice = $result[0]["price"];
}
if ($previousPrice <> $currentPrice)
{
$sql = sprintf("INSERT INTO pricehistory SET
merchant='%s',
name='%s',
price='%s',
date='%s'
",
database_safe($admin_importFeed["merchant"]),
database_safe($record[$admin_importFeed["field_name"]]),
database_safe($record[$admin_importFeed["field_price"]]),
time());
database_queryModify($sql,$result);
}
Then of course it's just a case of SELECT FROM pricehistory WHERE name='$q' on the product page (products.php) and use the data to display the price history however you require...
Cheers,
David.