Support Forum

Active Forum Topics

Keeping track of new products

Submitted by Harvey on Tue, 2010-02-23 14:21 in Price Tapestry

Hi,

Is there an out of the box way of keeping track of the latest products? Or a filter I can put in a field that puts the timestamp on when it's new?

Would reference the product name, and I only have one merchant.

Thanks :)

Hi Harvey, (Edit: updated

Submitted by support on Tue, 2010-02-23 15:11

Hi Harvey,

(Edit: updated for 13/03A)

I've just posted the modifications required for a very similar scenario (update instead of delete) in this thread; here's the same instructions but relating to the original distribution of Price Tapestry; with the inclusion of a timestamp field that will enable you to select the latest products.

Firstly a method to flag which product records need to be deleted as the DELETE process needs to be moved from before the import to afterwards. To do this, add a "deleteme" field to the `products` table, of type INT(11), and in your case a timestamp field also of type INT(11). You can either do this with your mySQL administration tool; or with the following dbmod.php script - (run once from the Price Tapestry installation folder):

dbmod.php

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

With that in place; the next step is to replace the initial DELETE with an UPDATE query to set deleteme to 1 for all products for the feed being imported. In includes/admin.php, look for the following code on line 535:

    $sql = "DELETE FROM `".$config_databaseTablePrefix."products` WHERE filename='".database_safe($admin_importFeed["filename"])."'";

...and REPLACE with:

    $sql = "UPDATE `".$config_databaseTablePrefix."products` SET deleteme='1' WHERE filename='".database_safe($admin_importFeed["filename"])."'";

Just a few lines later, after the import; look for the following code beginning at line 543:

    $sql = "SELECT COUNT(*) AS productCount FROM `".$config_databaseTablePrefix.$table."` WHERE filename='".database_safe($admin_importFeed["filename"])."'";

    database_querySelect($sql,$rows);

...and REPLACE with:

    $sql = "DELETE FROM `".$config_databaseTablePrefix.$table."` WHERE deleteme='1'";

    database_queryModify($sql,$insertId);

    $sql = "SELECT COUNT(*) AS productCount FROM `".$config_databaseTablePrefix.$table."` WHERE filename='".database_safe($admin_importFeed["filename"])."'";

    database_querySelect($sql,$rows);

Finally, look for the following code higher up the script within the import record handler function, at line 396:

    if (database_queryModify($sql,$insertId))

    {

      $admin_importProductCount++;

    }

...and REPLACE this with:

    global $now;

    if (!$now)

    {

      $now = time();

    }

    $sql .= ",deleteme='0'";

    $sql2 = "SELECT id FROM `".$config_databaseTablePrefix.$table."`

               WHERE original_name='".database_safe($importRecord["original_name"])."' AND filename='".database_safe($admin_importFeed["filename"])."'

               LIMIT 1";

    if (database_querySelect($sql2,$result))

    {

      $search = "INSERT INTO `".$config_databaseTablePrefix.$table."`";

      $replace = "UPDATE `".$config_databaseTablePrefix.$table."`";

      $sql = str_replace($search,$replace,$sql);

      $sql .= " WHERE original_name='".database_safe($importRecord["original_name"])."' AND filename='".database_safe($admin_importFeed["filename"])."'";

    }

    else

    {

      $sql .= ",timestamp='".$now."'";

    }

    if (database_queryModify($sql,$insertId))

    {

      $admin_importProductCount++;

    }

With that in place, you can then select latest products by ordering by timestamp DESC, for example, to select 5 latest products:

<?php
  print "<ul>";
  $sql = "SELECT * FROM `".$config_databaseTablePrefix."products` ORDER BY timestamp DESC LIMIT 5";
  database_querySelect($sql,$rows);
  foreach($rows as $row)
  {
    if ($config_useRewrite)
    {
      $productHREF = $config_baseHREF."product/".tapestry_hyphenate($row["name"]).".html";
    }
    else
    {
      $productHREF = $config_baseHREF."products.php?q=".urlencode($row["name"]);
    }
    print "<li><a href='".$productHREF."'>".$row["name"]."</a></li>";
  }
  print "</ul>";
?>

...or to list ALL new products after the latest import:

<?php
  print "<ul>";
  $sql = "SELECT MAX(timestamp) AS latest FROM `".$config_databaseTablePrefix."products`";
  database_querySelect($sql,$rows);
  $sql = "SELECT * FROM `".$config_databaseTablePrefix."products` WHERE timestamp='".$rows[0]["latest"]."'";
  database_querySelect($sql,$rows);
  foreach($rows as $row)
  {
    if ($config_useRewrite)
    {
      $productHREF = $config_baseHREF."product/".tapestry_hyphenate($row["name"]).".html";
    }
    else
    {
      $productHREF = $config_baseHREF."products.php?q=".urlencode($row["name"]);
    }
    print "<li><a href='".$productHREF."'>".$row["name"]."</a></li>";
  }
  print "</ul>";
?>

Hope this helps!

Cheers,
David.

Hi David, hope all is

Submitted by teezone on Sat, 2011-02-26 05:30

Hi David, hope all is well!

Sorry to reopen an old thread, but I haven't upgraded PT (too many custom mods) - after implementing the above changes, the import halts with the following message:

Cannot Execute:Duplicate entry '93a65c6082350e1a94be0d52b47b8b64' for key 2

In the database, dupe_hash is set to unique, which I believe is correct.

I'm not sure how to handle this, any ideas?

Thanks!
T.

Hi teezone, This sounds like

Submitted by support on Sat, 2011-02-26 10:40

Hi teezone,

This sounds like you have Database Debug Mode enabled (line 6 in config.advanced.php. Duplicate key warnings are normal during import as this is how merchant > product names are kept unique (in order for Price Comparison to work) - so that should be all it is...

If you find it convenient to keep debug mode enabled whilst working on your mods one option would be to add code to disable it just for imported - to do this, look for the following code at around line 368 of includes/admin.php:

global $admin_importCallback;

...and REPLACE with:

    global $admin_importCallback;

    global $config_databaseDebugMode;

    $config_databaseDebugMode = FALSE;

Hope this helps!

Cheers,
David.
--
PriceTapestry.com

Hmm, I didn't think I had

Submitted by teezone on Sat, 2011-02-26 14:33

Hmm, I didn't think I had debug mode on, but still receive the message:

[26-Feb-2011 09:16:19] PHP Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in database.php on line 25
[26-Feb-2011 09:16:19] PHP Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in database.php on line 25 on line 30

Cannot Execute:Duplicate entry 'aa962a6da9c50a35af3c10dae90958bc' for key 2

It happens when I try both @ALL or one xml file only..

Not sure where to look next..

Thanks!

Hi David, as a further

Submitted by teezone on Sat, 2011-02-26 14:43

Hi David, as a further update, I just checked the products table after the error.

I'm guessing the import halts at the first dupe_hash - there are a few hundred products in the table, deleteme flag = 0, and timestamp = 2011.

This might shed more light, I didn't realize it was actually importing products..

Thanks!

Hi teezone, The import won't

Submitted by support on Sat, 2011-02-26 14:51

Hi teezone,

The import won't stop - products will continue to be imported - it's just the one that created a duplicate dupe_hash won't be - that's all. If importing from the /admin/ area, the result of having debug mode output showing is that the script will not be able to redirect back to the admin home page, so a further "Cannot send headers" warning would be the last output generated...

Cheers,
David.
--
PriceTapestry.com

In this case the import

Submitted by teezone on Sat, 2011-02-26 14:57

In this case the import stops as soon as it hits the message:

Cannot Execute:Duplicate entry 'c8fd0a29147264d9272a487758986499' message.

This is the original PT version, could there be something else?

Thanks!

Hi teezone, Check in your

Submitted by support on Sat, 2011-02-26 15:26

Hi teezone,

Check in your includes/database.php for an exit() statement after the call to mysql_error(). It that exists, delete or comment out and that should let the import progress..

Cheers,
David.
--
PriceTapestry.com

Ahh, you're the best! The

Submitted by teezone on Sat, 2011-02-26 15:41

Ahh, you're the best! The import now happily continues.. and I learned something new today :)

It still generates hundreds of these lines during the import:
PHP Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in database.php on line 25
PHP Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in database.php on line 30

Products are importing, but the error_log is going to get out of control.. could it be the format of the new columns..?

Thanks again..

Hi, Could you email me your

Submitted by support on Sat, 2011-02-26 15:45

Hi,

Could you email me your includes/database.php and I'll modify the debug code to make use of the $config_databaseDebugMode variable so that you can make the modification described above to disable debugging during import...

Cheers,
David.
--
PriceTapestry.com