Adding a category list to the end of each blog post

[eluser]Andy UK[/eluser]
I have a blog which is almost finished, but I'm not sure how to add a list of the categories assigned to the posts at the end of each individual one.

Tables are as follows:

Blog_posts
Categories
Blog_categories_join (many to many relationship)

Now while I have no trouble getting the details from the Blog_posts table such as title, date, body, etc. I can't get my head around how to add another dimension by adding the categories for each post.

For example, let's say the database returns 10 posts. All well and good. I can loop through the results and get the title, body, etc and echo them to the screen.

However, if I want to add a list of the categories at the end of each of the ten posts, It gets complicated. Post one might have been assigned two categories, post three might have five, etc, etc.

Now I'm guessing that I need two different queries, one to get the blog posts and one to get the categories for each post ID. The second would query the Blog_categories_join table, joining the query with the categories table to get the names for each category ID.

Ok, let's say that I now have my two result sets... Where do I go from here? Do I need some kind of loop within a loop in the view, or should I be doing this in the model? I could probably do this is the view with quite a bit of code and trail and error, but I'd be interested to know if there's a better way.

Thanks in advance.

[eluser]Twisted1919[/eluser]

Code:

function posts()
{
   $posts = $post_categories = array();  
   if($posts = $this->model->get_posts(10))
   {
      foreach($posts AS $post)
      {
         $post_categories[$post->post_id] = $this->model->get_post_categories($post->post_id);
      }
   }
   $data['posts'] = $posts ;
   $data['post_categories'] = $post_categories;
   $this->load->view('posts',$data);
}
// view
if(!empty($posts))
{
   foreach($posts AS $post)
   {
      echo $post->title.'<br />';
      echo $post->body.'<br />';
      if(!empty($post_categories[$post->id]))
      {
         foreach($post_categories[$post->id] AS $pc)
         {
              echo $pc->category_name ;
         }
      }
   }
}

[eluser]Andy UK[/eluser]
Your code was a great help, Thank you!

I've almost got it working, but I'm stuck on getting the info out of the category result. I still haven't been able to get my head around the whole object, multi-dimensional array thing. I'm getting the error message "Message: Trying to get property of non-object" in the view, but on a positive note, the error appears the correct number of times, ie. Twice if a post has two categories, three times if there are three categories, etc.

Below is a test example to show you what I'm getting back from the database with an arbitrary post number 3:

Code:

$test_category = $this->blog_model->getPostCategories(3);

And a print_r in the view of the variable $test_category gives:

Code:

Array ( [0] => stdClass Object ( [category] => Mercado Inmobiliario [id] => 3 ) [1] => stdClass Object ( [category] => Arquitectura [id] => 2 ) )

I've also included below the getPostCategories method in the Model:

Code:

function getPostCategories($post_id)
    {
        $suffix = '_' . $this->lang->lang();
        
        $this->db->select("categories.category{$suffix} as category");
        $this->db->select("categories.id");
        $this->db->join('blog_categories_join', 'blog_categories_join.category_id = categories.id');
        $this->db->where('blog_categories_join.blog_id', $post_id);
        $result = $this->db->get('categories');
        return $result->result();
    }

Addendum

The view is the same is in your example:

Code:

<?php
    if(!empty($post_categories[$post->id]))
    {
        foreach($post_categories[$post->id] as $category)
        {
            print_r($category->category);
        }
    }
?>

[eluser]Andy UK[/eluser]
Can anyone help with this? If need be I can rephrase the question. Thanks again!

[eluser]Andy UK[/eluser]
Please can someone help with this. I'm sure it has something to do with the depth of the array being returned from the database. I've tried to figure it out by myself, but I could really use a pointer or two right about now.

Thanks!

[eluser]Twisted1919[/eluser]

Code:

function posts()
{
   $posts = $post_categories = array();  
   if($posts = $this->model->get_posts(10))
   {
      foreach($posts AS $post)
      {
         $post_categories[$post->post_id] = $this->model->get_post_categories($post->post_id);
      }
   }
   print_r($posts);
   print_r($post_categories);
}

this should get you an idea on what's in your array .
Also, your method should be like :

Code:

function getPostCategories($post_id)
    {
        $suffix = '_' . $this->lang->lang();
        
        $this->db->select("categories.category{$suffix} as category");
        $this->db->select("categories.id");
        $this->db->join('blog_categories_join', 'blog_categories_join.category_id = categories.id');
        $this->db->where('blog_categories_join.blog_id', $post_id);
        $query= $this->db->get('categories');
        return $query->num_rows() > 0 ? $query->result() : FALSE ;
    }

and finally:

Code:

<?php
    if(!empty($post_categories[$post->id]))
    {
        foreach($post_categories[$post->id] as $category)
        {
            print_r($category->category);
        }
    }
?>

is wrong somehow, don't print_r($category->category) but print_r($category);