MySQL searching using many 'like' operators: is there a better way?
Posted
by
DrAgonmoray
on Stack Overflow
See other posts from Stack Overflow
or by DrAgonmoray
Published on 2012-03-18T17:58:18Z
Indexed on
2012/03/18
18:03 UTC
Read the original article
Hit count: 209
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?
© Stack Overflow or respective owner
