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.
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.
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
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.