显示关注者和登录用户的帖子

Question

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:
- Users (id, name, password)
- Posts (id, body, date, user_id)
- Followers (id, user_id, follower_id)

followers.follower_id = person who does the following
followers.user_id = person who is being followed
$_SESSION['id'] = id of the logged in user

DB::query('
SELECT users.name, posts.body 
FROM users, posts, followers 
WHERE posts.user_id = followers.user_id 
AND users.id = posts.user_id 
AND followers.follower_id = :userid', 
array(':userid'=>$_SESSION['id'])
);

But the query only shows posts from the users being followed, not from the logged in user. How do I fix this?

I have already created a relation between the posts table and the users table.

CREATE TABLE `posts` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `body` varchar(160) NOT NULL DEFAULT '',
  `user_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `posts_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Answer 1

You need to include 2 criteria in your WHERE clause: Either the creator of the post matches your :userid variable, or the follower matches your :userid.

Please use current join syntax: This makes your query a lot easier to read.

Your final query should be something like this (I'm excluding the PHP part):

SELECT users.name, posts.body
FROM posts
    INNER JOIN users ON
        users.id = Posts.user_id
    -- use left join to handle when a 
    -- user has no followers
    LEFT JOIN Followers ON
        Followers.user_id = users.user_id
WHERE
    users.id = :userid
    OR followers.follower_id = :userid