如何将内容加入用户表跟随用户帖子？

Question

I am trying to retrieve the posts from the people the current user is following along with their details. I've been researching at it seems a join is the right way to do it but I am not sure what is wrong with it.

Here is my code

$queryfeed = "SELECT posts.user_id, posts.body, posts.image, posts.has_image 
FROM posts, follows WHERE posts.user_id =  follows.leader_id 
AND follows.follower_id =$user_id INNER JOIN users ON users.user_id 
= follows.leader_id";

$resultfeed = $db->query($queryfeed);
if($resultfeed->num_rows > 0) {
    while( $rowfeed = $resultfeed->fetch_assoc() ) {       
        if($rowfeed['posts.has_image'] == 1) {
    ?>
            <article class="post">
                <div class="post-head cf">
                    <a class="userpic" href=""><img src="<?php echo $userpic ?>" alt="<?php echo $rowfeed['users.username'] ?>"></a>
                    <a href="" class="username">
                        <?php echo $rowfeed[users.'username']; ?>
                    </a>
                </div>
                <img src="users/user_<?php echo $rowfeed['posts.user_id'] ?>/posts/<?php echo $rowfeed['posts.image']; ?>" alt="">
                <div class="post-body">
                    <div class="post-options">
                        <a class="likes" href="">2 likes</a>
                    </div>
                    <p>
                        <a class="username" href="">
                                <?php echo $rowfeed['users.username'] ?>
                        </a>
                        <?php echo $rowfeed['posts.body'] ?>
                    </p>
                    <hr />
                    <div class="cf">
                        <a class="like hide-text" href="javascript:;">Like This Post</a>
                        <form action="" class="comment">
                            <input type="text" placeholder="Add a comment">
                        </form>
                    </div>
                </div>
            </article>

   <?php } else { ?>

My tables look like this

users
user_id | username | avatar

posts
post_id | user_id | body | image | has_image

follows
leader_id | follower_id

Answer 1

From your original query, I see that you are only interested in the posts' data (not that of the user who posted it). I assume that $user_id holds the id of the current user, i.e. a value that is also used as follower_id. If this is the case, the query gets easier:

SELECT p.user_id, p.body, p.image, p.has_image
FROM followers f INNER JOIN posts p ON f.leader_id = p.user_id and f.follower_id = $user_id

If you are interested also in the poster's user data, change the query as follows:

SELECT p.user_id, p.body, p.image, p.has_image, u.username, u.avatar
FROM follows f
  INNER JOIN posts p ON f.leader_id = p.user_id and f.follower_id = $user_id
  INNER JOIN users u ON p.user_id = u.user_id