MySQL Like multiple values

Question

I have this MySQL query.

I have database fields with this contents

sports,shopping,pool,pc,games 
shopping,pool,pc,games 
sports,pub,swimming, pool, pc, games   

Why does this like query does not work? I need the fields with either sports or pub or both?

SELECT * FROM table WHERE interests LIKE ('%sports%', '%pub%')

Answer 1

Faster way of doing this:

WHERE interests LIKE '%sports%' OR interests LIKE '%pub%'

is this:

WHERE interests REGEXP 'sports|pub'

Found this solution here: http://forums.mysql.com/read.php?10,392332,392950#msg-392950

More about REGEXP here: http://www.tutorialspoint.com/mysql/mysql-regexps.htm

Answer 2

Your query should be SELECT * FROM `table` WHERE find_in_set(interests, "sports,pub")>0

What I understand is that you store the interests in one field of your table, which is a misconception. You should definitively have an "interest" table.

Answer 3

More work examples:

SELECT COUNT(email) as count FROM table1 t1 
JOIN (
      SELECT company_domains as emailext FROM table2 WHERE company = 'DELL'
     ) t2 
ON t1.email LIKE CONCAT('%', emailext) WHERE t1.event='PC Global Conference";

Task was count participants at an event(s) with filter if email extension equal to multiple company domains.