Want to add a count to the # of comments

[eluser]Brad Morse[/eluser]
I want to add the number of comments for each blog entry.

Here is my controller for the index that displays each entry with a link to view all the comments, which I want the # of comments to be next to the comments link.

Code:

function index() {

    $data['title'] = "My Blog Title";
    $data['heading'] = "My Blog Heading";
    //$data['query'] = $this->db->get('entries');
    
    $this->db->select('*');
    $this->db->from('entries');
    $this->db->join('authors', 'authors.id = entries.id', 'inner');
    //$this->db->join('user as user1', 'entries.author_id = authors.id');
    $data['query'] = $this->db->get();
    $this->load->view('blog_view', $data);
}

Here is the code for the view:

Code:

<?php foreach($query->result() as $row): ?>
    <h2>&lt;?=$row->title?&gt;</h2>

    <p>&lt;?=character_limiter("$row->body", 100);?&gt;</p>
    <p>Author: &lt;?=$row->first_name.' '.$row->last_name?&gt;</p>
    <p>&lt;?=anchor('blog/more/'.$row->id, 'Read on');?&gt;</p>
    <p>&lt;?=anchor('blog/comments/'.$row->id, 'Comments');?&gt;</p>
    <hr>
&lt;?php endforeach; ?&gt;

Right now it displays a link "Comments" which is linked to /blog/more/1 <-- 1 being the entry id, more being the more_view.php to display the entire entry w/ all the comments.

Which works great, but I want to add the # of comments next to the comments link that you see within the view code.

so it would go from "Comments" to "Comments (4)" <-- 4 being the number of comments that entry has.

The tables: comments.entry_id == entries.id

Any help is appreciated.

[eluser]Sarfaraz Momin[/eluser]
Hi... I did this sometime back... The query would look something like this.

masterLinks - Posts Table
countLinks - Comments Table

Code:

select masterLinks.masterLinkID, masterLinks.postUser,".
"masterLinks.postPic,masterLinks.postLink,".
"masterLinks.postID,masterLinks.postText,".
"masterLinks.postDate,masterLinks.postContent,masterLinks.postTitle,".
"substring(max(concat(countLinks.published,'|',countLinksID)),21) as countLinksID, ".
"count(countLinksID) as numCountLinks from masterLinks left join countLinks ".
"using(masterLinkID) group by masterLinkID order by masterLinkID;

I hope this helps.

Have a good day !!!

[eluser]moodh[/eluser]
or just add a comment_count column in your blog_entry table, sure it's abit of redundancy in your database but it's hellofalot easier (and faster) to access than the code above me, and then simply add comment_count = comment_count+1 when you add a new comment.

[eluser]Unknown[/eluser]
You could modify your query to find the number of comments (called comment_count for this example)

Below are modified examples of the code you posted.

controller:

Code:

function index(){
  $data['title'] = "My Blog Title";
  $data['heading'] = "My Blog Heading";                
  $sql = "SELECT a.id, a.body, a.title, a.first_name, a.last_name, COUNT(b.entry_id) AS comment_count FROM entries a JOIN comments b ON a.id = b.entry_id GROUP BY a.id";
  $data['query'] = $this->db->query($sql);        
  $this->load->view('blog_view', $data);
}

view:

Code:

<?php if ($query->num_rows() >0): ?>
<?php foreach($query->result() as $row): ?>
    <h2>&lt;?=$row->title?&gt;</h2>

    <p>&lt;?=character_limiter("$row->body", 100);?&gt;</p>
    <p>Author: &lt;?=$row->first_name.' '.$row->last_name?&gt;</p>
    <p>&lt;?=anchor('blog/more/'.$row->id, 'Read on');?&gt;</p>
    <p>&lt;?=anchor('blog/comments/'.$row->id, 'Comments');
        echo ' ('.$row->comment_count.')'; ?&gt;</p>
    <hr>
&lt;?php endforeach; ?&gt;
&lt;?php endif; ?&gt;

You would probably want your query to eventually grab (JOIN) the first and last or alias from your users table instead of storing it for each entry

Hope this helps