CODEWITHSUNDEEP
mysqlsearch
Kent
Kent

Reputation: 1

MySQL - Search One field for Multiple Values

Tablename: tbl_person
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

Upvotes: 0

Views: 6547

Answers (3)

Jon Black
Jon Black

Reputation: 16559

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

Upvotes: 1

Harish
Harish

Reputation: 2324

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

Upvotes: 1

Dan Grossman
Dan Grossman

Reputation: 52372

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.

Upvotes: 0

Related Questions