Creating a comprehensive search of products

[mallen]

I have been asked to create a more comprehensive search on my website. I have listed products. The table consists of id, name, model, description, watt_range, terms.

The problem I have having is if someone searches with multiple words the search won't give results. If I have an item like this.  AreaLight2, xyz light, here is the description, 70-100w, area.

If someone searches "AreaLight2" it returns the result. If you search "Arealight2 100w" it won't return any results. Can anyone help me point me in the right direction to get this to work? Thanks.

Here is the query 'SELECT cat.cat_name, cat.cat_id, prod.prod_id, prod.prod_name, prod.prod_mainImage, prod.prod_description, prod_model, prod.terms
FROM `products` as prod LEFT JOIN `category_assoc` AS assoc ON assoc.prod_id = prod.prod_id
LEFT JOIN `categories` AS cat ON cat.cat_id = assoc.cat_id
WHERE prod.terms LIKE '%Arealight2%' OR LIKE '%100w%'
OR prod.prod_name LIKE '%Arealight2%' OR LIKE '%100w%''

[Jacques1]

You can't write

... some_column LIKE '...' OR LIKE '...' ...

This may make sense in colloquial language, but to a computer, this is gibberish. The boolean OR operator can only be applied to two boolean expressions. But “LIKE ...” is no expression at all, it's just a syntax fragment.

 

A syntactically valid query would look like this:

... some_column LIKE '...' OR some_column LIKE '...' ...

[mallen]

I found a solution using MySQL full text search in boolean mode that has worked well.