2014-12-30 12:32



How to search for a comma-separated string in a database table in mySQL.

Suppose, I have a string in variable $facilities='breakfast,dinner,lunch' and in my database I have a string saved in a field called facilities having values breakfast,dinner,clothing,lunch,hot water.

How do I get the row having values $facilities?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答


  • drsb77336 drsb77336 7年前

    Work like this i hope this is your requirement

    select * from table where field_name like '%$fieldname%' 
    点赞 评论 复制链接分享
  • doukuiqian9911 doukuiqian9911 7年前

    You're going to need to break apart the comma separated string first, and include a clause for each, so for that input you'd generate SQL like (assuming you want rows that have all three, and maybe other flags set):

    SELECT      *
    FROM        YourTable
    WHERE       FIND_IN_SET('breakfast', facilities)
        AND     FIND_IN_SET('dinner', facilities)
        AND     FIND_IN_SET('lunch', facilities)

    You can break the string apart in PHP with something a little like:

    $desired = explode ( "," , $facilities );

    And just use a loop to build your SQL

    点赞 评论 复制链接分享
  • douqiao8370 douqiao8370 7年前
    SELECT  *
    FROM    tableName
    WHERE   FIND_IN_SET('lunch', 'breakfast,dinner,lunch')


    SELECT * FROM tableName WHERE FIND_IN_SET('lunch', food_column)

    food_column is the name of the column where the string 'breakfast,dinner,lunch' is stored

    点赞 评论 复制链接分享