You are here:  » Automatic Product Mapping by Unique ID

Support Forum



Automatic Product Mapping by Unique ID

Submitted by support on Mon, 2013-04-15 09:20 in

Hi everyone,

As you will know, Price Tapestry compares by exact matches between product names from different merchants. This has obvious SEO benefits, as it enables clean /product/Product-Name.html URLs that do not require any additional ID values to be included in the URL. Where merchants do not use identical product names, the script includes the Product Mapping feature, which lets you easily map the same product from different merchants to your preferred "master" product name.

Traditionally; affiliate data feeds have rarely included unique product ID fields, such as EAN (European Article Number) that are relevant across merchants; but more recently such values have become more readily available so I have created a method for performing an automatic product mapping by a unique product ID field as a post-import process operating on the entire product database.

Note that implementing it this way preserves the SEO benefit of having a single, clean product URL; and is much more efficient than attempting to re-query the database at various points in order to achieve the same.

Step 1 - Add the unique ID field to your database

Following the standard instructions for adding a custom field to your Price Tapestry database, add an appropriate unique ID fieldname, such as "ean" or "sku", and then re-register / import your feeds to populate the new field in the database.

Step 2 - Add a new config variable declaring the name of your unique ID field

To do this, edit config.advanced.php, and add the following code to the end of the file, just before the closing PHP tag - replacing the value with the name of your unique ID field as added in Step 1:

  $config_uidField = "ean";

Step 3 - Install the scripts, test and set-up as a CRON job

Download ProductMappingByUID.zip, extract the files to your local computer and upload to your Price Tapestry installation as

scripts/uiddbmod.php
scripts/uidmap.php

After uploading, browse to scripts/uiddbmod.php once to create the intermediate table used by the mapping process, and to add an index to your unique ID field on the products table. This file can then be deleted as it is no longer required.

Finally, to test the process; immediately after a full import of all products run scripts/uidmap.php. This can be run from the browser, but ideally should be run from the command line as it may take a few minutes to complete on large databases; for example, from a command prompt:

cd path/to/scripts/
php uidmap.php

If everything is working as expected, uidmap.php can be included in your CRON job. In most cases, multiple commands can be included in the same CRON job using semi-colon separation, for example, if your existing command is:

cd /home/example/htdocs/scripts/;/usr/bin/php cron.php

...then to include uidmap.php, use:

cd /home/example/htdocs/scripts/;/usr/bin/php cron.php;/usr/bin/php uidmap.php

Cheers!
David
--
PriceTapestry.com

Submitted by tommo101 on Sat, 2014-10-18 14:23

Hi there David, I'm having a little issue with using this script. When I try to use it through command I get this error:

PHP Fatal error: require(): Failed opening required '../includes/common.php'

Would this be a simple case of using the full path instead? (probably answering my own question on that one.)

It works fine through browser, albeit it normally times out, hence me wanting to do it from command.

My main thing going on though, is that a lot of the EANs that I am using are using for example 000000000000000 or 0 or empty, which is meaning a few products have really large comparisons, one with 169,000, which is causing an error.

Is there a way to stop this from happening, without having to delete those products? I'm using the latest release. I've looked a few threads, but they seemed to be for previous versions and the code replacements for them, didn't work.

Thanks in advance for any assistance. :)

Submitted by support on Mon, 2014-10-20 11:24

Hello Tommo,

uidmap.php needs to be run from the /scripts/ folder rather than the top level - that should be all it is...

Cheers,
David.
--
PriceTapestry.com

Submitted by Rocky on Wed, 2014-10-29 04:41

Hi David,

I'm quite new to all of this so sorry if I'm asking such newbie questions.

I'm not sure how to go about step 3.

"browse to scripts/uiddbmod.php "

How do I browse to a php file in my browser? Do I just do it via ftp (which is what I did)?

"run scripts/uidmap.php. This can be run from the browser, but ideally should be run from the command line "

How do I do this?

As you may know through our email exchange, I've installed it in {link saved}

Thanks again,
Rocky

Submitted by support on Wed, 2014-10-29 09:05

Hello Rocky, and welcome to the forum!

By browse to the script, this means just to go to the page as if you were viewing it directly from within your web browser - you'd have to enter the page name manually as there is no link to the script from your site of course, so if you just pop the following into your browser address bar:

http://yourdomain.org/pt/scripts/uidmap.php

...the script will then run and display progress followed by "Done." if completed successfully.

Cheers,
David.
--
PriceTapestry.com

Submitted by Ch3W on Sun, 2014-11-16 16:59

Hi there, hope you're well...

Everything works fine for me, except when I try to run uidmap.php from command line I get the following (it works fine from browser but will need it adding to cron as DB is about to get big):

(uiserver):*****:~/****/scripts > php uidmap.php
X-Powered-By: PHP/4.4.9
Content-Type: text/plain;charset=utf-8

Working...

Fatal error: Call to undefined function: date_default_timezone_set() in /homepages/******/includes/common.php on line 27
(uiserver):******:~/******/scripts >

Submitted by support on Mon, 2014-11-17 13:44

Hi Matt,

It looks like the command line version of PHP on your server is older than the version running as an Apache module. No problem - if you edit includes/common.php and look for the following code at line 27:

  date_default_timezone_set($config_timezone);

...and REPLACE with:

  if (function_exists("date_default_timezone_set")) date_default_timezone_set($config_timezone);

...you should then be fine with uidmap.php under both versions.

Cheers,
David.
--
PriceTapestry.com

Submitted by chrisst1 on Tue, 2014-11-18 20:34

Hi David

A couple of questions.

Will using this alter existing mapped products?

Once $config_uidField has been set before dbmod and table install can we change the setting after the install ie from ean to isbn without running dbmod again?

Is the uidfix table required after setup or does it act as a temp storage?

Thanks

cHRIS

Submitted by support on Wed, 2014-11-19 09:41

Hi Chris,

The process could affect the names of existing Product Mapping entries but these can be easily excluded. In uidmap.php look for the code that generates the SQL to select the names to be processed at line 44:

$sql2 = "SELECT SQL_CALC_FOUND_ROWS name FROM `".$config_databaseTablePrefix."products` WHERE ".$config_uidField."='".database_safe($product[$config_uidField])."' ORDER BY name LIMIT 1";

...and REPLACE with:

$sql2 = "SELECT SQL_CALC_FOUND_ROWS name FROM `".$config_databaseTablePrefix."products` WHERE ".$config_uidField."='".database_safe($product[$config_uidField])."' AND name NOT IN (SELECT name FROM `".$config_databaseTablePrefix."productsmap`) ORDER BY name LIMIT 1";

You can change $config_uidField at any time without having to re-run the dbmod script and the uidfix table is required as you are correct - it is used as temporary storage during the process.

Hope this helps!

Cheers,
David.
--
PriceTapestry.com

Submitted by niajahi on Sun, 2014-11-23 16:48

Hi David, I'm getting a 'Deprecated: mysql_connect()' on these two scripts as I'm using the latest PHP/MySQL version. Could you help fix this please?

Thanks,

Len

Submitted by support on Sun, 2014-11-23 16:58

Hello Len and welcome to the forum!

The current download of uidmap.php has been available for some time now so is still using the original mysql_ library but first thing tomorrow I will update it to mysqli_

Deprecated warnings cannot always be disabled but in most cases you should be able to suppress the warning by adding the following code to the very top of the script (immediately after the opening PHP tag)...

  ini_set('display_errors','off');

I'll update this thread tomorrow as soon as the download has been updated.

Cheers,
David.
--
PriceTapestry.com

Submitted by support on Mon, 2014-11-24 13:44

Hello Len,

The scripts in ProductMappingByUID.zip have now been updated the mysqli_ library functions which will remove the deprecated warning.

Cheers,
David.
--
PriceTapestry.com

Submitted by niajahi on Mon, 2014-11-24 16:33

Fantastic!

Thanks David.

Submitted by chrisst1 on Mon, 2014-11-24 16:35

Hi David

Whould I be ok to run this at the end of my cron.php after the call to ping.php as I commented here http://www.pricetapestry.com/node/5094.

Chris

Submitted by support on Mon, 2014-11-24 16:49

Hello Chris,

Absolutely - but since uidmap.php may change the product page URL of some products it would be best to run uidmap.php before ping.php. Make the same change to uidmap.php as described to ping.php in other words where you have the following code at line 8:

  require("../includes/common.php");

...REPLACE with:

  require_once("../includes/common.php");

And then at the end of scripts/cron.php:

  require("uidmap.php");
  require("ping.php");

Cheers,
David.
--
PriceTapestry.com

Submitted by davidre on Mon, 2014-12-22 20:59

I'm having problems with this. I've added the 'ean' field to the database and updated the config.advanced.php file with
$config_fieldSet["ean"] = "ean";
and the
$config_uidField = "ean";

When I try to register the feed the select all the required and optional fields (which now includes the ean), but when I register the feed management page doesn't update. It will not let me import the data.

Submitted by support on Tue, 2014-12-23 12:41

Hi David,

That does sounds like the dbmod hasn't completely applied properly - `ean` must be added to the pt_products table and `field_ean` to the pt_feeds table - if you run the following again, from the top level folder of your Price Tapestry installation that should be all it is (and then re-register) but if still no joy let me know and I'll check it out further with you...

<?php
  
require("includes/common.php");
  
$sql "ALTER TABLE `".$config_databaseTablePrefix."feeds`
            ADD `field_ean` VARCHAR(255) NOT NULL"
;
  
database_queryModify($sql,$result);
  
$sql "ALTER TABLE `".$config_databaseTablePrefix."products`
            ADD `ean` VARCHAR(255) NOT NULL"
;
  
database_queryModify($sql,$result);
  print 
"Done.";
?>

Hope this helps!

Cheers,
David.
--
PriceTapestry.com

Submitted by erv on Sun, 2015-03-08 15:14

Hi!

how would this affect an existing installation? from how i understand the code, the selection of the new "master-name" is different from the usual PT-way, and it would therefore replace the majority of the existing urls, which could be a problem.

what would be a way to implement this useful functionality and keep the new master-urls as similar to the existing ones as possible?

thanks
erv

Submitted by support on Mon, 2015-03-09 08:17

Hello erv,

The uidmap.php script uses an ORDER BY clause to ensure that the alphabetically first product name for all variations is the one that is used as the "Master" product name, so what you could do is implement the solution described in this thread which covers exactly the same scenario whereby a user wanted to work on Product Mapping but not lose the traffic being driven to the existing product pages...

Cheers,
David.
--
PriceTapestry.com

Submitted by Ch3W on Mon, 2015-07-13 22:50

Hi there,

Sorry for the delay in replying here. Thanks for helping above, but after changing as per your advice I now get:

php uidmap.php
X-Powered-By: PHP/4.4.9
Content-Type: text/plain;charset=utf-8

Working...

Fatal error: Call to undefined function: mysql_set_charset() in {code saved}/scripts/uidmap.php on line 14

Submitted by support on Tue, 2015-07-14 08:44

Hi,

mysql_set_charset() is actually a PHP > 5.2.3 function but it can be replaced with the query equivalent. In uidmap.php look for the following code at line 14:

  mysql_set_charset("utf8",$link1);

...and REPLACE with:

  mysql_query("SET CHARACTER SET utf8",$link1);

And similarly at line 22 for $link2:

  mysql_set_charset("utf8",$link2);

...REPLACE with:

  mysql_query("SET CHARACTER SET utf8",$link2);

Cheers,
David.
--
PriceTapestry.com

Submitted by Kiwialec on Tue, 2016-03-08 17:49

uidmap.php takes a long time on my server (in command line) - something like 1000 products processed in 20 mins. The connection to the mysql database is dying after about 20 minutes (looks like it might be timing out or being killed by my host).

Is there a way to paginate the processing of phase 1 so that it can resume later? Is it possible to first load all of the phase 1 uid's into a normal PHP array so that if the mysql server dies, the script can just create a new connection?

The errors I get after a while are:

...
Phase 1:PRODUCT NAME (2)
Phase 1:PRODUCT NAME (2)
<br />
<b>Warning</b>: Empty row packet body in <b>/var/sites/xxxx/public_html/scripts/uidmap.php</b> on line <b>28</b><br />
<br />
<b>Warning</b>: mysqli_real_query(): MySQL server has gone away in <b>/var/sites/xxxx/public_html/scripts/uidmap.php</b> on line <b>61</b><br />
<br />
<b>Warning</b>: mysqli_real_query(): Error reading result set's header in <b>/var/sites/xxxx/public_html/scripts/uidmap.php</b> on line <b>61</b><br />
Done.

Thanks,
Alec

Submitted by support on Wed, 2016-03-09 09:43

Hello Alec, and welcome to the forum!

"MySQL server has gone away" is a strange error that basically occurs when PHP believes that it is still connected to the database but in fact it is not.

Sure - uidmap.php can be modified as you describe by loading the results of the initial query into an array, and then the MySQL connection checked on each iteration using mysqli_ping(), and re-created if necessary. To do this, look for the following code at line 28:

    while($product = mysqli_fetch_assoc($result1))
    {

...and REPLACE with:

    $products = array();
    while($product = mysqli_fetch_assoc($result1))
    {
      $products[] = $product;
    }
    foreach($products as $product)
    {
      if (!mysqli_ping($link2))
      {
        $link2 = mysqli_connect($config_databaseServer,$config_databaseUsername,$config_databasePassword,$config_databaseName);
      }

Hope this helps,

Cheers,
David.
--
PriceTapestry.com

Submitted by haburi on Fri, 2016-06-17 07:12

Is it possible to map products directly by upc when importing first time in the database?

Submitted by support on Fri, 2016-06-17 07:57

Hello haburi and welcome to the forum!

Theoretically this would be straight forward to do - a permanent version of the temporary uidfix table can be established at import time and cross referenced for each product being imported to check for a mapping. It would be important for the table to remain in place so that the same product name is used for a given upc on subsequent imports (so that product page URLs don't change) and this would need some tie-in with Product Mapping to enable overriding of the auto-mapped name. I'll run some tests on my development server to check feasibility and will certainly look to include this "out of the box" in the next distribution...

Cheers,
David.
--
PriceTapestry.com

Submitted by haburi on Mon, 2016-06-20 08:04

Hi David,

Thank you for your fast answer. Looking forward for the outcome, hope it works as it would provide a better way to get a large number of products correctly mapped.

Submitted by richard on Mon, 2016-07-18 18:20

Hi David,

I'm just moving to the phase where I wish to use Cron to update product feeds now that I am nearly ok with modifications.

I'm getting the error message "Call to undefined function: date_default_timezone_set()" but when I follow the instructions above and change common.php line 27 to "if (function_exists("date_default_timezone_set")) date_default_timezone_set($config_timezone);"

I then get "Call to undefined function: mysqli_connect() in /homepages/1/PT/scripts/uidmap.php on line 12"

I am running PT1509 on same server as older PT installations using automated cron job. Installed php is v5.5.

Any ideas what I could do please?

Many thanks again.

Best regards,

Richard

Submitted by support on Tue, 2016-07-19 08:43

Hello Richard,

That sounds like the MySQL libraries are not installed in the command line version of PHP on your server. Most package managers synchronise installation of both the command line and Apache module build of PHP but that looks like it's not the case here. If you have root access, installation would normally be one of;

apt-get install php5-mysql

or
yum install php5-mysql

...and this would install the MySQL libraries for both command line (cli) and PHP as an Apache module (if installed). If you don't have root access you would need to contact your hosting support, let them know the error message "Call to undefined function: mysqli_connect()" and they should be able to update PHP for you as required...

Cheers,
David.
--
PriceTapestry.com

Submitted by richard on Tue, 2016-07-19 09:55

Hi David,

Many thanks. I have spoken with 1&1. I can confirm I am running php v5.5 and mysql is 5.1 (I cannot alter mysql version)

Just to confirm the steps before I do it!

1. Create FILE with command "apt-get install php5-mysql"
2. Using Putty Create job 00 * * * * lynx -dump FILE

Is this correct?

Apologies for all these questions.

Best regards

Richard

Submitted by support on Tue, 2016-07-19 10:52

Hi Richard,

If you can access your server using PuTTY there wouldn't be any need to create a CRON job but you would need to be root or at least have your username as a sudo'er which may be the case; try

sudo apt-get install php5-mysql

(if prompted for a password, use the same as used to login with your username)

If still no joy I'm afraid it would need to be resolved by your host - having MySQL installed does not necessarily mean that php5-mysql is installed which is what appears to be missing but it's very strange that this should be the case - hopefully they can get it installed for you but let me know if I can be of any further assistance of course;

Cheers,
David.
--
PriceTapestry.com

Submitted by richard on Tue, 2016-07-19 11:02

Many thanks David.

No joy so will be in hands of 1&1

Submitted by richard on Mon, 2016-08-01 19:07

Hi David,

Just to let you know that on a managed server I am unable to have root access so I am unable to update as suggested.

The GOOD NEWS is that I can run Option 2 /usr/bin/wget for the cron job :)

Best regards,

Richard

Submitted by richard on Mon, 2016-09-05 18:30

Hi David

I'm doing some more sense checking on data and discovered that one of feeds has the following, which is clearly wrong!

<ean> the sensor adjusts the exact water consumption to the wash</ean>

This creates some very misleading price comparisons as unrelated products can be mapped together.

What would you do to avoid these type of issues?

Best regards,

Richard

Submitted by support on Tue, 2016-09-06 10:18

Hi Richard,

You could add a new "Check EAN" filter - append the following code to includes/filter.php, just before the closing PHP tag:

  /*************************************************/
  /* checkEAN */
  /*************************************************/
  $filter_names["checkEAN"] = "Check EAN";
  function filter_checkEANConfigure($filter_data)
  {
    print "<p>There are no additional configuration parameters for this filter.</p>";
  }
  function filter_checkEANValidate($filter_data)
  {
  }
  function filter_checkEANExec($filter_data,$text)
  {
    if (!is_numeric($text)) return "";
    return $text;
  }

...and then add Check EAN as a Global Filter to the EAN field. This will simply clear the value if not numeric - I would try this in the first instance, but could be extended to check length, and even the check digits if necessary but this would be a good starting point...

Cheers,
David.
--
PriceTapestry.com

Submitted by richard on Tue, 2016-09-06 10:35

Great, nice simple but effective solution :)

Works well

Thank you.

Best regards

Richard

Submitted by sirmanu on Thu, 2016-09-15 13:18

Hi David.
I have two merchants which have this structure:

S,M,L,XL,2XL
40553391224XX,4055339XX2424,40553XX122448,4055339XX2578,4XX5339125296

Does the script consider the commas in the field_ean?

Submitted by support on Fri, 2016-09-16 08:11

Hi,

If you use an Explode filter against the EAN field, with "," (comma) as the Explode Character or String and 0 (zero) as the Return Index that will extract just the first EAN value - if it is consistent between merchants (as is often the case where they use the same wholesaler / supplier meta data) that should do the trick.

Cheers,
David.
--
PriceTapestry.com

Submitted by sirmanu on Wed, 2016-11-16 18:44

Hi.

What do you think about modifying cron.php and uidmap.php to make the changes to pt_products_import and then:

"$sql = "RENAME TABLE `".$config_databaseTablePrefix."products_import` TO `".$config_databaseTablePrefix."products`";"

For large databases, uidmap.php takes an hour so these changes would make the process invisible to the end user.

Submitted by support on Thu, 2016-11-17 15:08

Hi,

That would be no problem to set-up with very little modification.

First, save a copy of scripts/uidmap.php as a new file, scripts/cron_uidmap.php.

Edit the new file scripts/cron_uidmap.php and look for the following code beginning at line 2:

  header("Content-Type: text/plain;charset=utf-8");
  print "Working...\n";
  set_time_limit(0);
  require("../includes/common.php");

...and comment out or DELETE that section.

Next, using your text editor's Search and Replace function, perform a Search and Replace as follows:

Search:

`".$config_databaseTablePrefix."products`

Replace:

`".$config_databaseTablePrefix."products_import`

Finally, edit scripts/cron.php and look for the following code at line 101:

  $sql = "DROP TABLE `".$config_databaseTablePrefix."products`";

...and REPLACE with:

  require("cron_uidmap.php");
  $sql = "DROP TABLE `".$config_databaseTablePrefix."products`";

Cheers,
David.
--
PriceTapestry.com

Submitted by sirmanu on Thu, 2016-11-17 18:29

Oks, thank you!
If someone is going to make these changes, remember to remove your cron tab for old uidmap.php!

Submitted by support on Thu, 2016-11-17 18:44

Good point!

Cheers,
David.
--
PriceTapestry.com

Submitted by sirmanu on Mon, 2016-11-21 10:13

Is it possible that after these changes the EAN process it takes more time?
I am checking EAN index in pt_products_import and it exists so I am not sure exactly why now it takes a almost 4 hours (about 3.000.000 rows).

Submitted by support on Mon, 2016-11-21 11:11

Hi,

If you have previously been running uidmap.php from the command line rather than as a cron job that could explain it, if crond (the process that runs cron jobs) is running with a lower NICE level (priority). This is something that I have come across a few times recently, and is not unreasonable to asert that CRON should be run with lower priority in preference to say, web serving if that is the primary purpose of a server, however not necessarily ideal for complex CRON scripts such the fetch / import / UID mapping process of a large Price Tapestry installation.

If that's not the case let me know and I'll consider other possibilities - I double checked the mod above to ensure that cron_uidmap.php is called after indexes on products_import are enabled so there shouldn't be any difference...

Cheers,
David.
--
PriceTapestry.com

Submitted by sirmanu on Mon, 2016-11-21 12:04

The only change I have done is to change /etc/cron.daily/pricetapesty script from

#!/bin/sh
cd /var/www/example.com/public_html/scripts
/usr/bin/php cron.php
/usr/bin/php uidmap.php

To

#!/bin/sh
cd /var/www/example.com/public_html/scripts
/usr/bin/php cron.php

(with the above changes in cron.php)

As you can see, right now, it is taking almost for 4 hours and it have not finished yet. Is it normal for 3 million products?

Submitted by support on Mon, 2016-11-21 12:14

Hi,

It is a relatively complex process so I don't think that's too unexpected for 3 million rows - one thing I would say however for such a large database, if crond is taking in script output it would make sense to remove the progress output from the new cron_uidmap.php - simply remove all lines beginning print however I don't think that will have any impact on timing but worth doing anyway...

I will review the process and see if there are any alternative approaches / optimisations...

Cheers,
David.
--
PriceTapestry.com

Submitted by sirmanu on Tue, 2016-11-22 10:04

I can confirm, that commenting print lines in the uidmap does not have speed impact.
Maybe adding a new index (EAN, name) would improve performance?

Submitted by sirmanu on Mon, 2017-02-27 18:06

Hi David. I have noticed that some merchants use EAN-13 and others UPC-A (12 digits)
How can I modify the script that in order that if is length=12 (UPC-A), append a 0 before so the matches are correct.
Thanks!

Submitted by support on Tue, 2017-02-28 08:50

Hi,

You could create a simple filter "EAN Fix" to prefix leading 0 if length is 12 characters - add the following new code to includes/filter.php

  /*************************************************/
  /* EANfix */
  /*************************************************/
  $filter_names["EANfix"] = "EAN Fix";
  function filter_EANfixConfigure($filter_data)
  {
    print "<p>There are no additional configuration parameters for this filter.</p>";
  }
  function filter_EANfixValidate($filter_data)
  {
  }
  function filter_EANfixExec($filter_data,$text)
  {
    if (strlen($text)==12) $text = "0".$text;
    return $text;
  }

...and then apply as a Global Filter against the custom EAN field. Fix will be applied at next full import.

Hope this helps!

Cheers,
David.
--
PriceTapestry.com

Submitted by sirmanu on Thu, 2017-03-16 22:20

Hi!
For compatibility with PHP 7.1 I just added

$product = array();
while($product = mysqli_fetch_assoc($result1))

In order to avoid:

PHP Fatal error: Uncaught Error: [] operator not supported for strings in

Regards!

Submitted by sirmanu on Mon, 2017-05-01 18:43

Hi David!
Sometimes I have a strange situation when updating the name of the products. As you can remember from above, I have integrated cron_uidmap into cron.php

Phase 1 is great. However, when attempts to update first product:

[ERROR] /usr/sbin/mysqld: Incorrect key file for table '/pt_products_import.MYI'; try to repair it
[ERROR] /usr/sbin/mysqld: Incorrect key file for table '/pt_products_import.MYI'; try to repair it
[ERROR] Got an error from thread_id=222853, /build/mysql-5.7-QYsmzx/mysql-5.7-5.7.18/storage/myisam/mi_update.c:227
[ERROR] MySQL thread id 222853, OS thread handle 140093921703680, query id 1487673584 localhost globerada updating
UPDATE `pt_products_import` SET name='Biolasi Pianto + (Sabor Limon) 500 Gr',normalised_name='biolasi pianto sabor limon 500 gr',search_name='biolasipiantosaborlimon500gr' WHERE ean='0000000000002' AND `name`=`original_name`

I have read about "Incorrect key file for table", and usually is because /tmp is too small, but I don't think is my case.

Do you have any idea?
Regards!

Submitted by support on Thu, 2017-05-04 06:28

Hi,

That's strange - normally incorrect key file would mean what it says so you could issue a REPAIR TABLE query immediately before phase 2 - have a go with;

  $sql = "REPAIR TABLE `".$config_databaseTablePrefix."products_import`";
  database_queryModify($sql,$result);

Hope this helps!

Cheers,
David.
--
PriceTapestry.com

Submitted by sirmanu on Mon, 2017-06-05 22:09

Hi David.
Is any way to choose which merchant will be taken as name?

Submitted by support on Tue, 2017-06-06 09:05

Hi,

As it stands, uidmap.php selects the first name in alphabetical order which is to ensure that the same name (as far as possible) is used for the same uid whenever the script runs. However it would be no problem to include the merchant name in that initial SELECT, and if not the preferred merchant to use for names then re-query to get the name for that uid from the preferred merchant. To try this, look for the following code at line 38:

      $sql2 = "SELECT SQL_CALC_FOUND_ROWS name FROM `".$config_databaseTablePrefix."products` WHERE ".$config_uidField."='".database_safe($product[$config_uidField])."' ORDER BY name LIMIT 1";

...and REPLACE with:

      $sql2 = "SELECT SQL_CALC_FOUND_ROWS name,merchant FROM `".$config_databaseTablePrefix."products` WHERE ".$config_uidField."='".database_safe($product[$config_uidField])."' ORDER BY name LIMIT 1";

And then the following code at line 52:

        $product["name"] = $product2["name"];

...and REPLACE with:

        $preferredMerchant = "Merchant Name";
        if ($product2["merchant"]<>$preferredMerchant)
        {
          $sql2 = "SELECT SQL_CALC_FOUND_ROWS name,merchant FROM `".$config_databaseTablePrefix."products` WHERE ".$config_uidField."='".database_safe($product[$config_uidField])."' AND merchant='".database_safe($preferredMerchant)."'";
          $result2 = mysqli_query($link2,$sql2);
          if (mysqli_num_rows($result2))
          {
            $product2 = mysqli_fetch_array($result2,MYSQLI_ASSOC);
          }
        }
        $product["name"] = $product2["name"];

(edit the value of $preferredMerchant in the first line of the replacement, which as a constant of course can be included at the top of the script, but included above at this point for documentation purposes...)

Hope this helps!

Cheers,
David.
--
PriceTapestry.com

Submitted by bodybuildingcom... on Sun, 2017-09-17 16:01

Hi David,

I have gone ahead and implemented the ProductMappingByUID.zip. Unfortunately, I am now unable to import any feeds, as well as it has '0' some of the feeds I have already imported.

I saw in earlier comments in this thread from you, to re-import dbmod

<?php
  require("includes/common.php");
  $sql = "ALTER TABLE `".$config_databaseTablePrefix."feeds`
            ADD `field_ean` VARCHAR(255) NOT NULL";
  database_queryModify($sql,$result);
  $sql = "ALTER TABLE `".$config_databaseTablePrefix."products`
            ADD `ean` VARCHAR(255) NOT NULL";
  database_queryModify($sql,$result);
  print "Done.";
?>

Unfortunately this has not fixed my problem. I can see in the myPHPadmin that it has imported the extra question in the pt_feeds table but it still doesn't allow me to register the feeds.

One thing I did notice that has disappeared, is the pt_products table from the database. I would assume this needs to be re-imported. Is there a way to re-import this, without having to re-import the whole database?

Thanks.

Norbert

Submitted by support on Mon, 2017-09-18 08:49

Hi Norbert,

That's strange that the pt_products table has disappeared but it can be re-created easily by running

/setup.php?installDB=1

(existing tables will not be affected as the install tables process does not include DROP TABLE functionality)

With the products table re-created, run the following dbmodall.php script which will create the pt_feeds and pt_products table columns for any configured custom fields, including the ean field which from the above you will have added as $config_fieldSet["ean"] = "EAN";

<?php
  
require("includes/common.php");
  
$config_databaseDebugMode FALSE;
  
$ignore = array("name","description","image_url","buy_url","price","category","brand");
  foreach(
$config_fieldSet as $field => $v)
  {
    if (
in_array($field,$ignore)) continue;
    
$sql "ALTER TABLE `".$config_databaseTablePrefix."feeds`
              ADD `field_"
.$field."` VARCHAR(255) NOT NULL";
    
database_queryModify($sql,$result);
    
$sql "ALTER TABLE `".$config_databaseTablePrefix."products`
              ADD `"
.$field."` VARCHAR(255) NOT NULL";
    
database_queryModify($sql,$result);
  }
  print 
"Done.";
?>

Hope this helps!

Cheers,
David.
--
PriceTapestry.com

Submitted by bodybuildingcom... on Mon, 2017-09-18 21:15

Hi David,

Thank you. That has worked. It has fixed the tables.

Norbert

Submitted by Erik on Sat, 2019-12-28 20:42

Hi David,

I'm considering this but also I'm wondering by having product feeds where the main mapping relies on the manufacturer ID, would it be possible to create a regex field on the admin side solely connected to a custom field? I'm thinking some merchants have better naming of products as well as images, this way I could just import the everything into the regexp table (pt_productsmap_regexp).

Is there a way to do this or does the regexp field account for custom field values as well?

For example I have a custom field where the ID is ABCD I'd import the product data and in the regexp field add /ABCD/ would it be possible for creating a separate regexp field that queries a custom field?

Submitted by support on Mon, 2019-12-30 09:53

Hi Erik,

Just to check I have understood correctly, were you wanting to use uidmap.php but with preferred merchants from which to pick the product name to be used? That's no problem but just wanted to check, as no need to RegExp specifically in this case...

Cheers,
David.
--
PriceTapestry.com

Submitted by Erik on Mon, 2019-12-30 20:07

Hey David!

Thank you for your answer, that sounds great. I did a little search and I'm assuming it's the solution you shared in this thread: https://www.pricetapestry.com/node/5410

That makes everything much easier, let me know if there's anything I'm missing here otherwise I will implement this soon.

Best,
Erik

Submitted by Leosch on Thu, 2022-03-31 13:02

I have two merchants which do have a leading zero in front of their EAN.
Is there a filter which can remove a single leading zero in EAN?

Submitted by support on Fri, 2022-04-01 08:15

Hi,

To add an "EAN Fix" filter, add the following code to includes/filter.php

  /*************************************************/
  /* EAN Fix */
  /*************************************************/
  $filter_names["eanFix"] = "EAN Fix";
  function filter_eanFixConfigure($filter_data)
  {
    print "<p>There are no additional configuration parameters for this filter.</p>";
  }
  function filter_eanFixValidate($filter_data)
  {
  }
  function filter_eanFixExec($filter_data,$text)
  {
    if (substr($text,0,1)==="0")
    {
      $text = substr($text,1);
    }
    return $text;
  }

(other fix conditions could be added here if required as they come up)

Then add an EAN Fix filter to the EAN field for the feed containing the leading zero and this will strip if present...

Cheers,
David.
--
PriceTapestry.com

Submitted by Andrewc on Mon, 2022-04-25 13:37

David is this feature "Automatic Product Mapping by Unique ID" added to the codebase if I download the latest version? or Do I need to implement as per your instructions on this forum?

Submitted by support on Mon, 2022-04-25 13:47

Hi Andrew,

No - it's a standalone download and should work against all previous distributions following the instructions; note that the scripts use the mysqli library functions which may not be available on a server that has been online for many years running earlier versions of PHP / MySQL but that is unlikely now as support for older versions of PHP has been deprecated by most hosting companies in recent years...

Cheers,
David.
--
PriceTapestry.com