Follow Unfollow system

I am working on this site that follow and unfollow users just like twitter follow. The query below works then i notice it doesn't show me users i dont follow because the user has been followed by somebody or is following somebody

$stmt = $mysqli->prepare("SELECT * FROM town_users LEFT JOIN user_follow_list ON user_follow_list.following_id = town_users.user_id WHERE town_users.user_id != ? AND user_follow_list.follow_id IS NULL ORDER BY RAND() LIMIT 5");  $stmt->bind_param("i", $user_id);  

Below is my table structure

town_users  user_id username  | 1    | ben  | 2    | betty  | 3    | tom  | 4    | john      user_follow_list  follow_id         follower_id         following_id  |      1     |       1         |      2  |      2     |       1         |      3  

In the user_follow_list Ben is following betty and tom. Now john follows a user let say tom,

follow_id         follower_id         following_id  |      3     |       4         |      3  

Now the problem am having is that john doesn't show up on ben list of users to follow.

It's meant to show users i haven't followed so i can follow them. Thanks

https://stackoverflow.com/questions/67393943/follow-unfollow-system May 05, 2021 at 09:03AM