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
– Álvaro González, Commented Jan 26, 2011 at 11:36

Harish · Accepted Answer · 2011-01-26 11:34:53Z

1

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

answered Jan 26, 2011 at 11:34

Harish

2,3344 gold badges24 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 11:55:29Z

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, 2017 at 11:55

CommunityBot

11 silver badge

answered Jan 26, 2011 at 11:35

Jon Black

16.6k5 gold badges45 silver badges42 bronze badges

Comments

Dan Grossman · Accepted Answer · 2011-01-26 11:33:05Z

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, 2011 at 11:33

Dan Grossman

52.5k10 gold badges115 silver badges101 bronze badges

1 Comment

Kent Over a year ago

Nice one, Just what Im looking for. I cannot understand the MySQL reference library all the time. Thanks alot

Collectives™ on Stack Overflow

MySQL - Search One field for Multiple Values

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related