jnbn jnbn - 7 months ago 23
PHP Question

Mysql: Getting Count of Comma Separated Values With Like

I decided to use favs (id's of users which marked that post as a favorite) as a comma separated list in a favs column which is also in messages table with sender,url,content etc..

But when I try to count those rows with a query like:

select count(id)
from messages
where favs like '%userid%'


of course it returns a wrong result because all id's may be a part of another's

For example while querying for id=1 it also increase the counter for any other content that is favorited by user id 11...

Can you please tell me your idea or any solution to make this system work?

Answer

With a few or's, you can have an ugly solution:

select count(id) from messages where favs like 'userid,%' or favs like '%,userid,%' or favs like '%,userid'

There's likely a more elegant solution, but that one will at least return the result you're looking for, I believe.