Hello there,
i'm trying to use the SOUNDEX statement for my search (i know it's slowing down the search but i use the script localy, not online).
Right now, i'm using the boolean method, with a code someone kindly posted here :
default:
if (strlen($parts[0]) > 1)
{
$match = "+".str_replace(" "," +",$parts[0]);
$sql = "SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, MATCH name AGAINST ('".database_safe($match)."' IN BOOLEAN MODE) AS relevance FROM `".$config_databaseTablePrefix."products` WHERE MATCH name AGAINST ('".database_safe($match)."' IN BOOLEAN MODE) GROUP BY name";
$sqlResultCount = "SELECT COUNT(DISTINCT(name)) as resultcount FROM `".$config_databaseTablePrefix."products` WHERE MATCH name AGAINST ('".database_safe($match)."' IN BOOLEAN MODE)";
$orderBySelection = $orderByFullText;
}I really don't know how to add this Soundex statement . Any clue guys ?
Thanks a lot !
thank you for your help david !
About the 3 characters search, you're right, and i still have a problem with that ! I work locally with easyPHP. I added the value ft_min_word_len = 3 in "my.ini", under [mysqld] , restart mysql, even register and import again the feeds but it still not working. Don't know what to do next...!
Another thing about the search :
Let say i have an item called "KDLW4500SE" . Do you know if it's possible to find this item by using the keyword "W4500" ? (if possible without any wildcards before or after, simply the keyword alone ?)
Thanks a lot !!!!!
Hi,
As far as I know, 3 is the default - so if you changed the value of ft_min_word_len to 1 in my.ini (restart, re-import) that might make a difference. There is a default list of "stop words" as well so things like "and" and "or" etc. shouldn't be indexed.
Regarding string matching within a whole word; Price Tapestry (latest version) will do this whenever any of the words in the query are less than 4 characters, as it uses the "search_name" field in the products table which is a version of the name field with spaces removed. If you download the latest version and study search.php, look at line 91 where the LIKE keyword is used, with each word surrounded by the % character, which is a wildcard in SQL. This means that W4500 would match KDLW4500SE but only when always using the basic search method. Search performance is always a trade-off betweeen performance and functionality...
Hope this helps!
Cheers,
David.
Yep i had a problem with easyphp, i reinstalled it and set ft_min_word_len value and it's now working. And you were right, i downloaded the last version of Price Tapestry and it's working like a charm, thanks to you, great support ! :-)
Hi,
SOUNDEX returns a string value that you can use in comparison, so within SQL it would be used something like this:
WHERE SOUNDEX('something') = SOUNDEX(name)You could add SOUNDEX to the SQL above using an OR clause, for example:
default:if (strlen($parts[0]) > 1)
{
$match = "+".str_replace(" "," +",$parts[0]);
$sql = "SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants, MATCH name AGAINST ('".database_safe($match)."' IN BOOLEAN MODE) AS relevance FROM `".$config_databaseTablePrefix."products` WHERE MATCH name AGAINST ('".database_safe($match)."' IN BOOLEAN MODE) OR (SOUNDEX(".database_safe($parts[0]).") = SOUNDEX(name)) GROUP BY name";
$sqlResultCount = "SELECT COUNT(DISTINCT(name)) as resultcount FROM `".$config_databaseTablePrefix."products` WHERE MATCH name AGAINST ('".database_safe($match)."' IN BOOLEAN MODE) OR (SOUNDEX(".database_safe($parts[0]).") = SOUNDEX(name))";
$orderBySelection = $orderByFullText;
}
One final note - I see that you have changed the test for strlen($parts[0]) to 1 - this is originally 3 because by default, MySQL's full text index does not apply to words up to 3 characters (so that the performance of the index is not affected by thousands of references to short words like "an", "or" etc.) however you can modify MySQL to index all words which you would need to do with this change in place...(although it may require a re-compile of MySQL!)
Cheers,
David.