Searching for specific phrase in search

PHP
1

I have a database full of products and I also have a product search. Now the search works fine but I want to make the search results more specific to the search term the user types into the search box. At the moment if for example the search term is ‘call of duty’, it brings back results with ‘call’, ‘duty’ in the title or description.

What I really want is for the search to try and find the phrase ‘call of duty’ in the product name field of the product table. Then if there are no results it will bring back the results of ‘call’, ‘duty’ etc.

I have googled this and spent quite a bit of time testing different methods and looking through other examples, tutorials etc. Has anyone got a suggestion as to which way to go about this. Should I use strpos or preg_match to get more relevant results?

My search code is

<?php
$result = mysql_query("SELECT merchant_name, aw_product_id, aw_thumb_url, brand_name, product_name, description, category_name, aw_deep_link, aw_image_url, search_price, model_number FROM products WHERE MATCH(merchant_name, product_name, specifications, brand_name, ean, mpn, model_number, category_name) Against ('$terms' IN BOOLEAN MODE) ORDER BY id LIMIT 10")
?>

Cheers

2

Cheers for the suggestion. Will look into that.

3

Hi…


select * from (
            select *, 2 as score from products
                where product name like '&#37;call of duty%'
        union
            select *, score as 1 from products
                where match() against ('call of duty')) as scored
    order by score desc

This will place the results of the first query at the top. Wrap it again in a subquery to remove duplicates or just filter it using PHP.

I’ve used a like query in the first clause, but you could use any query you like.

Really though I’d start looking at a specialist engine like Sphinx or Lucene.

yours, Marcus