CODEWITHSUNDEEP
phpmysql
Jobz
Jobz

Reputation: 462

Search query optimization in mysql

I am working on a search query. I have a string "thomas alva edison" in my table. I want to display this string if the user searches for,

thomas alva edison,thomas,alva,edison,thomas alva,thomas edison,etc

but not for

tho,son,alv,etc

what I did is 

select name from tbl_names where name like "."'%".$key."%'

How can I modify this query to get the correct results.

Upvotes: 1

Views: 103

Answers (3)

TheFoxLab
TheFoxLab

Reputation: 704

I recommended to use REGEXP for word search in query,

A query should be like:

SELECT name FROM `tbl_names` WHERE `name` REGEXP '[[:<:]]thomas[[:>:]]'

Hope this will be useful to you. For more details please check this Check this answer.

Upvotes: 3

Karthik
Karthik

Reputation: 1488

MySQL provides a wonderful way (Full-text Search) of implementing a little search. http://viralpatel.net/blogs/full-text-search-using-mysql-full-text-search-capabilities/

example : 

select name from tbl_names WHERE MATCH (name) AGAINST ('thomas');

We have many options in full text search and less then MySQL 5.6 MYISAM engine only.

Upvotes: 3

hellcode
hellcode

Reputation: 2698

You could also try something like this:

$sql = "select name from tbl_names where ";
$array = explode(" ",$key);
$or = "";
foreach($array as $word) {
  if(strlen(trim($word))>0) {
    $sql.= $or."(name like '%".$word."%')";
    $or = " OR ";
  }
}

Upvotes: 0

Related Questions