Support Forum

Active Forum Topics

Hot Product

Submitted by martinn2009 on Thu, 2012-04-12 11:47 in Price Tapestry

Hi David,

There is a Featured Product to show on front page (html/featured.php).
Is it possible for me to create/show HOT PRODUCT which is based on most click/ most visitor click.

Best Regards,
Barra

Hello Barra, This can be

Submitted by support on Thu, 2012-04-12 13:39

Hello Barra,

This can be done but because of the way Price Tapestry updates products (by first deleting and then re-importing) it is quite an extensive modification as this process needs to be changed to preserve the existing product record for existing products. It is already documented on the forum for earlier distributions so this would make a good time to update the mod for 12/10B.

First, create a dbmod.php script to add a deleteme flag (used during import to delete expired products) and a clicks field:

<?php
  require("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 515:

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

    database_querySelect($sql,$rows);

...and REPLACE with:

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

    database_queryModify($sql,$insertId);

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

    database_querySelect($sql,$rows);

Then look for the following code at line 507:

    $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"])."'";

And finally in the same file look for the following code at line 386:

    if (database_queryModify($sql,$insertId))

    {

      $admin_importProductCount++;

    }

...and REPLACE with:

    global $now;

    if (!$now)

    {

      $now = time();

    }

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

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

               WHERE name='".database_safe($importRecord["name"])."'

               LIMIT 1";

    if (database_querySelect($sql,$result))

    {

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

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

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

      $sql .= " WHERE name='".database_safe($importRecord["name"])."'";

    }

    else

    {

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

    }

    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"])."'";

With 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 32:

  $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

Hi David, I followed all

Submitted by martinn2009 on Fri, 2012-04-13 14:15

Hi David,

I followed all your directions but unfortunately It turn me to blank page of Featured Product when I changed with the last code.

> With 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 32:

  $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";

Rgds,
Barra

Hi Barra, Could you enable

Submitted by support on Fri, 2012-04-13 14:20

Hi Barra,

Could you enable database debug mode at line 6 of config.advanced.php:

$config_databaseDebugMode = TRUE;

...and then browse to index.php again - if it's a database problem that should reveal the reason; most likely a field missing; in which case with debug mode still enabled re-run dbmod.php and try again; and if that's all fine re-import your smallest feed as that will reveal if the problem exists within the import process.

If you're not sure from any of the database errors displayed (if any) or if not let me know what is displayed and I'll check it out further...

Cheers,
David.
--
PriceTapestry.com

Hi David, I ve got this

Submitted by martinn2009 on Fri, 2012-04-13 14:23

Hi David,

I ve got this message:
[SELECT name,1 AS sequence FROM `pt_products` ORDER BY clicks DESC LIMIT 1][Unknown column 'clicks' in 'order clause']

Rgds,

Hi Martin, That would

Submitted by support on Fri, 2012-04-13 14:26

Hi Martin,

That would indicate that dbmod.php hasn't yet been run - the first stage of the mod is to create this new file:

<?php
  require("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.";
?>

...save as dbmod.php, upload to and browse to once and as long as it says "Done." (with no DB errors displayed) that should do the trick - don't forget to disable database debug mode once up and running...

Cheers,
David.
--
PriceTapestry.com

Hi David, I did create

Submitted by martinn2009 on Fri, 2012-04-13 14:51

Hi David,

I did create dbmod.php and upload into the root.
:(

Rgds,

Hi, Did it display "Done."

Submitted by support on Fri, 2012-04-13 14:56

Hi,

Did it display "Done." or an error when you go to

http://www.yoursite.com/dbmod.php

Cheers,
David.
--
PriceTapestry.com

Hi David, Ohhh I am sorry...

Submitted by martinn2009 on Fri, 2012-04-13 15:01

Hi David,

Ohhh I am sorry... so far I did not realize that I should open file dbmod.php, that is why there is no click column on my pt_product.

Now It is working great, thank you so much

Rgds,
Barra

No problem Barra glad you're

Submitted by support on Fri, 2012-04-13 15:07

No problem Barra glad you're up and running!

You can delete dbmod.php now, it only needs to be run once.

Cheers,
David.
--
PriceTapestry.com

Hi David, What I really need

Submitted by martinn2009 on Fri, 2012-04-13 15:10

Hi David,

What I really need actually to show 2 module in index.php (hot product and featured product in index.php)
but it is fine. I will learn from the code above to make it ;)

Rgds,
Barra

Hi David, Finally I have

Submitted by martinn2009 on Thu, 2012-04-26 16:29

Hi David,

Finally I have made hot product and featured product on my site :) thanks for your direction...
Would you help me to give direction for the dynamics menu to show?
I need to show dynamics menu on left side :)

Rgds,
Barra
http://mindprice.com

hi Barra, Code for a dynamic

Submitted by support on Fri, 2012-04-27 08:40

hi Barra,

Code for a dynamic sidebar Category index is in this thread - for a brand or merchant index simply use exactly the same code replacing "category" with "brand" or "merchant" as required...

Cheers,
David.
--
PriceTapestry.com

Hi David I followed that

Submitted by martinn2009 on Sun, 2012-04-29 08:33

Hi David

I followed that thread.
copy paste php code for dynamic sidebar on user_header_after.php and set $config_useRewrite = TRUE; in config.php
but error msg appear :
[SELECT DISTINCT(category) FROM `products` WHERE category <> '' ORDER BY category][Table 'xxxxxx.products' doesn't exist]

Rgds,
Barra

Hello Barra, My apologies,

Submitted by support on Sun, 2012-04-29 17:02

Hello Barra,

My apologies, there was a typo in the other thread; which I have corrected - this line:

  $sql = "SELECT DISTINCT(category) FROM `".$config_databaseTablePrefx."products` WHERE category <> '' ORDER BY category";

should be:

  $sql = "SELECT DISTINCT(category) FROM `".$config_databaseTablePrefix."products` WHERE category <> '' ORDER BY category";

("i" missing from $config_databaseTablePrefix)

Cheers,
David.
--
PriceTapestry.com

Thank you David. Also my

Submitted by martinn2009 on Tue, 2012-05-01 09:18

Thank you David.

Also my apologies to not check for missing i :)

Barra
http://mindprice.com

Hi David. You mentioned in

Submitted by ka on Wed, 2013-01-23 16:44

Hi David. You mentioned in the first post that you might be adding this to the mod 12/10b I can't see it in there or am I doing something wrong. If not, any plans for a new update?

//K

Hi ka, Ah sorry - what I

Submitted by support on Wed, 2013-01-23 17:14

Hi ka,

Ah sorry - what I meant was I'd update the instructions above for the latest distribution, which is done. The standard method of delete / insert creates the least fragmented database and helps performance so I don't recommend using update / insert unless required - that's all...

Cheers,
David.
--
PriceTapestry.com

Hi David After implementing

Submitted by tisbris on Sat, 2013-04-06 23:17

Hi David

After implementing this mod I got a ton of these errors:

{code saved}

please advise what causing this :o)

//Brian

Hello Brian, It looks like

Submitted by support on Sun, 2013-04-07 08:50

Hello Brian,

It looks like you might have enabled database debug mode perhaps whilst working on this mod, but it is still enabled - as those "errors" are actually just the expected ones that are generated during import as part of the de-duplication method. Once database debug mode is no longer required, if you disable by changing line 6 of config.advanced.php back to:

$config_databaseDebugMode = FALSE;

...all should then work as normal.

Cheers,
David.
--
PriceTapestry.com

Hi David I do have

Submitted by tisbris on Sun, 2013-04-07 11:32

Hi David

I do have this:
$config_databaseDebugMode = FALSE;

But still alot of these erros after each feed import

{code saved}

I have narrowed it dow to includes/admin.php and I have triple checked the REPLACE codes and also started over again - still these errors. If I undo these changes in includes/admin.php from this topic, then everything is working normally.

One thing is different from this topic and in my includes/admin.php

I have this: `".$config_databaseTablePrefix.$table."`
But this topic says this: `".$config_databaseTablePrefix."products`

When these erros occur, then the feed is not complete until I do a new register og import.

I really like this feature, but I need some help here (again) :o)

thx
Brian

Hi Brian, Could you perhaps

Submitted by support on Sun, 2013-04-07 17:20

Hi Brian,

Could you perhaps email me your latest includes/admin.php - it sounds like a couple of mods might have become combined and I'll check it out for you..

Thanks!
David.
--
PriceTapestry.com

Hi David, Will this mod work

Submitted by NiceGuyEddie on Mon, 2014-04-14 18:43

Hi David,

Will this mod work with 13/03A?

Cheers

Hi Eddie, Here's the

Submitted by support on Tue, 2014-04-15 11:34

Hi Eddie,

Here's the instructions updated for 13/03A:

First, create a dbmod.php script to add a deleteme flag (used during import to delete expired products) and a clicks field:

<?php
  require("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 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);

Then look for the following code at line 535:

    $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 396:

    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($sql,$result))

    {

      $search = "INSERT 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:

// DELETED

And 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:

// DELETED

With 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 32:

  $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";

Cheers,
David
--
PriceTapestry.com

Hi David, Thanks for

Submitted by NiceGuyEddie on Tue, 2014-04-15 14:19

Hi David,

Thanks for this.

In the updated instructions where:

ADDITIONAL CHANGE REQUIRED FOR 13/03A TO ACCOMODATE ZERO DOWN-TIME IMPORT:

In includes/import.php look for the following code at line 71:

I can't find this code.

Is this change supposed to be in a different file?

Sorry Eddie, I'd got the

Submitted by support on Tue, 2014-04-15 14:26

Sorry Eddie, I'd got the lines numbers confused between scripts/import.php and scripts/cron.php - corrected above...

Cheers,
David.
--
PriceTapestry.com

David, i try to make this

Submitted by cocota on Sat, 2014-06-14 16:05

David,

i try to make this mod but a problem when i modify admin.php with the last code:

{code saved}

this code give me an error on my admin all pages are broken (white,no error message),try $config_databaseDebugMode = TRUE;
but see nothing...

thanks for help
Cocota

Hello Cocota, My apologies

Submitted by support on Tue, 2014-06-17 10:20

Hello Cocota,

My apologies there was a syntax error, corrected in the original post above. This line:

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

...should be:

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

Cheers,
David.
--
PriceTapestry.com

each problems have a

Submitted by cocota on Tue, 2014-06-17 12:04

each problems have a solution it's a great support

thanks