CODEWITHSUNDEEP
mysqlcodeigniter
dnbrobin
dnbrobin

Reputation: 49

Codeigniter many-to-many relationship loop

I have a database setup like this:

post_id | title

1 | Some Title

2 | Another Title

tag_id | tag

1 | tag01

2 | tag02

post_id | tagt_id

1 | 1

1 | 2

2 | 1

I have joined these tables with the following code:

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

In my view, I can access the tag by using

$post['tag']

This results in a duplicate post for every tag associated with it.

The question is how do i loop through all tags associated with one post?

The expected output would be:

post_id 1 = tag01, tag02

instead of

post_id 1 = tag01

post_id 1 = tag02

Upvotes: 0

Views: 3812

Answers (2)

echo_Me
echo_Me

Reputation: 37233

try this

   $this->db->select('posts.post_id,GROUP_CONCAT(posts.tag) as all_tags');
   $this->db->from('posts');
   $this->db->join('posts_tags', 'posts.post_id = post_tags.post_id', 'inner');
   $this->db->join('tags', 'posts_tags.tag_id = tags.tag_id', 'inner');

Upvotes: 2

Mike Brant
Mike Brant

Reputation: 71384

If you intent is simply to look for tags related only to a single post, you would need to filter your query to only look for the post you are concerned with using a WHERE clause.

If your intent is to return all tags for all posts, but have each post only have one row with the tags listed as comma-separated values (or similar), you would need to look at using a GROUP_CONCAT function in your SELECT like this:

SELECT pt.post_id AS `post_id`, GROUP_CONCAT(t.tag) AS `tags`
FROM post_tags AS pt
INNER JOIN tags AS t ON pt.tag_id = t.tag_id
GROUP BY `post_id`

Upvotes: 1

Related Questions