I am working on adapting Price Tapestry to my site (see beta.rewardsdb.com) - as a rewards comparison engine. In this capacity it searches stores that are partnered with rewards programs rather than products from merchants. So far it is working OK but there is one quirk I can't seem to fix.
One of the stores (which is stored as a product) is "Best Buy" - if you search for "Best Buy" or "Best" you get no results but a search for "Buy" shows Best Buy in the list of results. What is causing this and how on earth do I get it to stop doing it?
Hi,
None of the feeds had 'best' appearing in more than a handful of records - certainly nowhere near 50%. I tried the solution above but this causes more problems than it solves, if I do that, search for best returns results but best buy does not, nor does apple store which worked with the original code. I have restored the original string.
Each rewards program has its own feed, so you can browse which merchants are listed in each feed, I tried re-importing them all but that didn't make a change....
Any other ideas?
Hi Andrew,
I don't really understand this. Could you email me a link to your site (reply to your registration code or forum reg email is the easiest way) and let me know the name of the feed and I will download it and run it through my test server to try and find out why "Best" is not being indexed...
Cheers,
David.
Hi David,
Thanks for your reply about this issue being caused by the default stopwords configuration in MySQL. Unfortunately my host does not allow alteration of this setting. Can you think of any work-arounds?
Andrew
Hi Andrew,
The first option that comes to mind is to check for the word "best" in the query when deciding which index to use (full text or normal) and revert to the normal index if "best" is present. The test is done on line 63 of search.php as follows:
if (strlen($parts[0]) > 3)You would need to change this as follows...
if ((strlen($parts[0]) > 3) && (strpos(strtolower($parts[0]),"best")===FALSE))Cheers,
David.
Thanks,
That allows "Best" to return a result for best buy but not "best buy". Is there a way I can add that as well?
(Curiously "bestbuy" returns results for best buy).
Thanks again,
Andrew
Hi Andrew,
The basic search index actually compares against a space removed version of the product name, so what you need to do in this case is to add function to remove spaces from the query (because you are now allowing some queries greater than 3 characters in length through into the basic search method).
In the second block of the IF statement below where the above change was made, you will see the basic search SQL as follows (line 73 in the distribution)...
$sql = "SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants FROM `".$config_databaseTablePrefix."products` WHERE search_name LIKE '%".database_safe($parts[0])."%' GROUP BY name";Change this as follows:
$sql = "SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numMerchants FROM `".$config_databaseTablePrefix."products` WHERE search_name LIKE '%".str_replace(" ","",database_safe($parts[0]))."%' GROUP BY name";That should do the trick...
Cheers,
David.
Hi Andrew,
There is a optimisation feature of MySQL's full text indexing method that means that words that appear in greater than 50% of records are not indexed.
I notice that you have several feeds on the site now; but it is possible that the majority of product names containing "best" were imported early, perhaps from the first feed to be imported and so at this time were optimised out by the indexer. One way to test if this is the case would be to re-import the feed containing a "Best ..." product name.
Price Tapestry will only use the full text index when the search term is greater than 3 characters, as this is another limitation of the full text indexing method. Up to 3 characters, a basic substring search method is used.
What you can do is enable the basic search method for all queries on your site if it turns out that in your case full text indexing does not give the best results. In search.php on line 63 is the following code:
if (strlen($parts[0]) > 3)To enable the basic search for all queries, comment out and replace this line with:
if (0)Hope this helps,
Cheers,
David.