As both the most efficient way to ensure that the product database is perfectly up to date with feeds, and to minimise fragmentation, when Price Tapestry imports a feed the process is simply to first DELETE all products associated with that feed, and then to carry out the import (INSERT) process.
However, some users like to modify this process so that existing product records are preserved rather than deleted, which enables use of the `id` field as a foreign key to other tables, or to preserve the values of custom fields added to the pt_products table.
To apply this change, first create and run the following dbmod.php script to add a new `deleteme` field to pt_products, used to identify expired products so that they can be deleted after the import process:
<?php
require("includes/common.php");
$sql = "ALTER TABLE `".$config_databaseTablePrefix."products`
ADD `deleteme` INT(11) NOT NULL
";
database_queryModify($sql,$result);
print "Done.";
?>
Next, edit includes/admin.php and look for the following code beginning at line 522:
if (database_queryModify($sql,$insertId))
{
$admin_importProductCount++;
}...and REPLACE with:
$sql .= ",deleteme='0'";
$sql2 = "SELECT id FROM `".$config_databaseTablePrefix."products`
WHERE dupe_hash='".$dupe_hash."'
LIMIT 1";
if (database_querySelect($sql2,$result))
{
$search = "INSERT IGNORE INTO `".$config_databaseTablePrefix."products`";
$replace = "UPDATE `".$config_databaseTablePrefix."products`";
$sql = str_replace($search,$replace,$sql);
$sql .= " WHERE dupe_hash='".$dupe_hash."'";
}
if (database_queryModify($sql,$insertId))
{
$admin_importProductCount++;
}Next look for the following code at line 725:
$sql = "DELETE FROM `".$config_databaseTablePrefix.$table."` 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"])."'";And finally the following code at line 733:
$sql = "SELECT COUNT(*) AS productCount FROM `".$config_databaseTablePrefix.$table."` WHERE filename='".database_safe($admin_importFeed["filename"])."'";...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"])."'";Using this method means that the zero-down time import process cannot be used of course, so this must be disabled. To do this, edit scripts/import.php and scripts/cron.php as follows:
Look for the following code beginning at line 54 (import.php) / line 71 (cron.php)
$admin_importAll = TRUE;
$sql = "DROP TABLE IF EXISTS `".$config_databaseTablePrefix."products_import`";
database_queryModify($sql,$result);
$sql = "CREATE TABLE `".$config_databaseTablePrefix."products_import` LIKE `".$config_databaseTablePrefix."products`";
database_queryModify($sql,$result);
$sql = "ALTER TABLE `".$config_databaseTablePrefix."products_import` DISABLE KEYS";
database_queryModify($sql,$result);And either comment out or DELETE that section. Similarly, look for the following code beginning at line 80 (import.php) / line 97 (cron.php)
$sql = "ALTER TABLE `".$config_databaseTablePrefix."products_import` ENABLE KEYS";
database_queryModify($sql,$result);
$sql = "DROP TABLE `".$config_databaseTablePrefix."products`";
database_queryModify($sql,$result);
$sql = "RENAME TABLE `".$config_databaseTablePrefix."products_import` TO `".$config_databaseTablePrefix."products`";
database_queryModify($sql,$result);..and again, either comment out or DELETE that section of code to complete the modification.