Hi,
It's been a while, but I'm back to looking at my PT site and giving it an update.
I've been looking at adding something like a Hot Products section, and the closest I can find is https://www.pricetapestry.com/node/4584.
I'm currently using version 15/09A, so not sure whether the link I've found is applicable for that or if there was another that I've just not been able to find. Would love to do more of an update and use v18, but I've done so many things that I can't honestly say what upgrades I've added along with other little mods.
So is this still okay? Or am I better off starting from fresh, using v18 and adding something like this then with up to date coding throughout the installation?
Thanks for that. I decided to make a copy of my site and duplicate the database so that it's using a different suffix. Then any changes I make won't affect my live site. So the new database had an empty products table.
When importing the feed, I get the following error, that just repeats itself over and over.
Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in \includes\database.php on line 38
Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in \includes\database.php on line 43
It is uploading products though, as I can see the categories appearing on the categories page, and inside those are products.
Hi,
If you could enable database debug mode by changing line 6 in config.advanced.php as follows;
$config_databaseDebugMode = TRUE;...then re-import again and in addition to the generic MySQL error you should see the query that caused the error and the actual MySQL error message. If you're not sure from the output where the problem lies let me know what is displayed and I'll check it out further with you...
Cheers,
David.
--
PriceTapestry.com
Hi David,
Done that and I just get the same.
Thanks
Ben
Hello Ben,
Sorry about that - the replacement to includes/admin.php at line 523 was incorrect for 15/09A, please update using:
$sql .= ",deleteme='0'";
$sql2 = "SELECT id FROM `".$config_databaseTablePrefix.$table."`
WHERE name='".database_safe($importRecord["name"])."'
LIMIT 1";
if (database_querySelect($sql2,$result))
{
$search = "INSERT IGNORE INTO `".$config_databaseTablePrefix.$table."`";
$replace = "UPDATE `".$config_databaseTablePrefix.$table."`";
$sql = str_replace($search,$replace,$sql);
$sql .= " WHERE name='".database_safe($importRecord["name"])."'";
}
if (database_queryModify($sql,$insertId))
{
$admin_importProductCount++;
}(corrected above)
Cheers,
David.
--
PriceTapestry.com
Hello Bem,
Here's the mods from that thread updated for your version - I don't normally recommend upgrading heavily modified sites, it's much easier to back port any new features that you wanted from a later distribution than to start over...
Make sure you have a good backup of all files and database before applying the below as it's quite a significant change but should do the trick...
First, create a dbmod.php script to add a deleteme flag (used during import to delete expired products) and a clicks field:
<?phprequire("includes/common.php");
$sql = "ALTER TABLE `".$config_databaseTablePrefix."products`
ADD `deleteme` INT(11) NOT NULL,
ADD `clicks` INT(11) NOT NULL";
database_queryModify($sql,$result);
print "Done.";
?>
Upload and browse to dbmod.php once to add the new fields.
Next, edit includes/admin.php and look for the following code at line 734:
$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);
Then look for the following code at line 726:
$sql = "DELETE FROM `".$config_databaseTablePrefix.$table."` WHERE filename='".database_safe($admin_importFeed["filename"])."'";...and REPLACE with:
$sql = "UPDATE `".$config_databaseTablePrefix.$table."` SET deleteme='1' WHERE filename='".database_safe($admin_importFeed["filename"])."'";And finally in the same file look for the following code at line 523:
if (database_queryModify($sql,$insertId)){
$admin_importProductCount++;
}
...and REPLACE with:
$sql .= ",deleteme='0'";$sql2 = "SELECT id FROM `".$config_databaseTablePrefix.$table."`
WHERE name='".database_safe($importRecord["name"])."'
LIMIT 1";
if (database_querySelect($sql2,$result))
{
$search = "INSERT IGNORE INTO `".$config_databaseTablePrefix.$table."`";
$replace = "UPDATE `".$config_databaseTablePrefix.$table."`";
$sql = str_replace($search,$replace,$sql);
$sql .= " WHERE name='".database_safe($importRecord["name"])."'";
}
if (database_queryModify($sql,$insertId))
{
$admin_importProductCount++;
}
The next step is to update jump.php to record product as well as feed clicks. Look for the following code at line 4:
$sql = "SELECT filename,buy_url FROM `".$config_databaseTablePrefix."products` WHERE id='".database_safe($_GET["id"])."'";...and REPLACE with:
$sql = "UPDATE `".$config_databaseTablePrefix."products` SET clicks=clicks+1 WHERE id='".database_safe($_GET["id"])."'";database_queryModify($sql,$result);
$sql = "SELECT filename,buy_url FROM `".$config_databaseTablePrefix."products` WHERE id='".database_safe($_GET["id"])."'";
ADDITIONAL CHANGE REQUIRED FOR 13/03A TO ACCOMODATE ZERO DOWN-TIME IMPORT:
In includes/import.php look for the following code at line 54:
$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 REPLACE with:
$admin_importAll = FALSE;// DELETED
Then look for the following code at line 80:
$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 REPLACE with:
// DELETEDAnd similarly in includes/cron.php look for the following code at line 71:
$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 REPLACE with just:
$admin_importAll = FALSE;// DELETED
...and finally the following code at line 97:
$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 REPLACE with:
// DELETEDWith that all in place, you can now modify the Featured Products code in index.php to select your top clicked products. For just the 1, look for the following code at line 12:
$sql = "SELECT * FROM `".$config_databaseTablePrefix."featured` ORDER BY sequence";...and REPLACE with:
$sql = "SELECT name,1 AS sequence FROM `".$config_databaseTablePrefix."products` ORDER BY clicks DESC LIMIT 1";Simply change the value after the LIMIT clause for more products. There is a potential performance issue if your site is very large as the ORDER BY clicks requires a full table scan so if this results in a very slow page load time it would be straight forward to create a cron job to run the process nightly and put the name(s) of the top clicked products into a text file use by index.php...
Cheers,
David
--
PriceTapestry.com