How to select unique keywords from comma-separated tags

Question

I want to get 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 

ie all unique tags

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

This following query, which I wrote so far, does not work:

 $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 )

This is not what I was looking for.

As now, my table structure looks like this: topic_id , topic_head, topic_body, topic_tag, topic_date, topic_owner . How can I make a normal topic_tag normal.

Answer 1

If you normalize your database design, you can easily get all the individual tags with

 SELECT DISTINCT tags FROM forum_topics WHERE topic_id > 0 

But now, with your database structure, you cannot do this, you must 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 can do it, normalize the database design is the best choice.

Answer 2

Try the following:

 $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 the mysql_* functions.

Answer 3

 select distinct tags from forum_topics;