CodeIgniter Forums
Nested select only returning one row :( - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Nested select only returning one row :( (/showthread.php?tid=43518)



Nested select only returning one row :( - El Forum - 07-14-2011

[eluser]Wonder Woman[/eluser]
Hello,

I would like some help with my nested query for my news article page - basically I want each article to have its related comments displayed underneath but at the moment it only returns one comment for each article Sad

Code:
function get_records($limit, $offset, $sort) {
   $this->db->select('news.*, COUNT(comments.news_id) as comments, comments.comment as comment, news.id as id, news.created_on as created_on, CONCAT(users.firstname, " ", users.surname) as author, categories.category as category, news_types.type as news_type', FALSE);
   $this->db->from('news', 'comments');
   $this->db->join('users', 'users.id = news.author', 'left');
   $this->db->join('comments', 'comments.news_id = news.id', 'left');
   $this->db->join('categories', 'categories.id = news.category', 'left');
   $this->db->join('news_types', 'news_types.id = news.news_type', 'left');
   $this->db->group_by('news.id');
   $this->db->order_by('news.id', 'DESC');
   $this->db->limit($limit, $offset);
   $query = $this->db->get();
   if($query->num_rows() > 0) {
      return $query->result_array();
   }
}