在PHP和MySQL中显示我的帖子以及关注者帖子

问题描述：

I'm trying to make a homepage somewhat like Facebook, I made it so it could show the posts from the people I follow, but I couldn't see my own posts as I can't follow myself. Here is the line of SQL code I've written (it contains PHP variables):

SELECT * 
FROM user_posts 
INNER JOIN user_following ON user_posts.username = user_following.username 
WHERE user_following.follower = '$me->username' 
ORDER BY id DESC 
LIMIT 0, 15

The user_posts table contains all the posts.
The user_following table contains all follow data, where username is the user being followed, and the follower is the user following the username
$me->username is the username of the user logged in.

user_posts table structure:

user_following table structure:

Thanks, in advance!

答

There's a couple of different ways to skin this query:

Sub-query

SELECT * 
FROM user_posts 
WHERE user_posts.username = 'bob'
OR user_posts.username IN(
  SELECT username 
  FROM user_following
  WHERE user_posts.username = user_following.username
)
LIMIT 0, 15

http://sqlfiddle.com/#!9/6bf2c6/9

Use the Users Table

Requires GROUP BY or DISTINCT user_posts.id, which are non-optimal.

SELECT
   user_posts.* 
FROM users
LEFT JOIN user_following ON users.username = user_following.username
INNER JOIN user_posts ON (
  users.username = user_posts.username
  OR user_following.follower = user_posts.username
)
WHERE users.username = 'bob'
GROUP BY user_posts.id
LIMIT 0, 15

http://sqlfiddle.com/#!9/d91be/1

IMPORTANT! Make sure and index those columns in your table. Otherwise, performance will suffer as the tables get bigger (especially user_following).

答

Try this code:

select * 
from user_posts up 
join user_following uf on up.username = uf.username
where uf.follower = '$me->username'
or up.username = '$me->username'

在PHP和MySQL中显示我的帖子以及关注者帖子

相关推荐