Make a list of most used keywords

Question

I have a mysql table with these columns: id, text, keywords.

ID is an id. Text is a title. Keywords is a list of tags in this format: tag1 tag2 tag3.

How would I go about getting a list of the most used keywords in the column? Eg. if I wanted to build a tag cloud from all the items in the table.

Answer 1

A simple way would be to create an array where each key is tag#. The value of each of those keys is the number of times tag# appears in the database; this would involve traversing through each tag in the database.

Answer 2

There are ways to do what you want. But it won't be simple. The way you have organized your keywords in this database is going to cause quite a few headaches. You should try to normalize the data.

Perhaps instead of this:

id   text   keywords
1    bob    he she it
2    thing  white yellow hello

Have an separate table for the keywords:

id keyword
1  he
1  she
2  white
2  yellow

That way, it would be a much simpler matter to find what you want:

select count(keyword) as num from `keywords` group by keyword order by num desc

Answer 3

You might be better off normalising your database tables. Maybe something like this:

Items table :  id, text

Tags table : id, text

items_tags table:  item_id, tag_id

This way you can associate multiple tags with each item, and queries for tag counts become easy.