searching database for multiple words

[seany123]

Hi, I know this could probably be solved using either php or SQL, so i wasnt sure which board to post this on, but ive always been under the impression its best to solve the issues through SQL if possible as thats the most efficient way.

 

I have a products table which contains

product_name varchar (255)

 

then im allowing users to search for products,

with the current php code:

product_name LIKE '%$searchTerms%' //searchTerm would contain ie: "red apples"

it wont return results when the users type the plural for a word ie apples instead of apple,

or if they type apple red instead of red apple.

 

im wondering the best way to search products?

 

i thought i could explode the searchTerm variable and run the like query for every word

product_name LIKE '%$searchTerm[0]%' OR product_name LIKE '%$searchTerm[1]%'

but then im worried about relevance, if one of the search terms is the word "and" then it might return completely irrelevant results.

 

also how would i go about dealing with the plurals?

 

Any suggestions on the best way to do this?

 

thanks

Edited November 28, 2016 by seany123

[Barand]

Sounds like a case for FULLTEXT INDEX