MySQL - Search One field for Multiple Values

Question

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

Next time you can replace *does not work at all* with *does not return any match* ;-) — Álvaro González, Jan 26 '11 at 11:36

score 1 · Answer 1 · answered Jan 26 '11 at 11:34

1

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

answered Jan 26 '11 at 11:34

Harish

2,311
4
23
28

score 1 · Answer 2 · edited May 23 '17 at 11:55

1

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

edited May 23 '17 at 11:55

Community

1
1

answered Jan 26 '11 at 11:35

Jon Black

16,223
5
43
42

score 0 · Answer 3 · answered Jan 26 '11 at 11:33

0

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.

answered Jan 26 '11 at 11:33

Dan Grossman

51,866
10
112
101

Nice one, Just what Im looking for. I cannot understand the MySQL reference library all the time. Thanks alot – Kent Jan 26 '11 at 12:45

MySQL - Search One field for Multiple Values

3 Answers3