PHP mysql how to relationship three tables showing outputs from different tables

facebook, mysql, php, sql

Solution

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.

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).

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

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

Problem

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

Original source