CODEWITHSUNDEEP
phpmysql
Azixy_codes
Azixy_codes

Reputation: 123

How to display posts from followers and logged-in user in news feed?

I want to display posts from the users being followed and the logged in user using a single PHP MySql query.

I have three tables:

I have variable

$id = $_SESSION['user_id']

$que = $db->prepare("SELECT posts.*, users.*, followers.follow, followers.followed_by FROM posts 
                    INNER JOIN users ON users.user_id = posts.added_by_user_id
                    LEFT JOIN followers ON followers.follow = posts.added_by_user_id
                    WHERE (posts.added_by_user_id = $id OR followers.followed_by = $id) ORDER BY post_id DESC");
                $que->execute();
                $posts = $que->fetchAll();

But the query shows each post created by logged-in user twice.

Upvotes: 0

Views: 99

Answers (1)

The concise
The concise

Reputation: 452

You can use any of the following:

Add additional clause to you LEFT JOIN followers and it will do the trick i.e LEFT JOIN followers ON ( followers.follow = posts.added_by_user_id AND followers.followed_by = $id ) . Note the AND

$que = $db->prepare("SELECT posts.*, users.*, followers.follow, followers.followed_by FROM posts 
INNER JOIN users ON users.user_id = posts.added_by_user_id
LEFT JOIN followers ON ( followers.follow = posts.added_by_user_id 
 AND followers.followed_by = $id ) 
   WHERE (posts.added_by_user_id = $id OR followers.followed_by = $id) 
GROUP BY( posts.post_id ) 
ORDER BY post_id DESC");
 $que->execute();
 $posts = $que->fetchAll();

Anotber way using your code, just add DISTINCT after SELECT. I.e SELECT DISTINCT posts.* .....

Another way is using sub query

$que = $db->prepare("SELECT posts.*, users.* FROM posts 
INNER JOIN users ON users.user_id = posts.added_by_user_id 
WHERE posts.added_by_user_id= $id OR posts.added_by_user_id IN (SELECT follow FROM followers WHERE followed_by= $id) 
ORDER BY post_id DESC");
    $que->execute();
     $posts = $que->fetchAll();

You can also use the below. The key function is GROUP BY

$que = $db->prepare("SELECT posts.*, users.*, followers.follow, followers.followed_by FROM posts 
                    INNER JOIN users ON users.user_id = posts.added_by_user_id
                    LEFT JOIN followers ON followers.follow = posts.added_by_user_id
                    WHERE (posts.added_by_user_id = $id OR followers.followed_by = $id) 
GROUP BY( posts.post_id ) 
ORDER BY post_id DESC");
 $que->execute();
 $posts = $que->fetchAll();

(I'm not too sure how efficient this is)

Upvotes: 0

Related Questions