How to search mulitple value seperated by commas in mysql

Question

Hi all,

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 trophy1 then trophy1 should be as result.

How to solve this issue.

thanks in advance

Answer 1

You can use like.

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

Where the % is a wildcard.

Answer 2

These keywords must be stored in the separate table

Answer 3

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'

Answer 4

Hello,

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

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