Should I use two queries, or is there a way to JOIN this in MySQL/PHP?

Question

Morning y'all!

Basically, I'm using a table to store my main data - called 'Code' - a table called 'Tags' to store the tags for each code entry, and a table called 'code_tags' to intersect it.

There's also a table called 'users' which stores information about the users who submitted each bit of code.

On my homepage, I want 5 results returned from the database. Each returned result needs to list the code's title, summary, and then fetch the author's firstname based on the ID of the person who submitted it. I've managed to achieve this much so far (woot!).

My problem lies when I try to collect all the tags as well. At the moment this is a pretty big query and it's scaring me a little.

Here's my problematic query:

SELECT code.*, code_tags.*, tags.*, users.firstname AS authorname, users.id AS authorid
FROM code, code_tags, tags, users
WHERE users.id = code.author AND code_tags.code_id = code.id AND tags.id = code_tags.tag_id
ORDER BY date DESC
LIMIT 0, 5

What it returns is correct looking data, but several repeated rows for each tag. So for example if a Code entry has 3 tags, it will return an identical row 3 times - except in each of the three returned rows, the tag changes. Does that make sense?

How would I go about changing this? Thanks!

Jack

Answer 1

You should try GROUP_CONCAT :

SELECT code.*, 
       code_tags.*, 
       tags.*, 
       users.firstname AS authorname, 
       users.id AS authorid,
       GROUP_CONCAT(tags.name SEPARATOR ', ')
FROM code, code_tags, tags, users
WHERE users.id = code.author AND code_tags.code_id = code.id AND tags.id = code_tags.tag_id
GROUP BY code_id
ORDER BY date DESC
LIMIT 0, 5