CodeIgniter - joining users, posts, comments tables

im joining 3 tables: users, posts and comments in the following code :

    public function join_user_post(){
        $this->db->select('
        posts.post_body,
        posts.date_created,
        users.username,
        users.user_image,   
        comments.comment_body,
        comments.date_created
    ');
        $this->db->from('posts');
        $this->db->join('users', 'users.id = posts.post_user_id ');
        $this->db->join('comments', 'comments.comment_post_id = posts.post_id ');
        $query = $this->db->get();
        return $query->result();
    }

then passing this array data to the homepage view via controller:

<?php
class Home extends CI_Controller{

        public function index(){
            $this->load->model('user_model');
            $data['posts'] = $this->user_model->join_user_post();
            $this->load->view("user/homepage",$data);
        }
}
?>

in the homepage view im trying to echo the post with the username and user image, then looping through comments of each post and echo it out

    <body>
    <?php include 'navbar.php';?>

    <?php foreach($posts as $post): ?>
        <div>
            <img src="<?php echo $post->user_image ?><br>">
            <?php echo $post->username ?><br>
            <?php echo $post->post_body ?><br>
            <?php echo $post->date_created ?><br>

            <?php foreach($posts as $post): ?>
                <?php echo $post->comment_body ?><br>
                <?php echo $post->date_created ?><br>


            <?php endforeach; ?>

        </div>

    <?php endforeach; ?>
    </body>

but im rather getting all comments on every post instead of getting the comments that are related to specific post id, the foreach is looping through all comments and echo them on every post, if i remove the foreach loop it will echo only one comment at each post what should i do to solve this issue?

Solution

The problem is when you join posts with comments it return many records of comments with the same post. You can modify a little bit first in your controller to store each post with comments belong to it:

function index()
{
    $this->load->model('user_model');
    $comments = $this->user_model->join_user_post();
    $posts = array();
    foreach ($comments as $comment) {
        if (array_key_exists($comment->post_id, $posts)) {
            $posts[$comment->post_id]['comments'][] = $comment;
        } else {
            $posts[$comment->post_id]['post_body'] = $comment->post_body;
            $posts[$comment->post_id]['username'] = $comment->username;
            $posts[$comment->post_id]['date_created'] = $comment->date_created;
            $posts[$comment->post_id]['user_image'] = $comment->user_image;
            $posts[$comment->post_id]['comments'][] = $comment;
        }
    }
    $data['posts'] = $posts;
    $this->load->view("user/homepage", $data);
}

And in your view:

<div>
    <img src="<?php echo $post['user_image'] ?><br>">
    <?php echo $post['username'] ?><br>
    <?php echo $post['post_body'] ?><br>
    <?php echo $post['date_created'] ?><br>

    <?php foreach ($posts['comments'] as $comment) : ?>
        <?php echo $comment->comment_body ?><br>
        <?php echo $comment->date_created ?><br>
    <?php endforeach; ?>

</div>