showing the number of comments

[eluser]castrolng[/eluser]
Hi, I've just started my journey with CI and it's been amazing so far however I'm having a hitch getting the results I want...

I'm rebuilding a magazine, scitech360[dot]com, using CI, but I'm having troubles keeping the logic away from the presentation(view).

I want to show the number of comments posted for each article on the same view that lists the article titles,

the comments table (MySQL) has a foreign index that associates each comment with an article. I've tried a table join solution but I'm not getting results.

I could do a

Code:

SELECT * FROM comments WHERE articleid='$row->articleid'

but the only place I see that I can call this successfully is in the view while iterating through the fetched article result...but I want to keep that in the Controller where I understand it should be...

Code:

$this->db->where('valid','1' );
        $this->db->orderby('art_id', 'desc');
        $this->db->limit(9,3);
        $data['query3']=$this->db->get('article');

then in the view

Code:

...

<?php foreach($query3->result() as $row){
                    
echo"<div id='update'>"; echo anchor('blog/viewer/'.$row->art_id.'/'.$row->c_id, $row->title);echo"<br />
<span id='text' >By $row->author | $row->entrydate)</span><br />
"; echo cutText($row->message, 250); echo anchor('blog/viewer/'.$row->art_id.'/'.$row->c_id, '...details');
} echo"</div>";?&gt;

I'll appreciate all the help I can get. Thanks

[eluser]TheFuzzy0ne[/eluser]
Welcome to the CodeIgniter forums.

You probably need to use SELECT DISTINCT ...

If you can dump your database schema and post it, I'll be happy to try to help.

[eluser]castrolng[/eluser]
Thanks Here it is:

Code:

CREATE TABLE `comments` (
  `id` int(11) NOT NULL auto_increment,
  `art_id` int(11) NOT NULL,
  `message` longtext NOT NULL,
  `valid` varchar(255) NOT NULL,
  `entrydate` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `title` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;




CREATE TABLE `article` (
  `art_id` int(11) NOT NULL auto_increment,
  `title` longtext NOT NULL,
  `message` longtext NOT NULL,
  
  PRIMARY KEY  (`art_id`)
  
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

[eluser]TheFuzzy0ne[/eluser]
Do you need to show:

a) the number of comments per article, or
b) the number of comments per user?

[eluser]castrolng[/eluser]
per article

Thanks for the swift reply

[eluser]TheFuzzy0ne[/eluser]
This do do the bulk of what you want to do:

Code:

SELECT DISTINCT *, COUNT(*) AS `comment_count` FROM `comments` GROUP BY art_id;

This will return more results than expected, but hopefully you can expand it a bit better, by adding a WHERE or HAVING and LIMITing the results.

I haven't tested it, but the equivalent using the active Record class might be something like this:

Code:

$db =& $this->db;
$db->select('DISTINCT *, COUNT(*) AS `comment_count`', FALSE);
$db->from('comments');
$db->group_by('art_id');

[eluser]castrolng[/eluser]

This do do the bulk of what you want to do:
SELECT DISTINCT *, COUNT(*) AS `comment_count` FROM `comments` GROUP BY art_id;

This will return more results than expected, but hopefully you can expand it a bit better, by adding a WHERE or HAVING and LIMITing the results.

I haven’t tested it, but the equivalent using the active Record class might be something like this:
$db =& $this->db;
$db->select('DISTINCT *, COUNT(*) AS `comment_count`', FALSE);
$db->from('comments');
$db->group_by('art_id');

I'm bit lost as to how to link the results from the comments to the articles in the view...

could u show that.

thanks

[eluser]TheFuzzy0ne[/eluser]
Well, I can show you how it should work in theory, sure.

This is an imaginary model method, based on how I would do it:

Code:

function get_articles($limit=25, $offset=0)
{
    # First, we get the articles
    
    $this->db->where('valid','1' );
    $this->db->order_by('art_id', 'desc'); # $this->db->orderby() is deprecated, use $this->db->order_by() instead.
    $this->db->limit($limit,$offset);
    $res = $this->db->get('article');
    
    if ($res->num_rows() > 0) # Make sure we have some rows.
    {
        $articles = $res->result_array();
        
        # Loop through the results to obtain the article IDs for our next database query.
        $article_ids = array();
        
        foreach ($articles as $article)
        {
            $article_ids[] = $article['art_id'];
        }
        
        # Next we use that array to get our comment counts
        $this->db->select('art_id, COUNT(*) AS `count`', FALSE);
        $this->db->distinct();
        $this->db->where_in('art_id', $article_ids);
        $this->db->group_by('art_id');
        $this->db->order_by('art_id');
        $this->db->limit($limit, $offset);
        $res = $this->db->get('comments');
        
        # Finally, we loop through the articles again, but we add the count
        if ($res->num_rows() > 0)
        {
            $c = 0;
            $comments = $res->result_array();
            
            foreach ($articles as &$article)
            {
                # Add the count as 0
                $article['comment_count'] = 0;
                
                if ($article['id'] === $comments[$i]['art_id'])
                {
                    $article['count'] = $comments[$i]['count'];
                    $c++;
                }
            }

            return $articles;
        }
    }
    
    return FALSE; # Return FALSE if we get to here.
}

Don't forget, this is totally untested, although you might find it works straight out of the box. There's probably a way to do this in a single query, but I think it would be inefficient, and you wouldn't be able to use the AR class as it's intended.

Hopefully this helps.

Good luck!

[eluser]castrolng[/eluser]
Big Thanks,

I'll try it out and let u know.

[eluser]castrolng[/eluser]
Hi Loller, sorry for being long in replying, just got the code (ignited) today

Thanks,

I created a helper function comments_helper.php

Code:

<?php

function article_comments($art_id)
    {
  
$querycomm=@mysql_query("SELECT comments.id, comments.title,comments.name,comments.entrydate,comments.valid,comments.art_id, article.art_id,article.c_id
FROM comments
    INNER JOIN article  
        ON comments.art_id = article.art_id  
    WHERE comments.valid=1 AND article.art_id=$art_id");
    $commented=mysql_num_rows($querycomm);
    return $commented;
    }
    
?>

and in the view I called the fuction for each result row

Code:

<?php foreach($query3->result() as $row)
     {
                    
    $comments=article_comments($row->art_id);
    echo"<div id='update'>";
    echo anchor('blog/viewer/'.$row->art_id.'/'.$row->c_id, $row->title);
    echo"<br /><span id='text' >By $row->author | $row->entrydate) |$comments</span><br />";
    echo cutText($row->message, 250);
    echo anchor('blog/viewer/'.$row->art_id.'/'.$row->c_id, '...details');
    }
                        echo"</div>";
?&gt;

It works, what do u think?

Thanks