Querying comma separated field, array!!

love_bug

Hi,
This is an unusual problem I have..

I have a field named "recipient_userids" where I store ids of users in a comma separated form,
sucha as "3323,5543,53,3235,23"

Now I want to select records from db, which matches a particular userid in "recipient_userids".
such as.. If I want to retrieve a record that has 53 in "recipient_userids" how do I do it?
I tried LIKE, IN syntax, but not doing the job.

OR is there a way to do it please share.

laserlight

Instead of storing the comma separated list of ids, store each id in its own row in a table. After all, you have a many to many relationship here.

By the way, you might want to read the PHPBuilder article on Database Normalization and Design Techniques.

love_bug

thanks.. i knew it wouldn;t work...

bogu

You should do what laserlight suggested, but if you cant do that you may use MySQL REGEXP:

SELECT
	*
FROM `table`
WHERE `recipient_userids` REGEXP '(^|,)53(,|$)'

But I'll also reiterate; normalizing your DB schema will make it much easier to relate data and perform tasks like the one you're trying to do.

bradgrafelman

You should do what laserlight suggested, but if you cant do that you may use MySQL REGEXP:

SELECT
	*
FROM `table`
WHERE `recipient_userids` REGEXP '(^|,)53(,|$)'

But I'll also reiterate; normalizing your DB schema will make it much easier to relate data and perform tasks like the one you're trying to do.