Output Count

[eluser]HSKrustofsky[/eluser]
I am trying to get and output a count of how many comments an entry has. I was looking at the User Guide, and tried to follow that as much as I could. Here is the code I attempted to come up with in my model:

Code:

function getCount() {
        $this->db->where('entry_id', $this->uri->segment(3));
        $query = $this->db->get('comments');
        $this->db->count_all_results();
        return $query->result();
    }

No number is outputed. All it says is "Array." Any sugestions?

[eluser]mddd[/eluser]
When you want a count, you use count_all_results() INSTEAD of get(). Not after it. Like so:

Code:

$this->db->where( ..... ); // your selection here
$this->db->from('table');
$count = $this->db->count_all_results(); // this makes a query, counts the results and gives the number to you!

[eluser]HSKrustofsky[/eluser]
Thanks for your help. It worked. I have one more question. I am trying to get it to where the number of comments is also displayed on the link before the page where the actual comments are displayed. For example, If someone viewing a list of items(blog posts, videos list, etc...), the number of comments is displayed there as well. I tried using the same code, but all I get in return is 0. Any suggestions?

[eluser]mddd[/eluser]
If you are getting a list of blog posts and their comments with them, you need to use a JOIN query. Check out the Myqsl manual for JOIN to understand this better.

Code:

$this->db->select('posts.*, count(comments.id) as num_comments'); // get the count of the number of different id's and call that 'num_posts'
$this->db->where('posts.category', 10); // any selection you need to make
$this->db->from('posts');
$this->db->join('comments', 'comments.post_id=posts.id', 'left'); // take the results from posts. match them up with comments based on post_id field
$this->db->group_by('posts.id'); // count those comments for each group where the group is defined by a single post id
$this->db->get();

This will give you a result with all the fields from 'posts' and an extra field 'num_comments' that contains the count.

[eluser]HSKrustofsky[/eluser]
Sorry if I sound stupid, but I tried doing what you posted, and can't seem to get it to work. Just to attempt to make sense of stuff, I have a site with videos on it. The videos(video embed code) is set in a table named videos which has an ID number. The comments are set in a table named comments, where it ID really has no meaning, but I have a row named entry_id which store the 3rd uri segment, which is the ID number of the video, from videos(table).

The first code you helped me on only worked to count the comments within the actual video's page, where the comments are displayed. Now, I have a page that has a list of videos(showing name, description, etc...) linked to the actual video, and I would like display the number of comments on the video list.

Following your code, this is what I did:

Code:

$this->db->select('videos.*, count(comments.entry_id) as num_comments');
$this->db->where('videos.id', $this->uri->segment(3));
$this->db->from('videos');
$this->db->join('comments', 'comments.entry_id = videos.id', 'left');
$this->db->group_by('videos.id');
$this->db->get();

It just comes out blank. Also, do I have to call/do anything special in the controller? Just wondering when to call num_comments, or is num_comments what I call on in the views page? Once again, sorry if I sound, or did anything stupid. Just doing something new in a project, and have little/no idea what to do in this instance...

[eluser]HSKrustofsky[/eluser]
Did a bit of tweaking. It still doesn't work, but I got it to display the number of comments. The thing is it is giving me the total number of comments(for every post). Here is what I have:

Code:

$this->db->select('id');
        $this->db->from('videos');
        $this->db->join('comments', 'comments.entry_id = videos.id', 'left');
        $query = $this->db->count_all_results();
        return $query;

Suggestions?

[eluser]mddd[/eluser]
count_all_results will give you the number of rows in the database results. In your query you are in fact getting ALL the comments. And then looking at how many results you got. It's logical that the number of results equals the total number of comments.

What you need to do for a listing, is get 1 row per video. That row will contain the details of the video, and the number of comments for THAT video.
This is exactly the query you have in your post of 26 aug 5:00pm. But of course you need to do something with the result of that query. So instead of

Code:

$this->db->get();

you need to do something with that result:

Code:

$query = $this->db->get();
$video = $query->row(); // not result, because there will be only one row. so we just get that row.
// results is now a record of the video, plus the number of comments in column num_comments.
echo $video->title; // shows you the title (if that's a field in your video table)
echo $video->num_comments; // number of comments for this video, counted using 'group by'

[eluser]HSKrustofsky[/eluser]
Did what you told me and now it's giving me the comments of every post for only 1 ID. I'm guessing the first(id of 1) one. I thought maybe I needed a foreach loop, but when I tried that, It gives me an error saying, something about trying to get something that is a non-object.

Here is what I got:

Model:

Code:

function getCount() {
        
        $this->db->select('videos.*, count(comments.id) as num_comments');
        $this->db->from('videos');
        $this->db->join('comments', 'comments.entry_id = videos.id');
        $this->db->group_by('videos.id');
        $query = $this->db->get();
        return $query->row();
        
    }

Controller:

Code:

function index() {
        
        $this->load->model('videos_model');
        $data['count'] = $this->videos_model->getCount();
        $this->load->view('videos', $data);
        
    }

View:

Code:

...
        <div class="totalComments">Comments: &lt;?php echo $count->num_comments; ?&gt;</div>
    ...

What should I do?

By the way, thank you very much for helping me out.I know I must be annoying, but this a learning experience for me.