March 11, 2012
mysql select data from a multivalue attribute
Question by user962449
How can I run a select query on a multivalue attribute? Does mysql have a function do select certain data from a multivalue field? Much help is appreciated. Here’s a sample of the problem:
Table
userid groups
-------------------
2 2,3,5
4 1
9 2,5,10
datatype is char(250) for groups
I want to do a query to select all userids that belong to group 5, in this example it would be userid 2 and 9. So:
userid
------
2
9
Any way to go about it with a mysql query? or with php/mysql?
Answer by Starx
In case the groups datatype is SET
You can use
SELECT * FROM users WHERE FIND_IN_SET('5', groups);
UPDATE
In case of char or varchar. You can use this
SELECT * FROM users
WHERE
groups LIKE '5,%'
OR groups LIKE '%,5'
OR groups LIKE '%,5,%'
OR groups = '5'
