PHP mysql如何关联三个表显示来自不同表的输出

I have this diagram

我有这个图

What I wanna do is have this output:

我想做的是有这个输出：

How do you manage to do the query of this one?

你如何设法查询这个？

I have this code

我有这个代码

SELECT users.firstname, users.lastname, 
       users.screenname, posts.post_id, posts.user_id,
       posts.post, posts.upload_name, 
       posts.post_type, posts.date_posted
FROM website.users users
INNER JOIN website.posts posts ON (users.user_id = posts.user_id)
ORDER BY posts.pid DESC
//PROBLEM with this one is that it only views the post from all users.

//SO I added
SELECT COUNT(user_id) AS friends, SUM(user_id = ?) AS you, user_id
FROM feeds WHERE post_id = ?
//This one will give you two fields containing how many different users **feeds** the
post

Please help guys. Actually this one I am just following Facebook's "LIKE" status the only thing is I'm not an amateur with this kind of stuff so I'd be glad to hear all your answers. I really need your help

请帮帮我们实际上这个我只是跟随Facebook的“LIKE”状态唯一的事情就是我不是这类东西的业余爱好者所以我很高兴听到你所有的答案。我真的需要你的帮助

2 个解决方案

#1

If I've understood you correctly, you want an outer join with the feeds table (in order to retain all posts even if there are no associated feeds), then GROUP BY post.pid in order to amalgamate together all such feeds for each post, and SELECT the desired information.

如果我理解正确，你想要一个带有feed表的外连接（为了保留所有帖子，即使没有相关的提要），然后GROUP BY post.pid，以便将每个帖子的所有这些提要合并在一起，并选择所需的信息。

I use MySQL's GROUP_CONCAT() function to obtain a comma-separated list of all users (up to group_concat_max_len) who have a "feed" for the given post (you can change the delimiter with the SEPARATOR modifier, if so desired).

我使用MySQL的GROUP_CONCAT（）函数来获取所有用户（最多为group_concat_max_len）的逗号分隔列表，这些用户具有给定帖子的“提要”（如果需要，您可以使用SEPARATOR修饰符更改分隔符）。

SELECT users.firstname, users.lastname, 
       users.screenname, posts.post_id, posts.user_id,
       posts.post, posts.upload_name, 
       posts.post_type, posts.date_posted,
       COUNT(feeds.user_id) AS friends,   -- number of "likes"
       SUM(feeds.user_id = ?) AS you,     -- did I like this?
       GROUP_CONCAT(feeds.user_id)        -- who likes it?
FROM website.users users
INNER JOIN website.posts posts ON (users.user_id = posts.user_id)
LEFT  JOIN website.feeds feeds ON (posts.post_id = feeds.post_id)
GROUP BY posts.pid
ORDER BY posts.pid DESC

---

UPDATE

UPDATE

To obtain the full name of users who have "liked" the post, excluding oneself, one needs to join the users table a second time:

要获得“喜欢”帖子的用户的全名，不包括自己，需要第二次加入用户表：

SELECT users.firstname, users.lastname, 
       users.screenname, posts.post_id, posts.user_id,
       posts.post, posts.upload_name, 
       posts.post_type, posts.date_posted,
       COUNT(feeds.user_id) AS friends,                      -- number of "likes"
       SUM(feeds.user_id = ?) AS you,                        -- did I like this?
       GROUP_CONCAT(
         CASE WHEN NOT likes.user_id = ? THEN                -- exclude self
           CONCAT_WS(' ', likes.firstname, likes.lastname)   -- full names
         END
       )
FROM website.users users
INNER JOIN website.posts posts ON (users.user_id = posts.user_id)
LEFT  JOIN website.feeds feeds ON (posts.post_id = feeds.post_id)
LEFT  JOIN website.users likes ON (feeds.user_id = likes.user_id)
GROUP BY posts.pid
ORDER BY posts.pid DESC

#2

If You want to do it for all the users and simultaneously get the feeds, You have to join this feed table:

如果您想为所有用户执行此操作并同时获取Feed，则必须加入此Feed表：

SELECT u.firstname, u.lastname, 
   u.screenname, p.post_id, p.user_id,
   p.post, p.upload_name, 
   p.post_type, p.date_posted,
   COUNT(f.user_id) AS friends, SUM(f.user_id = ?) AS you
FROM website.users u
INNER JOIN website.posts p ON (u.user_id = p.user_id)
LEFT  JOIN website.feeds f ON (p.post_id = f.post_id)
GROUP BY p.pid
ORDER BY p.pid DESC

This one should do the trick...

这个应该做的伎俩......