MySQL Query JOINS are not retrieving fully

On my website I am trying to program a feature, similar to facebook and twitters timeline, where a user can 'follow' another user, and receive their 'broadcast'. The database tables are:

**members**
--------
id
fullname
following

**broadcasts**
-----------
id
mem_id
broadcast (the content)
broadcast_date

"following" in the members table is a varchar text that stores user ID's. So if I'm following users 4 5 and 6 (4,5,6,) would appear in the following column. My problem is, what I'm querying from the database, it's only retrieving the 'broadcasts' from the first user that I am following and no other user else.

$sql_broadcasts = mysql_query("
    SELECT *
    FROM members 
        JOIN broadcast 
            ON(broadcast.mem_id = members.following)
    WHERE members.id=$id
    ORDER BY broadcast_date DESC
    LIMIT 10;
");开发者_StackOverflow中文版

where $id is $_SESSION['id']. I've been staring at this code for a long time, can anyone spot what I'm doing wrong? thanks in advance

Joins do not work that way.

Assuming that broadcast.mem_id is a numeric type the database will silently cast members.follower_array; this will make 4 from 4,5,6, so one record matches.

You will need a m:n relation for this to work, one table with members (which you already have); and another table with followers, for each follower you insert a record which contains the member id of the member who is being and the member id of the member who is following. That way a member can have 0-n followers.

Table member
id

Table follower
member_id
member_id_follower

Then you can do something like

SELECT
  ...
FROM
   member AS mb
JOIN
   follower AS fl ON fl.member_id = mb.id
WHERE
   mb.id = 123

First of all you have to get all following ids in sub query and passing it to parent one!

$sql_broadcasts = mysql_query("SELECT *
FROM broadcast where mem_id IN( Select following from members where id = $id)
ORDER BY broadcast_date DESC
LIMIT 10;
");

where following filed contains all following ids comma split e.g 4,5,6