[eluser]tomclowes[/eluser]
My code is posted below.
What I am trying to do is utilize the TOXI tag storage method to get tags for my custom blog software whilst also getting a comment count. The code is pretty self explanatory.
I have:
blogs - my table containing posts
comments - my comments table connected to blogs with a field comments.postID
tags - a table containing my tags (columns 'ID' and 'tag')
post_tags - connects tags and blogs by having post_tags.postID and post_tags.tagID
My problem is that my code at the moment is duplicating the comment count, and the tags.. such that each tag is displayed twice, and the comment count is double what it should be.
I have absolutely no idea why.
Could anyone send me in the right direction?
Thanks
Code:
$this->load->database();
$this->db->select('GROUP_CONCAT(tags.tag) as tags,count(comments.ID) AS commentcount,blog.title,blog.permalink,blog.content,blog.author,blog.date');
$this->db->from('blog');
$this->db->join('post_tags', 'post_tags.postID = blog.ID','left');
$this->db->join('tags', 'tags.ID = post_tags.tagID','left');
$this->db->join('comments', 'comments.postID = blog.ID','left');
$this->db->group_by('blog.ID');
$query=$this->db->get();
$data=$query->result_array();
return $data;