How to search mulitple value seperated by commas in mysql

How to search multiple values separated by commas.

ex:

table name : searchTest

id    name      keyword

1     trophy1   test1,test2,test3

2     trophy2   test2,test5

Points:

1. If i search for test2 both results trophy1 and trophy2 should be display.

2. If i search for tr开发者_如何学运维ophy1 then trophy1 should be as result.

How to solve this issue.

thanks in advance

I would say that, here, your data structure is quite not right.

It would be a better solution to not store several values in one field using some comma-separated format, but use three tables, defined this way :

• searchtest
  • id
  • name
• keywords
  • id
  • word
• keywords_searchtest
  • id_keyword
  • id_searchtest

With that, searching for entries in searchtest that have specific keywords would be as simple as :

select searchtest.*, keywords.*
from searchtest
    inner join keywords_searchtest on keywords_searchtest.id_searchtest = searchtest.id
    inner join keywords on keywords.id = keywords_searchtest.id_keyword
where keywords.word = 'test2'

And, additionnaly, you'd have to search for searchtest entries with a specific name :

select *
from searchtest
where name = 'trophy1'

These keywords must be stored in the separate table

For Point 1 : select * from searchTest where keyword like LIKE '%test2%

For Point 2 : select * from searchTest where name like LIKE 'trophy1%

Use the FIND_IN_SET MySQL string function, like so:

SELECT * FROM searchTest WHERE FIND_IN_SET('test2', keyword) > 0

You can use like.

select * from searchTest where keyword like '%test2%'

Where the % is a wildcard.

select * from searchTest where keyword  LIKE 'test2' or LIKE '%,test2' or LIKE 'test2,%'

it will work i have done this using above query