Multiple Categories

[eluser]georgerobbo[/eluser]
Hello. I'm trying to display multiple categories assigned to a blog entry in CodeIgniter. I can do so with standard PHP but not CI.

Database Structure

table: Blog
BlogID
BlogSlug
BlogTitle
BlogContent
BlogPostDate
BlogAmmendDate
BlogThumb
BlogStatus

table: Category
CategoryID
The Category

table: BlogCategory
BlogID
CategoryID

In my controller I am successfully passing the 10 most recent blog entries to the view. What I want to achieve is for each entry to query all the categories assigned to that blog and then display them.

Controller: Home

Code:

<?php

class Home extends Controller {

    function Home()
    {
        parent::Controller();    
    }
    
    function index()
    {    
        $this->load->model('Meta');
        $this->load->model('Blog');
        
        $this->load->helper('typography');
        $this->load->library('excerpt');
        
        $Data['GetLatest'] = $this->Blog->GetLatest();
                                
        $this->load->view('header',$Data);
        $this->load->view('multiple');
        $this->load->view('footer');
    }
}

/* End of file Home.php */
/* Location: ./system/application/controllers/Home.php */

View: multiple

Code:

<div id="content">
    <div class="wrapper">
        
            &lt;?php
            
            foreach ($GetLatest->result_array() as $item)
            {
            
            ?&gt;
            <div class="blog mini">
            
                <div class="thumb">
                    <a href="&lt;?php echo site_url('/blog/'.$item['BlogSlug']); ?&gt;"><img src="&lt;?php echo base_url(); ?&gt;uploads/&lt;?php echo $item['BlogThumb']; ?&gt;" alt="&lt;?php echo $item['BlogTitle']; ?&gt;" /></a>
                </div>
                
                <h3><a href="&lt;?php echo site_url('/blog/'.$item['BlogSlug']); ?&gt;">&lt;?php echo $item['BlogTitle']; ?&gt;</a></h3>
                <p><a href="&lt;?php echo site_url('/blog/'.$item['BlogSlug']); ?&gt;">&lt;?php echo $this->excerpt->construct($item['BlogContent']); ?&gt;</a></p>
                
            </div>
            
            &lt;?php
            
            }
            
            ?&gt;
    </div>
</div>

[eluser]danmontgomery[/eluser]
Why not just do that in the query you're using to fetch the blog?

You haven't posted the model so I can't speculate what your query looks like, but it should be something like:

Code:

$this->db->select('Blog.*, GROUP_CONCAT(DISTINCT Category.CategoryName SEPARATOR ", ") AS categories', FALSE)
    ->join('BlogCategory', 'BlogCategory.BlogID = Blog.BlogID', 'left')
    ->join('Category', 'BlogCategory.CategoryID = Category.CategoryID', 'left')
    ->group_by('Blog.BlogID')
    ->order_by('Blog.BlogPostDate', 'DESC')
    ->limit(10)
    ->get('Blog');