how to select unique keywords from a comma separated tags

Question

I want to retrieve some tags from my database, they are in the form:

topic_id       tags
   1        `tag1,tag2,tag3`
   2        `tag1,tag4,tag5`
   3        `tag2,tag4,tag5`
   4        `tag6,tag7,tag2`

I want to have something like this:

tag1 tag2 tag3 tag4 tag5 tag6 tag7

i.e all unique tags

So that I can wrap each tag in a link in order to group news articles that has such specific tags.

This following query I've written so far is not working:

$tags = mysql_query("SELECT tags, topic_id
                       FROM forum_topics
                       WHERE topic_id > 0")  or die (mysql_error());
                    while($tag = mysql_fetch_assoc($tags)){   
                    $split_tags  = "$tag";
                    $pieces = explode(",", $split_tags);
                    echo $pieces ;

When I did print_r($pieces);

I got Array ( [0] => Array ) Array ( [0] => Array ) Array ( [0] => Array ) Array ( [0] => Array )

Which was not what I was looking for.

As it is now my table structure looks like this topic_id , topic_head, topic_body, topic_tag, topic_date, topic_owner .. How can I further make the topic_tag normal.

Answer 1

If you normalize your database design, then you could get all the distinct tags very easy by

SELECT DISTINCT tags FROM forum_topics WHERE topic_id > 0

But now, with your database structure, you can't do this, you have to get all the tags and use array_unique on them.

$tags = array();
$rows = mysql_query("SELECT tags FROM forum_topics WHERE topic_id > 0")  or die (mysql_error());
while($row = mysql_fetch_assoc($rows)){   
  $tags = array_merge($tags, explode(',' $row['tags']));
}
$tags = array_unique($tags);
print_r($tags);

But even you could do this, normalize your database design is the best choice.

Answer 2

Try this:

$tags = "";
while($row = mysql_fetch_assoc($tags)) {   
    $tags .= $row["tags"] . ",";
}

$tags = rtrim($tags, ",");
$pieces = explode(",", $tags);

print_r($pieces); // all of them

$pieces = array_unique($pieces);

print_r($pieces); // distinct

...and as Jonah Bishop already mentioned, please avoid mysql_* functions.