Hi David.
First of all, as I didn't reply in node 6172, I hope you are having great holidays. I wish you all the best for 2017. You are the most committed person with a software I have ever met
I am wondering about your opinion for mapping products when keywords appears or not.
For now, I am using this "complex" regex.
For example, if I want to map Epson Office BX525
/^(?=.*Epson)(?=.*Office)(?=.*525)((?!.*band)(?!.*accesories)(?!.*cartridge)(?!.*charger)(?!.*cable)(?!.*strap)).*$/i
As this Regex is sometimes quit long, I had to modify field regexp at "pt_productsmap_regexp".
So, do you think there is an easiest way to achieve that?
Thank you for your time.
Excellent! Just as expected. Is any way that I can have "preview" (test) like in regexp?
Hi,
To add Test functionality, edit admin/productsmap_configure.php and look for the following code at line 77:
print "<h3>".translate("Configure")." (".$productmap["name"].")</h3>";...and REPLACE with:
print "<h3>".translate("Configure")." (".$productmap["name"].")</h3>";
if (isset($_POST["submit"]) && ($_POST["submit"]=="Test"))
{
$productmap["alternates"] = $_POST["alternates"];
$alternates = explode("\n",trim($productmap["alternates"]));
$wheres = array();
foreach($alternates as $alternate)
{
if (substr($word,0,1)=="-")
{
$alternate = substr($alternate,1);
}
$wheres[] = " (original_name LIKE '%".database_safe(trim($alternate))."%') ";
}
$where = implode(" OR ",$wheres);
print "<h4>Test Results</h4>";
$link = mysqli_connect($config_databaseServer,$config_databaseUsername,$config_databasePassword,$config_databaseName);
mysqli_set_charset($link,"utf8");
$sql = "SELECT merchant,original_name FROM `".$config_databaseTablePrefix."products` WHERE ".$where;
mysqli_real_query($link,$sql);
$result = mysqli_use_result($link);
$products = array();
while($importRecord = mysqli_fetch_assoc($result))
{
foreach($alternates as $alternate)
{
$alternate = trim($alternate);
if (substr($alternate,0,1) == "=")
{
if ($importRecord["original_name"] == substr($alternate,1))
{
$products[] = $importRecord;
break;
}
}
else
{
$found = 0;
$words = explode(" ",$alternate);
foreach($words as $word)
{
if (substr($word,0,1)=="-")
{
if (stripos($importRecord["original_name"],substr($word,1)) === FALSE) $found++;
}
elseif ($word)
{
if (stripos($importRecord["original_name"],$word) !== FALSE) $found++;
}
}
if ($found == count($words))
{
$products[] = $importRecord;
break;
}
}
}
}
if (count($products))
{
print "<table>";
print "<tr><th>Stockist</th><th>Catalogue Product Name</th></tr>";
foreach($products as $product)
{
print "<tr><td>".$product["merchant"]."</td><td>".$product["original_name"]."</td></tr>";
}
print "</table>";
}
else
{
print "<p>No Matches</p>";
}
}And then the following code at (now) line 166:
widget_formButtons(array("Save"=>TRUE),"productsmap.php");...and REPLACE with:
widget_formButtons(array("Save"=>TRUE,"Test"=>TRUE),"productsmap.php");Hope this helps!
Cheers,
David.
--
PriceTapestry.com
I can confirm, that is an elegant solution (although a bit slow), but it works as expected.
Thank you, David.
Hi again and sorry another question on this topic!
As looking into the whole table is a bit slow.
Could you propose an alternative to TEST via boolean search?
Hi,
I've updated the above to make the initial SELECT with a WHERE clause for each keyword which should speed things up...
Hope this helps!
Cheers,
David.
--
PriceTapestry.com
Hi David. I was thinking about this topic, again.
Is it feasible boolean search (at test mode) to gain speed?
Sorry for insisting, but I have several rows and the test mode is veryyy slow because it has to do a full scan
Hi,
You could use a BOOLEAN MODE full text search for the initial query but you would need to create a new FULLTEXT index against the `original_name` field - which is no problem of course - just consider the impact on database size and import time. To make the index, run the following dbmod.php script, ideally from the command line as may take a little while if the database is large;
<?php
set_time_limit(0);
require("includes/common.php");
$sql = "CREATE FULLTEXT INDEX original_name_2
ON `".$config_databaseTablePrefix."products` (original_name)";
database_queryModify($sql,$result);
print "Done.";
?>
With that new index in place, in the test modification where you have the following code;
$wheres = array();
foreach($alternates as $alternate)
{
if (substr($word,0,1)=="-")
{
$alternate = substr($alternate,1);
}
$wheres[] = " (original_name LIKE '%".database_safe(trim($alternate))."%') ";
}
$where = implode(" OR ",$wheres);...REPLACE with:
$words = array();
foreach($alternates as $alternate)
{
if (substr($word,0,1)=="-")
{
$alternate = substr($alternate,1);
}
$words[] = "+".database_safe(trim($alternate));
}
$where = "MATCH original_name AGAINST ('".implode(" ",$words)."' IN BOOLEAN MODE)";Hope this helps!
Cheers,
David.
--
PriceTapestry.com
Hi,
Thank you for your comments!
It's straight forward to add negative keyword support to regular Product Mapping. To try this, edit includes/admin.php and look for the following code at beginning at line 282:
if ($word){
if (strpos($importRecord["name"],$word) !== FALSE) $found++;
}
...and REPLACE with:
if (substr($word,0,1)=="-"){
if (stripos($importRecord["name"],substr($word,1)) === FALSE) $found++;
}
elseif ($word)
{
if (stripos($importRecord["name"],$word) !== FALSE) $found++;
}
Specify negative keywords prefixed with "-". This mod also makes Product Mapping case insensitive by using stripos() instead of strpos()...
Hope this helps!
Cheers,
David.
--
PriceTapestry.com