selecting all rows where a column has an specific ID

Question

So I have a badly designed database (I think) which I can't change. It's a twitter like app where the users can follow each other. Every user has it`s row in the table, and in that table there is a column named 'following' which represents all USERID's that a user is following. In that column there is a list of USERID's separated with a coma. So lets say the user with the ID 1 is following users 2 and 3 and the user with ID 2 is following user 1 the table would look like this, user 3 is not following anybody.

USERID  |  username         |  following  
-------------------------------------------
1       |  some user        |  2,3                
2       |  test1            |  1                
3       |  test2            |

Question is how do I show all the users which user 1 is following?

EDIT 1

The code that did not work from 491243, posting here, maybe I missed something in php

$USERID = $_GET['userid'];//this has a value, so not the problem here
$sql_select = "SELECT B.USERID FROM users A INNER JOIN users B ON FIND_IN_SET(B.USERID, B.following) > 0 WHERE B.USERID = '$USERID'";
$result_select = mysqli_query($link,$sql_select);
while($record = mysqli_fetch_array($result_select))
{
    $following = $record['USERID'];
    var_dump($following); //result is nothing, not even NULL
}

EDIT 2 Just for sanity check I did this:

 $sql_select = "SELECT USERID FROM users WHERE USERID = '1'";
 $result_select = mysqli_query($link,$sql_select);
 while($record = mysqli_fetch_array($result_select))
 {
    $following = $record['USERID'];
    var_dump($following); //result is 1, like it`s supposed to be
 }

Is it possible my PHP code is wrong for the query in the replays?

Why is it that people responsible for such a bad design never have to actually use it... — , Sep 15 '13 at 13:30

score 5 · Answer 1 · answered Sep 15 '13 at 13:08

5

Your table schema is in bad shape. you should normalize it properly. But to answer you question, you can still get the result you want using JOIN and FIND_IN_SET

SELECT  b.userid, b.username
FROM    tableName a
        INNER JOIN tableName b
            ON FIND_IN_SET(b.userID, a.following) > 0
WHERE   a.userID = 1

SQLFiddle Demo

My preferred design would be

User Table

UserID (PK)
UserName

Following Table

UserID (FK) - also a PK with FollowID
FollowID (FK)

answered Sep 15 '13 at 13:08

John Woo

258,903
69
498
492

Ty you for the db structure suggestion I wanted to ask that (how to do this better). But for now I need this. Your answer does not work, I do var_dump and get nothing from the query. – Vladimir Szabo Sep 15 '13 at 14:27
but it works on the fiddle right? maybe you have not correctly implemented it. – John Woo Sep 15 '13 at 14:34

Praveen Prasannan · Accepted Answer · 2013-09-15T15:39:58.647

1

You might be looking for FIND_IN_SET()

SELECT  userid, username
FROM    tableName
WHERE FIND_IN_SET('1', following);

SAMPLE FIDDLE

edited Sep 15 '13 at 15:39

answered Sep 15 '13 at 14:34

Praveen Prasannan

7,093
10
50
70

This is crazy, I see it`s working in fiddle and it does not work for me in localhost... – Vladimir Szabo Sep 15 '13 at 14:45
can you post the original data which is not getting in your machine – Praveen Prasannan Sep 15 '13 at 15:09
Is it possible the problem is the MySQL version. My wamp has 5.5.24 and the fiddle uses 5.5.32? – Vladimir Szabo Sep 15 '13 at 15:20
Awesome it`s working now, I am marking your answer as correct. Although I am not sure why it`s not working the previous way in my localhost when it did work in fiddle. Anyway tnx. And thanks for the all the other answers. – Vladimir Szabo Sep 15 '13 at 18:16

selecting all rows where a column has an specific ID

2 Answers2