MySQL searching using many 'like' operators: is there a better way?
- by DrAgonmoray
I have a page that gets all rows from a table in a database, then displays the rows in an HTML table.
That works great, but now I want to implement a 'search' feature. There is a searchbox, and search-terms are separated by a space. I am going to make it search three fields for the search terms, 'make' 'model' and 'type.' These three fields are VARCHAR(30).
Currently if I wanted to search using 3 terms (say 'cool' 'abc' and '123') my query would look something like this.
SELECT * FROM table WHERE make LIKE '%cool%' OR make LIKE '%abc%' OR make LIKE '%123%' OR model LIKE '%cool%' OR model LIKE '%abc%' OR model LIKE '%123%' OR type LIKE '%cool%' OR type LIKE '%abc%' OR type LIKE '%123%'
That looks really bad, and it will get even worse if there are more search terms or more fields to search.
My question to you: is there a better way to search? If so, what?
