[eluser]vokic[/eluser]
Well i do it like this, simply by using the active record class, and i must separate two different situations:
1. You are pulling out categories just for representation (to put them on display to the user):
Code:
// Categories
$this->db->select('GROUP_CONCAT(DISTINCT ci_news_categories.name SEPARATOR 'whatever') AS categories');
$this->db->join('ci_news_to_categories', 'ci_news_to_categories.news_id = ci_news.id', 'LEFT');
$this->db->join('ci_news_categories', 'ci_news_categories.id = ci_news_to_categories.category_id', 'LEFT');
// News
$this->db->select('ci_news.*');
$this->db->orderby('ci_news.date', 'desc');
$this->db->groupby('ci_news.id');
$news = $this->db->get('ci_news');
$news = $news->result_array();
2. You also need data from categories (these categories migh be clickable and you want their id's and rest of the data)... So we select our news items just like in the previous case and categories must go through the loop...
Code:
// News
$this->db->select('ci_news.*');
$this->db->orderby('ci_news.date', 'desc');
$news = $this->db->get('ci_news');
$news = $news->result_array();
//Categories
$this->db->select('ci_news_to_categories.news_id, ci_news_categories.*');
$this->db->join('ci_news_to_categories', 'ci_news_to_categories.category_id = ci_news_categories.id');
$this->db->orderby('ci_news_categories.name');
$query = $this->db->get('ci_news_categories');
foreach ($query->result() as $row) {
$categories[$row->news_id][] = array(
'id' => $row->id,
'name' => $row->name
);
}
// News loop to assign categories
foreach ($news as $key => $item)
$news[$key]['categories'] = isset($categories[$item['id']]) ? $categories[$item['id']] : array(); // or just ''
There you go... I think.. Please don't mind the spelling errors and if this solution is not exactly correct (syntax etc.) because I'm writing it at 5am (local time) and porting it from another, similar, situation... Also note that group_concat was added from mySQL 4.1... Hope this helps...
