php, mysql single query for multipe columns distinct values

Question

Is it possible to retrieve records using single query in case like:

id  title         tags
1   First         yellow,blue
2   Second        green, yellow,red,
3   Third         black,purple

What I would like to do is select all records where keyword yellow appears. The result should return two records "First and Second"

Try select * from table where tags like '%yellow%'

Gowri
– Gowri

2015-01-17 03:58:13 +00:00
Commented Jan 17, 2015 at 3:58 — Gowri
– Gowri, Commented Jan 17, 2015 at 3:58
why isn't a proper 1-N structure used here?

Schien
– Schien

2015-01-17 04:06:28 +00:00
Commented Jan 17, 2015 at 4:06 — Schien
– Schien, Commented Jan 17, 2015 at 4:06

Girish · Accepted Answer · 2015-01-17 04:08:50Z

2

Better to use REGEX to get exact search

SELECT * FROM tblname WHERE 
    tags REGEXP '[[<:]]yellow[[:>]]'

Or you can also use FIND_IN_SET() function

SELECT * FROM tblname WHERE 
    FIND_IN_SET('yellow', tages) > 0

NOTE: FIND_IN_SET() function won't work correctly if tags not symmetric comma separated, if tags have white space between , then it would create problem

edited Jan 17, 2015 at 4:08

answered Jan 17, 2015 at 4:01

Girish

12.1k3 gold badges37 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3176519 Over a year ago

FIND_IN_SET() works perfectly. All my tags are imploded before being stored.

Collectives™ on Stack Overflow

php, mysql single query for multipe columns distinct values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related