[eluser]polish[/eluser]
Hi!
I hava a big problem in my blog witch one sql query! I have 2 tables:
news (news_id, news_title, news_text, news_author, news_date, news_time),
comments (com_id, news_id, com_title, com_text, com_author, com_date)
and i need a one sql query (normal or ActiveRecord) who give me only ONE news and ALL comments for this news!
This query
SELECT distinct comments.*, news.news_text FROM comments INNER JOIN news ON comments.news_id = news.news_id WHERE comments.news_id=".$news_id
work but when i have two or more comments for one news the news is shown so many times how many is comments!
This is my model function:
function get_comments_for_news($news_id)
{
return $this->db->query("SELECT distinct comments.*, news.news_text FROM comments INNER JOIN news ON comments.news_id = news.news_id WHERE comments.news_id=".$news_id);
}
Controller function:
function show()
{
$news_id = $this->uri->segment(3);
IF(isset($news_id) and is_numeric($news_id))
{
$this->load->model('Comments');
$query = $this->Comments->get_comments_for_news($news_id);
$content = '';
if ($query->num_rows() > 0)
{
foreach($query->result() as $item)
{
$content .= $this->load->view('com_loop', $item, True);
}
}
else
{
$content = '<h1><center>No Comments</center></h1>';
}
$this->response['content'] = $content;
}
else
{
$this->response['content'] = '<h1><center>Bad link</center></h1>';
}
$this->load->view('index', $this->response);
}
And this is my view:
<?PHP echo $news_text; ?><br /><br />
<img src="<?php echo base_url(); ?>images/ico_wtk.gif" alt="Ikonka" />
<div class="comtyt"><?PHP echo $com_title ?></div><br />
<div class="text"><?PHP echo $com_text; ?></div>
<div class="comtime">
<span class="newsat">~<?PHP echo $com_author; ?> <?PHP echo $com_date; ?></span>
<span class="comlink" style="padding-right: 10px;"></span>
</div>
Any ideas???