[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>";?>
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]
Quote: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>";
?>
It works, what do u think?
Thanks
|