CODEWITHSUNDEEP
mysqlsearchhtml-tablesql-like
Jacob Brunson
Jacob Brunson

Reputation: 1482

MySQL searching using many 'like' operators: is there a better way?

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?

Upvotes: 2

Views: 336

Answers (3)

thecodedeveloper.com
thecodedeveloper.com

Reputation: 3240

Use REGEXP instead of like

SELECT * FROM table WHERE make REGEXP 'cool|abc|123' OR model REGEXP 'cool|abc|123' OR type REGEXP 'cool|abc|123';

read more about REGEXP

http://dev.mysql.com/doc/refman/5.1/en/regexp.html

http://www.go4expert.com/forums/showthread.php?t=2337

Upvotes: 2

yehuda
yehuda

Reputation: 1282

You should use FULLTEXT. Search SO about it, it is very easy and useful.

Upvotes: 2

Hassan
Hassan

Reputation: 301

You could use a JQuery Table Plugin, like data tables Features on-the-fly filtering which you could use instead of a lengthy sql command. And its free.

Upvotes: 0

Related Questions