CodeIgniter关联users、posts、comments表后，视图中评论无法与对应帖子匹配的问题求助

Hey there, let's break down why your comments are showing up under every post, and fix this properly.

The Root Problem #

Your current JOIN query returns a flattened result set—every comment creates a duplicate row of its parent post and user data. For example, if a post has 3 comments, you'll get 3 identical post entries (each paired with one comment) in your results.

When you loop through $posts twice (outer for posts, inner for comments), the inner loop is iterating over the entire flattened set—so every comment gets printed under every post. If you remove the inner loop, you only see one comment per post because each row is a post+single comment pair.

Solution 1: Restructure Data in the Model (Most Efficient) #

We'll modify the model to group comments under their respective posts, turning the flat result into a nested structure where each post has its own comments array.

Update Your Model Method

public function join_user_post(){
    // Add aliases to date_created to avoid field conflicts!
    $this->db->select('posts.post_id, posts.post_body, posts.date_created as post_date, users.username, users.user_image, comments.comment_id, comments.comment_body, comments.date_created as comment_date');
    $this->db->from('posts');
    // Use LEFT JOIN to keep posts that have no comments
    $this->db->join('users', 'users.id = posts.post_user_id ', 'left');
    $this->db->join('comments', 'comments.comment_post_id = posts.post_id ', 'left');
    
    $query = $this->db->get();
    $raw_results = $query->result();

    // Restructure the data to group comments by post
    $organized_posts = [];
    foreach($raw_results as $row){
        $post_id = $row->post_id;
        
        // Initialize the post entry if it doesn't exist
        if(!isset($organized_posts[$post_id])){
            $organized_posts[$post_id] = [
                'post_id' => $row->post_id,
                'post_body' => $row->post_body,
                'post_date' => $row->post_date,
                'username' => $row->username,
                'user_image' => $row->user_image,
                'comments' => []
            ];
        }
        
        // Add comment only if it exists (LEFT JOIN might return null)
        if(!is_null($row->comment_id)){
            $organized_posts[$post_id]['comments'][] = [
                'comment_body' => $row->comment_body,
                'comment_date' => $row->comment_date
            ];
        }
    }

    // Convert associative array to indexed array for easier looping
    return array_values($organized_posts);
}

Update Your View

Now each post in $posts has a comments array—loop through that to show only the comments for the current post:

<body>
<?php include 'navbar.php';?>
<?php foreach($posts as $post): ?>
<div class="post-container">
    <img src="<?php echo $post['user_image']; ?>"><br>
    <strong><?php echo $post['username']; ?></strong><br>
    <p><?php echo $post['post_body']; ?></p>
    <small>Posted on: <?php echo $post['post_date']; ?></small><br>
    
    <!-- Display comments for this specific post -->
    <div class="comments-section" style="margin-left: 2rem; margin-top: 1rem;">
        <?php if(!empty($post['comments'])): ?>
            <?php foreach($post['comments'] as $comment): ?>
                <div class="comment">
                    <p><?php echo $comment['comment_body']; ?></p>
                    <small>Commented on: <?php echo $comment['comment_date']; ?></small><br>
                </div>
            <?php endforeach; ?>
        <?php else: ?>
            <p>No comments yet—be the first to comment!</p>
        <?php endif; ?>
    </div>
</div>
<hr> <!-- Separate posts with a horizontal rule -->
<?php endforeach; ?>
</body>

Solution 2: Query Posts and Comments Separately (More Flexible) #

If your site doesn't have a huge number of posts, you can first fetch all posts (with user data), then fetch comments for each post individually. This keeps your logic straightforward.

Add These Methods to Your Model

// Get all posts with associated user data
public function get_posts_with_users(){
    $this->db->select('posts.post_id, posts.post_body, posts.date_created as post_date, users.username, users.user_image');
    $this->db->from('posts');
    $this->db->join('users', 'users.id = posts.post_user_id ');
    return $this->db->get()->result_array();
}

// Get comments for a specific post ID
public function get_comments_for_post($post_id){
    $this->db->select('comment_body, date_created as comment_date');
    $this->db->from('comments');
    $this->db->where('comment_post_id', $post_id);
    return $this->db->get()->result_array();
}

Update Your Controller

<?php
class Home extends CI_Controller{
    public function index(){
        $this->load->model('user_model');
        
        // Fetch all posts first
        $posts = $this->user_model->get_posts_with_users();
        
        // Attach comments to each post
        foreach($posts as &$post){
            $post['comments'] = $this->user_model->get_comments_for_post($post['post_id']);
        }
        
        $data['posts'] = $posts;
        $this->load->view("user/homepage",$data);
    }
}
?>

You can reuse the same view from Solution 1 here!

Key Takeaways #

• Field Aliases: Always alias duplicate field names (like date_created in posts/comments) to avoid overwriting data.
• LEFT JOIN: Use this instead of the default INNER JOIN to ensure posts without comments still show up.
• Nested Data Structure: Converting flat query results to nested arrays makes it trivial to display related content correctly.

内容的提问来源于stack exchange，提问作者Aasem Shoshari