CODEWITHSUNDEEP
phpmysql
caner taşdemir
caner taşdemir

Reputation: 203

Select posts from users which are followed

I have spent hours and still couldn't able to do it correctly. I have user table, and posts table.

User table

username | followers        | following

john     | mary,steven,joel | anthony,matthew

Posts table

fromuser | post                    | date 

mary     | Bla bla, today is cold. | 1475982647

I need to get posts from the users who are being followed. Tried this;

SELECT * FROM posts WHERE $member[nick] 
IN (SELECT followers FROM users WHERE $member[nick] IN followers ORDER BY id DESC limit 10

This doesn't return any posts. What is the correct way to do it ?

Upvotes: 1

Views: 57

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

SELECT *
FROM posts
WHERE FIND_IN_SET(fromuser,
                  (SELECT following FROM users WHERE username = 'john')) > 0

This query uses FIND_IN_SET to check each user appearing in posts whether or not he is being followed by a given user (John in this case).

Upvotes: 1

Related Questions