Want to add a count to the # of comments

Want to add a count to the # of comments - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Want to add a count to the # of comments (/showthread.php?tid=14212)

Want to add a count to the # of comments - El Forum - 12-22-2008

[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:
&lt;?php foreach($query->result() as $row): ?&gt;

    <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.

Want to add a count to the # of comments - El Forum - 12-22-2008

[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 !!!

Want to add a count to the # of comments - El Forum - 12-23-2008

[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.

Want to add a count to the # of comments - El Forum - 12-23-2008

[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:
&lt;?php if ($query->num_rows() >0): ?&gt;

&lt;?php foreach($query->result() as $row): ?&gt;

    <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