Display table rows with the per-row count of another table using CodeIgniter

Question

I have a view that displays some posts. Each post can have multiple comments and I want to display the total number of comments next to each post.

So far all my database calls are in my controller (I'll be changing this).

function index() {
    $data['query'] = $this->db->get('posts');
    $this->load->view('blog_view', $data);
}

In my view:

<?php foreach($query->result() as $row): 
      <div class="post-box">
          <p><?php echo $row->body; ?><small>&nbsp;added by <?php echo $row->username; ?> on  <?php echo date ('d/m/Y',strtotime($row->created)); ?>&nbsp;<a href="<?php echo base_url(); ?>blog/comments/<?php echo $row->id; ?>"><img src="<?php echo base_url(); ?>images/comments_icon.png" />&nbsp;0</a></small></p>
      </div>
<?php endforeach; ?>

I want to get the total number of comments where comment.post_id = the current record's id. and display it next to the comments icon.

UPDATE

Controller:

function index(){
    //load the model
    $this->load->model('City_model');

    //call the model method
    $data->posts = $this->City_model->get_posts();
    
    
    $this->load->view('blog_view', $data);
}

Model (city_model.php):

<?php

class City_model extends Model{

    function get_posts($id = NULL) {
        
        //define optional id for single post
        //if an id was supplied
        if ( $id != NULL ) {
            $this->db->where('id',$id);
        }

        // execute query
        $query = $this->db->get('posts');

        //make sure results exist
        if($query->num_rows() > 0) {
            $posts = $query->result();
        } else {
            return FALSE;
        }

        //create array for appended (with comments) posts
        $appended_posts_array = array();

        //loop through each post
        foreach ($posts as $post) {

            //get comments associated with the post
            $this->db->where('post_id', $post->id)
            $comments = $this->db->get('comments');

            //if there are comments, add the comments to the post object
            if($comments->num_rows() > 0) {
                $post->comments = $comments;
            }
            else {
                $post->comments = array();
            }

            //rebuild the returned posts with their comments
            $appended_posts_array[] = $post;

        }

        //if post id supplied, only return the single post object
        if ($id != NULL) {
            return $appended_registration_array[0];
        }
        else {
            return $appended_registration_array;
        }
    }
}

jondavidjohn · Accepted Answer · 2011-01-08 20:15:41Z

3

Here is an example model method that takes the approach I usually do for Items that have "sub" items...

I generally build them in a Multi-level array in the model like so...

Note: this model returns a post or all posts, complete with an array of associated comments accessable through the ->comments property.

function get_posts($id = NULL) {

    //define optional id for single post
    //if an id was supplied
    if ( $id != NULL ) {
        $this->db->where('id',$id);
    }

    // execute query
    $query = $this->db->get('posts');

    //make sure results exist
    if($query->num_rows() > 0) {
        $posts = $query->result();
    } else {
        return FALSE;
    }

    //create array for appended (with comments) posts
    $appended_posts_array = array();

    //loop through each post
    foreach ($posts as $post) {

        //get comments associated with the post
        $this->db->where('post_id', $post->id)
        $comments = $this->db->get('comments');

        //if there are comments, add the comments to the post object
        if($comments->num_rows() > 0) {
            $post->comments = $comments;
        }
        else {
            $post->comments = array();
        }

        //rebuild the returned posts with their comments
        $appended_posts_array[] = $post;

    }

    //if post id supplied, only return the single post object
    if ($id != NULL) {
        return $appended_registration_array[0];
    }
    else {
        return $appended_registration_array;
    }       
}

Now in the controller...

function posts() {

    //load the model
    $this->load->model('model_name');

    //call the model method
    $data->posts = $this->model_name->get_posts();

    //load the view
    $this->load->view('view/file', $data);

}

Then in the view you can use a nested foreach to loop through the posts AND the comments

<? foreach($posts as $post): ?>                //posts foreach start

    <h1><?= $post->title ?></h1>  //post title
    <p><?= $post->body ?></p>     //post body

    <? foreach($post->comments as $comment): ?>     //comments foreach start       
        <h3><?= $comment->author ?></h3>  //comment author
        <p><?= $comment->body ?></h3>     //comment body
    <? endforeach; ?>                               // end comments foreach

<? endforeach; ?>        // end posts foreach

Take note that once you build the posts array like I have shown in the model, for each $post item you have a $post->comments that is simply an array of comments that are associated with that post, so knowing that, you can call count($post->comments) in the controller or view to get the number of comments related to a single post.

So for your question about just displaying the count, in the view the only change is that you wouldn't loop through all the comments, you'd just do this instead...

<? foreach($posts as $post): ?>                //posts foreach start

    <h1><?= $post->title ?></h1>             //post title
    <p><?= count($post->comments) ?></p>     //comment count


<? endforeach; ?>        // end posts foreach

EDIT : I added the optional parameter $id to the model method so that if you want you can specify a single post if you wish, by passing it's id to the method. That way this same model method can be re-used to display single posts in detail with all comments displayed also.

edited Jan 8, 2011 at 20:15

answered Jan 8, 2011 at 17:37

jondavidjohn

62.5k21 gold badges120 silver badges159 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Colin Brock Over a year ago

@jondavidjohn: I deleted my original answer, as yours is more comprehensive and more in the MVC "spirit", so-to-speak. I agree that the OP should definitely opt for this type of solution over a "quick fix." +1 for taking the time to illustrate the proper approach.

iamjonesy Over a year ago

THis looks like just what I wanted. I'll give it a go!

iamjonesy Over a year ago

I tried your code. Looks like it should work fine. All the table names are correct. I've named the model correct. Added the class to the model, call the model correct but I'm getting a server error when I load the module. Module name: city_model.php calling it with $this->load->model('City_model');

jondavidjohn Over a year ago

first thing that jumps out at me is that you're capitalizing City_model when loading and using it, load it as city_model and use it as $this->city_model

iamjonesy Over a year ago

Sorry I wasn't notified of your reply. No I didn't as soon as I load the model, I get a server error. Capitalized or not :(

|

mickmackusa · Accepted Answer · 2024-08-22 07:01:59Z

In your Controller:

I think it is odd to have "blog" content in a "city" model, so you might consider renaming.
I always prefer to alias loaded models using StudlyCase -- it just better aligns with PSR standards. I feel snake_case just makes scripts look old and slightly more verbose.
For a "reading" request, the $id should be passed in via $_GET and can be most simply accessed as a lone parameter of the controller method.
I always type hint and endeavor to keep possible types narrow (incoming and outgoing).

public function index(?int $id = null): void
{
    $this->load->model('Blog_model', 'BlogModel');

    $this->load->view('blog_view', [
        'posts' => $this->BlogModel->getPostsWithCommentCount($id),
    ]);
}

In your Model:

With very few exceptions, variables should be passed into model methods as method parameters; try to keep the number of parameters minimal.
Only add a WHERE clause if the $id is not null or zero.
You don't need to perform iterated queries and therefore YOU SHOULDN'T.
Use a LEFT JOIN to relate the comments table and GROUP BY with COUNT() to append the total number of comments to each blog row.
You may wish to ->order_by('created', 'DESC') or another column for consistent presentation. If so, chain the new method after ->group_by() and before ->get().
There isn't a ->from() method call because the ->get() method is adding the FROM clause and executing the query.
This method will unconditionally return an array of zero or more objects.

class Blog_model extends CI_Model
{
    public function getPostsWithCommentCount(?int $id = null): array
    {
        if ($id) {
            $this->db->where('posts.id', $id);
        }
        return $this->db
            ->select('posts.*, COUNT(comments.id) total_comments')
            ->join('comments', 'comments.post_id = posts.id', 'LEFT');
            ->group_by('posts.id')
            ->get('posts')
            ->result();
    }
}

In your View:

If you don't like doing a lot of jumping in and out of <?php and ?> tags in views and think short tags are controversial, the next snippet will use a template string with passed in variables.

foreach ($posts as $post) {
    printf(
        '<div class="post-box">
             <p>%s <small>added by %s on %s <a href="%s"><img src="%s"> %s</a></small></p>
         </div>
        ',
        $post->body,
        $post->username,
        date('d/m/Y', strtotime($post->created)),
        base_url("blog/comments/$post->id"),
        base_url('images/comments_icon.png'),
        $post->total_comments
    );
}

Or if you prefer lots of opening and closing PHP tags, then use the following snippet.

<?php foreach ($posts as $post) { ?>
    <div class="post-box">
        <p>
            <?php echo $post->body; ?> 
            <small>
                added by <?php echo $post->username; ?> on <?php echo date('d/m/Y', strtotime($post->created)); ?>
                <a href="<?php echo base_url("blog/comments/$post->id"); ?>">
                    <img src="<?php echo base_url('images/comments_icon.png'); ?>"> 
                    <?php echo $post->total_comments; ?>
                </a>
            </small>
        </p>
    </div>
<?php } ?>

Collectives™ on Stack Overflow

Display table rows with the per-row count of another table using CodeIgniter

2 Answers 2

11 Comments

In your Controller:

In your Model:

In your View:

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

In your Controller:

In your Model:

In your View:

Comments

Your Answer

Sign up or log in

Post as a guest

Related