PHP + MySQL WHERE EXISTS find_in_set returning all values

Question

I have the following table structure:

For uc_users:

id  |  following | Name
------------------------
1   |  2,3,      | Bill
2   |  1,3,      | Bob
3   |  1,        | Dan

I want to find out who user id 1 is following.

I have done the following:

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

But it's giving me all 3 users where it should only give users 2 and 3.

Can anyone help? Thanks!

score 1 · Accepted Answer · edited May 23 '17 at 12:01

1

Try this:

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

Change: FIND_IN_SET(p.id,following) > 0

Storing delimited list is too bad. Instead you should store record for each <id,follower> pair.

Is storing a delimited list in a database column really that bad?

edited May 23 '17 at 12:01

Community

1
1

answered Aug 06 '16 at 14:46

1000111

13,169
2
28
37

That did it. Thanks! I thought it was something simple I'm just new to this particular area. Thought I might have to take a downvote in order to ask. – Ben Aug 06 '16 at 14:48
Whoever downvoted your question **storing delimited list in column** might be the reason behind.@Ben – 1000111 Aug 06 '16 at 14:52
Yes I understand it's considered bad practise, however for my current situation it's fine. Sorry I normally do @1000111, it's annoying they make you wait before doing so. Thanks again. – Ben Aug 06 '16 at 20:38

PHP + MySQL WHERE EXISTS find_in_set returning all values

1 Answers1