Ben Ben - 3 months ago 7
MySQL Question

PHP + MySQL WHERE EXISTS find_in_set returning all values

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!

Answer

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?

Comments