GROUP CONCAT / GROUP BY ?

[eluser]surf66[/eluser]
Hi

I ran into a problem a while back and have recently revisited it. I am really struggling.

I have a news feed and each post can be assigned to two or more categories.

I totally understand the database side and structure but by problem is simply showing the data.

This is the function I have been using in the model:

Code:

function get_news_posts(){
  $this->db->select('*');    
  $this->db->from('news_linktable');
  $this->db->join('news', 'news_linktable_news_id = news.news_id');
  $this->db->join('news_categories', 'news_linktable_news_category_id = news_categories.news_category_id');
  $this->db->order_by('news_date_posted', 'desc');
  $query = $this->db->get();
  return $query->result();
} //function get_news_posts() //gets all news posts and populates home page content slider

And I have been displaying the results as folows:

Code:

foreach($news_posts as $post) {
        $date_posted = $post->news_date_posted;
        $news_body = $post->news_body;
        $news_body = character_limiter($news_body, 500);  
        echo "<div class='slide'>";
        echo "<h3><a href='#'>" . $post->news_title . "</a></h3>";
         echo "<div class='post_details'>";
          echo "<p><img class='post_img' src='images/front_end/callender.png'/> <img class='post_img' src='images/front_end/clock.png'/>" . date("jS M g:ia",strtotime($date_posted)) . "</p>";
          echo "<p><img class='post_img' src='images/front_end/categories.png'/>" . $post->news_category_name . "</p>";
         echo "</div><br /><br />";
         echo "<div class='clear'></div>";
         echo "<p>" . $news_body . "</p>";
         echo "<div class='cont_reading'><a href='#'>Continue Reading...</a></div>";
        echo "</div>";
       }

Say I have a post in 2 categories in the db, the full post will show twice, each time with a different category assigned to it. All I wish to acheive is to have the post and then echo out next to the title something like this: Categories: Events, Announcements. Rather than have the post show several times.

I understand this may be easy for some of you to fix and you may also want to scream at me saying 'look at the user guide' but I have had this issue for a while and I am struggling, im just looking for further explanation.

Thanks in advance for any help

surf66

[eluser]vitoco[/eluser]
The idea here it's to show each news once....so you have to group the query for the field that must be unique in the resulset, i guess news.news_id , and GROUP_CONCAT the fields that will have more than one occurrence for news.news_id, in this case i guess the news_categories.news_category_id. So the result will be a row ( news ) with a field with all his categories concatenated.

Slds.