CODEWITHSUNDEEP
mysqlregex
xkeshav
xkeshav

Reputation: 54022

MySQL search a text from two columns

i have a table (tbl_world) which look like this 

  id | first_name | last_name | age | class |

now i want to search the text which can be anywhere in first_name or in last_name

i m using below mysql query

 "SELECT * FROM tbl_world WHERE REGEXP '".$word."' IN( first_name, last_name)";

where $word is user input (means if i search 'hell' then 'hello' as well as 'wellhell' also returned in result)

above query display error, please suggest me optimize method for search in mysql.

addition question: should i use LIKR or RLIKE?

Upvotes: 1

Views: 1292

Answers (3)

Ian Clelland
Ian Clelland

Reputation: 44132

"SELECT * FROM tbl_world WHERE first_name LIKE '%'".$word."%' OR last_name LIKE '%".$word."%'"

Upvotes: 0

slebetman
slebetman

Reputation: 113906

I would do it with:

"SELECT * FROM tbl_world WHERE first_name REGEXP '" . $word .
    "' OR  last_name REGEXP '" . $word . "'"

Upvotes: 2

LesterDove
LesterDove

Reputation: 3044

 "SELECT * FROM tbl_world 
WHERE first_name LIKE  '%".$word."%' 
OR last_name LIKE  '%".$word."%'";

(Consider enabling fulltext search if you want to search for a string among multiple fields.)

Upvotes: 0

Related Questions