php/mysql tags query schema

Question

I have a 3 table tag system, topics, tags and tagtopic.

I now want to display the various tags for each topic as I display the topic. Since there are multiple tags for one topic, when I do the query below, I get multiple rows and the topic displays multiple times, once for each row. I don't think I can use a groupby as I'm not doing anything mathematical with the columns. What I really would like would be for each topic, display the topic and then a list of tags for a post such as (for Siri), 'iphone,smartphone,Siri'. tables:

topics
id | name |userid
tags
id | topicid | topicname
tagtopic
id | tagid | topicid '

$sql = "SELECT * FROM `topics` t
LEFT JOIN `tagtopic` tagtopic
on t.id = tagtopic.topicid
WHERE t.id= '12'";

Should mention that display is done with while($row = mysql_fetch_array($res)) so that it is displaying the rows of the recordset. I would like it to display the topic once, but since there are multiple rows created for the tags, it is displaying multiple times.

Do I have to give up on the join and do a subquery? Thanks for any suggestions.

please post tables structures.

Sebas
– Sebas

2012-06-01 13:55:05 +00:00
Commented Jun 1, 2012 at 13:55 — Sebas
– Sebas, Commented Jun 1, 2012 at 13:55
Should tags.topicname instead be called tags.tagname?

Marcus Adams
– Marcus Adams

2012-06-01 14:18:53 +00:00
Commented Jun 1, 2012 at 14:18 — Marcus Adams
– Marcus Adams, Commented Jun 1, 2012 at 14:18

Isaac Hildebrandt · Accepted Answer · 2012-06-01 13:55:50Z

2

You can do a GROUP_CONCAT which will put all the tags into a single field separated by a comma.

SELECT GROUP_CONCAT(tt.tagid) FROM topics t
JOIN tagtopic tt ON tt.topicid = t.topicid
WHERE t.id=12
GROUP BY t.id

answered Jun 1, 2012 at 13:55

Isaac Hildebrandt

1,0265 silver badges16 bronze badges

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

Comments

jbrtrnd · Accepted Answer · 2012-06-01 14:01:13Z

0

If you execute this query for ON AND ONLY ON topic ( deducted by WHERE t.id = 12 )
In your while loop :

var first = true;
while($row = mysql_fetch_array($res)) {
    if(first) {
        // PRINT THE TOPIC NAME
        $first = false;
    }
    // PRINT THE TAG ROW
}

answered Jun 1, 2012 at 14:01

jbrtrnd

3,8435 gold badges26 silver badges41 bronze badges

Collectives™ on Stack Overflow

php/mysql tags query schema

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related