Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
331 views
in Technique[技术] by (71.8m points)

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

How to solve this issue.

thanks in advance

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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'

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...