You are here:  » price history

Active Forum Topics


price history

Submitted by davidn on Fri, 2009-09-11 11:37 in

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

Submitted by support on Fri, 2009-09-11 13:48

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.

Submitted by davidn on Fri, 2009-09-11 19:20

One word... wow:-) Perfect support. Thanks very much for idea and showing how to move on.

Submitted by tisbris on Fri, 2010-12-17 06:47

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 31

I'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

Submitted by support on Fri, 2010-12-17 09:30

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

Submitted by tisbris on Fri, 2010-12-17 10:39

Hi David

Almost there - only the merchant columm is still empty - any idea?

thx
//Brian

Submitted by tisbris on Fri, 2010-12-17 17:50

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

Submitted by support on Fri, 2010-12-17 17:52

That's great Brian, let me know if you still need any help...

Cheers,
David.
--
PriceTapestry.com

Submitted by matmatoo on Sun, 2011-10-09 03:34

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

Submitted by support on Mon, 2011-10-10 12:49

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

Submitted by matmatoo on Mon, 2011-10-10 20:07

Hi David,

Thats no problem, the rewrite issue has now been solved.

Cheers
Matt

Submitted by technoarenasol on Wed, 2012-12-26 17:22

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

Submitted by support on Thu, 2012-12-27 10:35

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

Submitted by allanch on Wed, 2013-04-24 11:27

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

Submitted by support on Wed, 2013-04-24 11:31

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

Submitted by allanch on Wed, 2013-04-24 11:37

I'll give that a shot, thanks for the help as always!

Submitted by allanch on Tue, 2013-04-30 07:25

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

Submitted by support on Tue, 2013-04-30 08:28

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

Submitted by allanch on Tue, 2013-04-30 09:02

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

Submitted by support on Tue, 2013-04-30 10:14

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.php

And 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

Submitted by allanch on Tue, 2013-04-30 12:00

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!

Submitted by allanch on Thu, 2013-05-02 07:28

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

Submitted by support on Thu, 2013-05-02 07:50

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

Submitted by allanch on Thu, 2013-05-02 11:07

Thanks David, query and insert works. The only issue is the multiple entries for the same product from the same merchant.

Submitted by support on Thu, 2013-05-02 11:10

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

Submitted by allanch on Thu, 2013-05-02 13:11

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?

Submitted by support on Thu, 2013-05-02 13:22

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

Submitted by allanch on Thu, 2013-05-02 17:36

Thanks David, I didn't realise they were non active products, all seems to be working now!

Submitted by sirmanu on Wed, 2016-09-07 16:08

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.

Submitted by support on Thu, 2016-09-08 08:45

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

Submitted by smartprice24 on Mon, 2017-12-04 11:38

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

Submitted by support on Mon, 2017-12-04 18:12

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