CODEWITHSUNDEEP
mysqlcodeigniter
dnbrobin
dnbrobin

Reputation: 49

join tables and loops

I have joined tables of posts and tags like this:

$this->db->select('*');
$this->db->from('posts');
$this->db->join('posts_tags', 'posts.post_id = posts_tags.post_id', 'left');
$this->db->join('tags', 'posts_tags.tag_id = tags.tag_id', 'left');

How do i loop through each post, and display a list of their respective tags. For example:

post1: tag1, tag2
post2, tag1, tag3

At the moment, i can display the tags, but it return two rows for post 1 and two. The output now is:

post1: tag1
post1: tag2
post2: tag1
post2: tag3

How do i return one row for post, with all the related tags inside?

Upvotes: 0

Views: 51

Answers (1)

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

Use group concat and group results

$this->db->select('posts.*');
$this->db->select('GROUP_CONCAT(posts_tags.tag_title) as TagTitles');
$this->db->from('posts');
$this->db->join('posts_tags', 'posts.post_id = posts_tags.post_id', 'left');
$this->db->join('tags', 'posts_tags.tag_id = tags.tag_id', 'left');
$this->db->group_by('posts.id');
$this->db->get();

Upvotes: 1

Related Questions