如何从多个用户一起从两个不同的表中获取数据？

Question

I have 1 table for the user, 1 for comments and another for friends below is the table structure :

Comments Table :

------------------------------------------------
| comment_id  | user_id_c | commentstatus      |
------------------------------------------------
|  1          |   1       |   Sample comment 1 |
|  2          |   2       |   Sample comment 2 |
------------------------------------------------

User Table :

------------------------------------------------------
| id  | username            | password  |  Full name |
------------------------------------------------------
|  1  |   user1 (loggedin)  | Sample 1  |  John      |
|  2  |   user2             | Sample 2  |  Smith     |
|  3  |   user3             | Sample 3  |  Andrew    |
|  4  |   user4             | Sample 4  |  Victor    |
|  3  |   user5             | Sample 3  |  Robert    |  
-------------------------------------------------------

Friends Tables

---------------------------------------------------
| id  | friend1             | friend2   |   status   
---------------------------------------------------
|  1  |   user1             | user3     |  friend
|  2  |   user1             | user5     |  friend
|  3  |   user2             | user4     |  friend
---------------------------------------------------

currently, if any user posts a comment it gets inserted there in the database inside comments table. now if I am logged in I can fetch all my comments from the database.

To explain I would like to inform that you all can see, user 1 is friend with user 3 and user 5 according to the friends table.

All I want is to fetch the comments for the logged in user and for those who are the friend with the logged in user. however, I am unable to figure it out that how can I fetch the comments of loggedin user and it's friends all together.

the script I am using to fetch my posts is as follows :

public function comments(){
            global $pdo;
            $query = $pdo->prepare("SELECT u.*, c.* FROM users u INNER JOIN comments c ON u.id = c.user_id_c WHERE u.id = ".$_SESSION['sid']." ORDER BY c.comment_id DESC");
$query->execute();
            return $query->fetchAll();
}

and then fetching comments using below code :

$comments  = $get->comments();

Then :

foreach($comments as $row){
 
echo $row['Full name']; //user name from user table in the database
echo $row['commentstatus']; //comment from comments table in the database

It is fetching data for the logged in user perfectly, Is it possible anyway that I can fetch the comments from the friends of the logged in user also with logged in user's comments from the database at the same time.

Answer 1

You are looking for something like this:

$query = $pdo->prepare("SELECT u.*, c.* FROM users u INNER JOIN comments c ON u.id = c.user_id_c WHERE u.id = ".$_SESSION['sid']." OR c.user_id IN (SELECT friend_id FROM friends WHERE friends.user_id = ".$_SESSION['sid'].") ORDER BY c.comments_id DESC"