php/mySQL How do I get all the tags from the database for multiple items?

Question

I have 3 tables that look like this -

content
- cid
- data
contenttags
- cid
- tid
tags
- tid
- tag

I'm trying to select 5 items from the content table whilst display every tag associated with the content. Here's what I have -

SELECT c.*, t.tag FROM content AS c LEFT JOIN contenttags AS ct ON c.cid = ct.cid LEFT JOIN tags AS t ON ct.tid = t.tid LIMIT 5

But all I seem to get is a whole lot of duplicate rows with the only difference being the tag. I thought ideally, all the tags could be merged in sql into one field? I dunno, am I going about this all wrong?

The Scrum Meister · Accepted Answer · 2011-03-09 04:39:42Z

3

you can use the GROUP_CONCAT() function, which will return the tags comma delimited:

SELECT c.*, GROUP_CONCAT(t.tag) AS Tags
FROM content AS c 
  LEFT JOIN contenttags AS ct ON c.cid = ct.cid 
  LEFT JOIN tags AS t ON ct.tid = t.tid 
GROUP BY c.cid 
LIMIT 5

answered Mar 9, 2011 at 4:39

The Scrum Meister

30.2k8 gold badges69 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

allanminium Over a year ago

oh wow, that's exactly what I was looking for, thanks! But why does it exclude those entries which don't have any tags?

The Scrum Meister Over a year ago

It shouldn't excludes them from the resultset since you are using LEFT joins. If you do not want it to return NULL, Try surrounding it with a IFNULL() ie IFNULL(GROUP_CONCAT(...),'') AS Tags.

Collectives™ on Stack Overflow

php/mySQL How do I get all the tags from the database for multiple items?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related