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.

Confirmed

Is this a table that you constructed? If so, it's clearly not normalized. A normalized table would have a 1-to-1 mapping between the topic_id and the tags. You could then simply do a select distinct tags from forum_topics query to get what you want. – Shannashannah

Also, do not use the mysql_* functions in your code. These functions are no longer maintained and are being deprecated. Instead, you should use either MySQLi or PDO. Don't know which to use? This article should help. – Shannashannah

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.

Brachyuran

Thanks for the answer. 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. – Confirmed

@DotOyes Create another table topic_tags to manage the relation of topics and tags. – Brachyuran

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.

Okra

Answer 3

select distinct tags from forum_topics;

Radu

I'm not 100% sure this will result in what the OP wants. His table isn't normalized, so you'll (potentially) get back as many results as there are entries. – Shannashannah

Ahh, well that complicates things. (And sorry I didn't see your comment). – Radu