MySQL - Search One field for Multiple Values

Tablename: tbl_pe开发者_如何学运维rson

Columns: id,name,bio

Sample Data: (id,name,bio)

1,Mike,Cambridge university degree in physics

2,Pete,Cambridge university degree in geology

3,Sam,Oxford university degree in geology

Problem: I would like to create a (My'SQL') search that can take multiple values and search and match them in one column

Example:

specifically search for: cambridge geology

I would like it to return record 2,Pete

and not all the record (because they contain matching keywords cambridge,geology)

The search that I have so far is

SELECT * FROM tbl_person WHERE tbl_person.bio IN ('cambridge','geology')

--Now this does not return a match--

Any Ideas please

    SELECT * FROM tbl_person WHERE tbl_person.bio
    LIKE '%cambridge%' AND tbl_person.bio LIKE '%geology%'

try fulltext searching which will give you more functionality and better performance

http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html

Any way to achieve fulltext-like search on InnoDB

SELECT
  *
FROM
  tbl_person
WHERE
  bio LIKE '%cambridge%'
AND
  bio LIKE '%geology%'

You will generate all the LIKE clauses for the WHERE clause in your program based on the search your user did, then AND them together to create the query.