CODEWITHSUNDEEP
mysql
Kiran
Kiran

Reputation: 13

check comma separated field against comma separated value in mysql

A table field has values has 1,2,3 (comma seperated value) and a variable has 2,3. Now i need to check weather variable value is in table field using query along with another name field

user table

id name   cat_id
-----------------
1  test   1,2,3
2  test1  3,4
3  test2  4

variable $value = 2, 3

Query : select * from user where name='test' and cat_id IN ('".$value."')

but for above query i get zero data

How to check if given id is exist in cat_id field and name exist in table

Upvotes: 0

Views: 334

Answers (2)

Nick
Nick

Reputation: 147216

You can use a regex to check whether the value is contained in cat_id:

SELECT * FROM user WHERE name='test' AND cat_id REGEXP CONCAT('[[:<:]]', value, '[[:>:]]')

this will attempt to match value at any word boundary in cat_id, so for cat_id='1,2,3', values of (for example) '1,2', '2', '2,3' will match.

To put it in a string form (e.g. for PHP):

$sql = "SELECT * FROM user WHERE name='test' AND cat_id REGEXP CONCAT('[[:<:]]','" . $value. "', '[[:>:]]')";

Upvotes: 1

S&#233;bastien S.
S&#233;bastien S.

Reputation: 1544

Your cat_id field should contain only one id by row.

It's normal that your SQL request doesn't work currently because you're looking for cat_id 2 or 3 which SQL is not finding.

For example in your first row 1,2,3, for MySQL it's a string "1,2,3" and not an array of three ids.

If a name can be used by several cats maybe they should be the ones having a name_id.

And if a cat can have several names, and a name can have several cats, you should create a new table cats_names containing one name_id and one cat_id by row.

Upvotes: 0

Related Questions