0

I have a relational database in this format

Table: posts
Columns: post_id,post_title,post_content

Table: categories
Columns: category_id,category_name

Table: posts_categories
Columns: post_id,category_id

Posts can have multiple categories so i store them in posts_categories using post and category id, when i get results from database using below query, it just display the last category, Is it possible to display all categories otherwise i have to run a separate query, here my code.

    $this->db->select("p.*,pc.*,c.*");
    $this->db->where('post_id', $id); 
    $this->db->from('posts AS p');
    $this->db->join('posts_categories AS pc', 'pc.post_id = p.post_id', 'inner');
    $this->db->join('categories AS c', 'pc.category_id = c.category_id', 'inner');
    $q = $this->db->get();

Thanks for any help.

user969068
  • 2,818
  • 5
  • 33
  • 64
  • [This answer](http://stackoverflow.com/a/276949/1415625) should help. `GROUP_CONCAT` is what you need. – David Sep 08 '12 at 22:28
  • Thanks, I shall look into it, currently its look confusing on how i will implement with above code. – user969068 Sep 08 '12 at 22:59
  • You might consider using `$this->db->query()` instead of trying to lay it out with Active Record. Good luck! – David Sep 08 '12 at 23:05

1 Answers1

1

You didn't mention what fields you actually select. However, you can SELECT p.title, c.category_name and after doing your query (mentioned in the question), you should have multiple rows in your result, containing the posts title and a category name for that post.

Now if you want you can group these categories by posts in php, building a new array from the db result.

Tamás Pap
  • 17,777
  • 15
  • 70
  • 102
  • Thanks, well I am actually adding this way $this->db->select("p.*,pc.*,c.*"); code updated but i dont get all categories in post, just the last one. – user969068 Sep 08 '12 at 23:00
  • @Dshah Can you get the built query as a string, and post it? – Tamás Pap Sep 08 '12 at 23:07
  • SELECT p.*, pc.*, c.* FROM (posts AS p) INNER JOIN posts_categories AS pc ON pc.post_id = p.post_id INNER JOIN categories AS c ON pc.category_id = c.category_id WHERE post_id = '5' – user969068 Sep 08 '12 at 23:15
  • The query is correct. I tried it: http://d.pr/i/jC7T. Are you sure you are reading the results correctly in PHP? – Tamás Pap Sep 08 '12 at 23:32
  • ah right, i was saving result to a string instead of array, now i see results as array, what would be proper way to combine that to one element in php? Thanks a lot for ur efforts, appreciated. – user969068 Sep 09 '12 at 10:45
  • nvm i hv to save it in foreach($q->result_array() as $row){ $data['post_id'] = $row['post_id'];$data['post_categories'][] = $row['post_category']; Thanks for your help :) – user969068 Sep 09 '12 at 10:58