Welcome Guest, Not a member yet? Register   Sign In
Showing number of comments in blog (while listing all posts)
#1

[eluser]ddbt[/eluser]
Dear community..

while working on my little CI blog project I got stuck at the point where I want my post metadata (post author, post date, post time and number of comments) to show throughout the foreach loop in the view file. I managed to retrieve the post author, the post date and post time from the posts table in my database. However, I can't think of a valid structure to retrieve the number of comments (linked via a post_id) from the comments table in my database, and meanwhile keeping a clean MVC approach.

So my blog controller is structured like this:
Code:
function blog() {
$data['posts'] = $this->Posts_model->get_all_posts();
$this->load->view('blog_view', $data);
}

Model:
Code:
function get_all_posts() {
$q = $this->db->get('posts');
return $q->result();
}

And my view file looks like this:
Code:
<?php if (count($posts)): ?>
<?php foreach ($posts as $post): ?>
<div class="metadata">&lt;?php echo $post['post_author']; ?&gt; | &lt;?php echo $post['post_date']; ?&gt; at &lt;?php echo $post['post_time']; ?&gt; | NUMBER OF COMMENTS HERE</div>
&lt;?php endforeach; ?&gt;
&lt;?php endif; ?&gt;

Now I want to show &lt;?php echo $...['number_of_comments']; ?&gt; in the metadata <div> too, keeping a clean MVC structure. I know how to count comments in a model. All I gotta do is something like this:
Code:
function count_all_comments($post_id) {
$this->db->where('post_id', $post_id);
$q = $this->db->count_results('comments');
return $q->result();
}

But where do I get the $post_id from while running an foreach loop in the view? I know I can directly call this model action from the view file, but that is not respecting the MVC principles. Also.. how do I incorporate this into my controller?

All help is welcome, if you guys suggest a totally different structure I am also willing to learn from that. And the next step is doing a structure like:

if 0 -> 0 comments
if 1 -> 1 comment
if >1 -> x comments

Thank you for reading!
#2

[eluser]kgill[/eluser]
Do it all in your query, you just need to outer join your comments table to your posts table then you can select the count right along with the post date, time, etc.
#3

[eluser]eokorie[/eluser]
How about counting the number of comments in the model?

I am going to assume you have a table for comments and another for the articles and both tables are linked by the article id.

This is what I use to display the number of comments ofr each individual post on my own blog:

Code:
$this->db->select('*');
        $this->db->select("DATE_FORMAT(gk_blog_posts.date_art,'%W, %D %M %Y') AS article_date", FALSE );
        $this->db->select("Count(gk_blog_comments.id_com) AS TotalComments");
        // $this->db->from('gk_blog_posts');
        ($cat)? $this->db->where('idtop_art', $cat) : '' ;
        $this->db->where('status_art','publish');
        $this->db->join('gk_users', 'gk_users.id_usr = gk_blog_posts.author_art', 'left');
        $this->db->join('gk_blog_comments', 'gk_blog_comments.idart_com = gk_blog_posts.id_art AND gk_blog_comments.is_approved = 1', 'left');
        $this->db->join('gk_blog_categories', 'gk_blog_categories.id_top = gk_blog_posts.idtop_art', 'left');
        $this->db->group_by('id_art');
        $this->db->order_by('date_art','desc');
        $this->db->limit($limit, $offset);
        $query = $this->db->get('gk_blog_posts');
        
        return $query->result();

THe above code is my model file and in the views, I have this:

Code:
&lt;?php foreach($blog_articles as $p): ?&gt;
    <div class="articles">
          <ul>
            <li class="article_date">&lt;?php echo $p->article_date; ?&gt;</li>
            <li class="article_title author-&lt;?php $p->author_art; ?&gt;">&lt;?php echo anchor('/article/'.$p->id_art.'-'.url_title($p->title_art),$p->title_art); ?&gt;</li>
            <li class="article_meta"><span class="article_commentsLink">&lt;?php echo anchor('/article/'.$p->id_art.'-'.$p->name_art.'#comments', $p->TotalComments. ' Comments'); ?&gt;</span>
            </li>
              <li>&lt;?php echo $p->desc_art; ?&gt;</li>
            <li>&nbsp;</li>
              <li class="article_readmorelink">&lt;?php echo anchor('/article/'.$p->id_art.'-'.url_title($p->title_art),'Continue reading &raquo;'); ?&gt;</li>
              <li>
                <span class="article_category">Filed In: &lt;?php echo anchor('/blog-category/'.$p->id_top.'-'.url_title($p->title_top),$p->title_top); ?&gt;</span></li>
            <li class="article_socialbits">&lt;!-- AddThis Button BEGIN --&gt;
                    <a href="http://www.addthis.com/bookmark.php"
                        style="text-decoration:none;"
                       >id_art . "-" . url_title($p->title_art); ?&gt;', '&lt;?php echo $p->title_art; ?&gt;');"
                        onmouseout="addthis_close();"
                        onclick="return addthis_sendto();"><img
                        src="http://s7.addthis.com/static/btn/sm-plus.gif"
                        width="16" height="16" border="0" alt="Share" /> Share Bookmark</a>
                    [removed][removed]
&lt;!-- AddThis Button END --&gt;
            </li>
        </ul>
    </div>
    <br />
    <br />
&lt;?php endforeach; ?&gt;

Hope it helps...
#4

[eluser]ddbt[/eluser]
kgill and eokorie, thank you both for your replies! But actually they overwhelmed me a bit. To me it is pretty advanced code, and I don't really understand what happens (even with reading parts of the user guide along). As you can see my code examples are very basic, maybe one of you, or someone else of course, could help me understand or come up with a simpler solution. Maybe it is a simple solution, but both the terms used by kgill and the code provided by eokorie look daunting to me.

Once again, thanks.
#5

[eluser]jedd[/eluser]
I'm a big advocate of writing dodgy SQL and coming back to it later to fix it up .. but that's because I'm on the steep bit of the learning curve with SQL, and keep finding new and neat ways of doing things.

So, from the code you've given in your first message, and with the caveat that I always use arrays rather than objects (and consequently this code is UNTESTED) I'd suggest you could start with something like this. You're more familiar with your own data structures, so it should be easy to make this much better. And yes, I really should get my head around object fiddling one day.

Code:
function get_all_posts() {
    $q = $this->db->get('posts');
    $results = q->result_array();
    $retval = array();
    $x = 0;
    foreach ($results as $result)  {
         $retval[$x] = $result;
         $retval[$x]['comment_count'] = $this->count_all_comments($result['post_id']);
         $x++;
         }
    return $retval;  // Note this is an array, not an object, of course.
    }

function count_all_comments($post_id) {
    $this->db->where('post_id', $post_id);
    $q = $this->db->count_results('comments');
    return $q->result();
    }

Mind, as has been pointed out already, you really should be doing all these grabs in a single database query.
#6

[eluser]jegbagus[/eluser]
man, you make it look really hard,
just do simple sql with group query...

example :
assume if you have 2 table (blog , and comment)

Code:
select blog.blog_id, blog.blog_author, blog.blog_insert_date, count(*) as 'total_comment'
from blog, comment
where blog.blog_id = comment.blog_id
group by blog.blog_id, blog.blog_author, blog.blog_insert_date

you should get array result of blog id, blog author, blog insert date, total comment every blog id.

best regards,
#7

[eluser]ddbt[/eluser]
Wow jegbagus,

you make it look very easy. One last request.. could this be translated into CodeIgniter terminology.. using terms like $this->db->select, $this->db->where.. etc. And so that it can be run like one query from a controller?

Really, thank you all!
#8

[eluser]sl3dg3hamm3r[/eluser]
untested:

Code:
$this->db->select("blog.blog_id, blog.blog_author, blog.blog_insert_date, count(*) AS 'total_comment'");
$this->db->join('comment', ' blog.blog_id = comment.blog_id', 'LEFT');
$this->db->group_by('blog.blog_id, blog.blog_author, blog.blog_insert_date');
$query = $this->db->get('blog');
#9

[eluser]kgill[/eluser]
[quote author="ddbt" date="1252520535"]Wow jegbagus,

you make it look very easy. One last request.. could this be translated into CodeIgniter terminology.. using terms like $this->db->select, $this->db->where.. etc. And so that it can be run like one query from a controller?

Really, thank you all![/quote]

There's a problem with jegbagus' code - if a post has no comments it won't be selected. Do yourself a huge favour and find a tutorial on SQL joins - if the terms outer join are daunting then you need to read up on them not ask for simpler solutions. Without that knowledge you're both unaware a problem existed and that the code sl3dg3hamm3r posted fixed it using a left outer join - if he hadn't you'd be implementing a solution that only partially works.

@sl3dg3hamm3r you're nicer than I am - I'm a crusty old curmudgeon who would have left it to him to figure out for himself, I mean and don't spoon-feed answers.
#10

[eluser]sl3dg3hamm3r[/eluser]
[quote author="kgill" date="1252525124"]There’s a problem with jegbagus’ code - if a post has no comments it won’t be selected. Do yourself a huge favour and find a tutorial on SQL joins - if the terms outer join are daunting then you need to read up on them not ask for simpler solutions. Without that knowledge you’re both unaware a problem existed and that the code sl3dg3hamm3r posted fixed it using a left outer join - if he hadn’t you’d be implementing a solution that only partially works.[/quote]
I was fast-reading and didn't even notice - spot on, you're absolutely right. That's why I always use proper join-syntax, like that I usually consider 'left' and 'inner' always correctly right from first attempts.
Besides, queries are way more readable like that...


[quote author="kgill" date="1252525124"]@sl3dg3hamm3r you're nicer than I am - I'm a crusty old curmudgeon who would have left it to him to figure out for himself, I mean and don't spoon-feed answers.[/quote]
Had a boring minute Wink




Theme © iAndrew 2016 - Forum software by © MyBB