Most used tags - mysql

Question

I am working on a blog. Now I want to display the most used tags of an author. The tags are stored in the articles table, in a column called tags, komma seperated:

Article table (id | authorid | tags):

1 | 1 | soccer, sport
2 | 1 | sport, tennis, injury
3 | 1 | sport, golf, injury

So I want to display author with id 1 and display his 3 most used tags (from the article table as displayed above). In this case that would be: sport, injury, soccer.

Is this possible in MySql?

Answer 1

Yes its possible, but the better answer would be to direct you into a more "Normal" database structure.

You really need a db set up like:

Table of articles, table of tags, table of article_tags containing the IDs of the tag and the article it's assigned to.

You can then query the tags by each article ('X') by looking in article_tags and returning all tag_id's where article_id = ('X').

Answer 2

You should take a look at database normalization -- the wikipedia article is very advanced however it will give you terminology which you can search for to find more articles on the topic.

The basic idea is you'll have another table:

table name: tags
columns: id | name

And then a table to join them:

table name: article_tags
columns: article_id | tag_id

Then you'll join all of the data together in a query in order to display the tag names for each article. It will then be possible to do more advanced queries on the tables to figure out things such as most used tag by article, and most used tag by author.