Ben Ben - 4 months ago 15
PHP Question

MySQL + PHP WHERE IN (list of numbers) only working for first number in list

I'm running the following in MySQL/PHP

SELECT * FROM `uc_posts` WHERE `postinguser` IN (SELECT `following` FROM `uc_users` WHERE `id` = 1) ORDER BY id DESC LIMIT 20


The value of
following
is
1,2


However for some reason it is only displaying rows where the
postinguser
is
1


To confirm when I run:

(SELECT `following` FROM `uc_users` WHERE `id` = 1)


It returns the cell
following
with the content
1,2


What's strange is when I run this:

SELECT * FROM `uc_posts` WHERE `postinguser` IN (1,2) ORDER BY id DESC LIMIT 20


It returns the desired result with rows with both
postinguser
1
and
2


The column
following
is structured as
varchar(255) utf8_general_ci


Can you see what is wrong here?

Thanks.

Answer

You should not store lists of numbers in a single column as a delimited list. SQL has a great data structure for storing lists; it is called a table, not a string column.

In your case, the value '1,2' is exactly what it looks like: a single string. It will match another string that has three characters, a one, a comma, and a two.

Sometimes, we are stuck with other people's bad design decisions. If so, you could write you condition as:

SELECT p.*
FROM `uc_posts` p
WHERE EXISTS (SELECT 1
              FROM `uc_users`
              WHERE `id` = 1 AND find_in_set(postinguser, following) > 0
             )
ORDER BY id DESC
LIMIT 20;

However, I would encourage you to change your data structure so you can use a regular JOIN. The performance is much better when you use the proper data structures.