Relevance of search results

[rudolfl]

Hi all,

 

How can searching can be improved?

 

For example, my site has lost of products that contain phrase "in place". I saw people looking for "lace". While we do have few lace products, search results show pretty much all products when "search in description" option is on.

 

One suggestion is to match fulltext search as in this link:

http://stackoverflow.com/questions/1568068/mysql-select-like-must-match-whole-words-only-to-the-variable

(I did not check if this will work, though).

 

Is there any way to "smarten up" searching?

 

Thanks,

Rudolf

[MrPhil]

I'll assume you meant "lots of products" :)

 

The SQL query uses LIKE %lace% to match any field containing "lace" (with anything before or after that substring), which will of course also match "in place". If you removed the %, it should only match a text field with exactly "lace" and nothing else (which is probably not what you want).

 

A more sophisticated search would allow the user to add exclusion terms, such as "don't match 'in place'". Depending on what kind of sanitizing of input that the current code does, you might be able to specify a search term of lace% AND NOT LIKE %in place (but it might not work). To prevent SQL injection attacks, such a term may be disallowed. A simple syntax (requiring PHP code changes) would be lace -"in place", which would expand to %lace% AND NOT LIKE %in place". In theory, anyway.

 

If there are just a few terms that cause trouble for a specific site, you could take the burden off the customer by looking for specific search terms and adding the AND NOT LIKE expression. However, this would be specific to one shop and might have to be frequently  updated, so it's not an elegant solution.

 

There might be some setting or term in MySQL that limits matches to whole words, such as (in the post you referenced) FULLTEXT fields (requiring database changes). I don't know how to do it "on the fly", when the customer wants to restrict matches to "lace" and ignore "in place", without making it full-time. The closest to that would be -term clauses, as discussed above. That still puts the burden on the customer to know how to exclude unwanted results.

[rudolfl]

Thanks,

 

The problem is that y customers are middle-aged ladies, so any "advanced search" will not work.

I started to collect statistics and will go with the option of "sanitising" the search string. This way, at least I can give out predictable results.

 

I think I should also start looking at Google custom search. As far as I know it allows search on the site only, but I will have to post process the result to output them in shop format (products list), rather than Google search result format.

 

Rudolf

[Havock]

Maybe it's a dumb suggestion, but you could make it a two steps process.

 

First just count the number of results of the standard query (with the % signs).

 

If the number of results is lower than a number you choosed (let's say 20 for example), then you show the relevant products.

 

if the number of results is greater than 20, you do not show anything, but make a new query without the % so than you  get the exact match of the search term.

[MrPhil]

The % is a wildcard. If you omit it, and just search LIKE lace (or maybe, LIKE 'lace'), I think that will match a product description ONLY if it is JUST lace. That probably won't work as desired.

 

Even for little old ladies, if you show an example like lace -'in place' near the search box, and explain it, that might be adequate. Or, add a separate "terms to exclude" box next to the original search box:

 

Search for: [ lace           ]

Excluding:  [ 'in place'    ]  [Go]

 

For advanced users, a whole search language with + and - terms, and key to switch to a regular expression (say, '@') might be popular.