query big problem

[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:
&lt;?PHP echo $news_text; ?&gt;<br /><br />
<img src="&lt;?php echo base_url(); ?&gt;images/ico_wtk.gif" alt="Ikonka" />
<div class="comtyt">&lt;?PHP echo $com_title ?&gt;</div><br />
<div class="text">&lt;?PHP echo $com_text; ?&gt;</div>
<div class="comtime">
<span class="newsat">~&lt;?PHP echo $com_author; ?&gt;&nbsp;&lt;?PHP echo $com_date; ?&gt;</span>
<span class="comlink" style="padding-right: 10px;"></span>
</div>

Any ideas???

[eluser]xwero[/eluser]
You want it to be one query but because you request a single row and multiple rows of different data you end up repeating the data from the single row, which means you transport more data between the methods than you should.

If you create a method get_comments_for_news it will confuse other developers when you query for the news item properties too. Why not make comments a flag parameter of the method?

Code:

function get_news_item($id,$comments = false)
{
    $output = $this->db->query('SELECT news_text FROM news WHERE id=?',array($id))->row();

    if($comments == true)
    {
        $output->comments = $this->db->query('SELECT * FROM comments WHERE news_id=?',array($id))->result();
    }

    return output;
}

[eluser]warrennz[/eluser]
Doing it in one query, while possibly simple for you, actually creates a method that is very un-effecient and carries a lot of wasted overhead & data. xwero is right, you should run 2 queries and then organize the results how you need them from within the model.

[eluser]polish[/eluser]
Now i have one error:

Fatal error: Call to undefined method stdClass::num_rows() in my controller file!

I think that it is necessary there to change something but I don't know what!

[eluser]warrennz[/eluser]
Please wrap all code examples in

Code:

code tags.

Will make forum users all that more willing to have a look at your posted code and try to help.

[eluser]polish[/eluser]
This is my controller function:

Code:

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_news_item($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:

Code:

&lt;?PHP echo $news_text; ?&gt;<br /><br />
<img src="&lt;?php echo base_url(); ?&gt;images/ico_wtk.gif" alt="Ikonka" />
<div class="comtyt">&lt;?PHP echo $com_title ?&gt;</div><br />
<div class="text">&lt;?PHP echo $com_text; ?&gt;</div>
<div class="comtime"><span class="newsat">~&lt;?PHP echo $com_author; ?&gt;&nbsp;&lt;?PHP echo $com_date; ?&gt;</span>

[eluser]polish[/eluser]
Now is good Any ideas???

[eluser]polish[/eluser]
What can I do with this error on my controller function????

Code:

Fatal error: Call to undefined method stdClass::num_rows() ...

The solution can be other than proposed by Me!
Any suggestions???
Help me!

[eluser]TheFuzzy0ne[/eluser]
Please post your model.

[eluser]polish[/eluser]
It is my model function:

Code:

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);
    }

And it is function proposed by xwero:

Code:

function get_news_item($id,$comments = false)
{
    $output = $this->db->query('SELECT news_text FROM news WHERE id=?',array($id))->row();

    if($comments == true)
    {
        $output->comments = $this->db->query('SELECT * FROM comments WHERE news_id=?',array($id))->result();
    }
    return $output;
}

I know that it is necessary to use two queries but I don't know how to combine it into the workinng result with my controller, model and view. The solution can be different at this from me.

I apologises for grammatical mistakes because my English is ... average!