Very simple database structure for a tag cloud

The tag cloud (or word cloud) is a visual representation of the word content of a given text: in plain English it’s a listing of the words having different sizes, those used more frequently being larger and those less used being smaller. It started out as a buzzword but quickly become largely used. I will not go now into endless discussions about the controversial value it adds to your websites.  Assuming you already decided you want one, let’s see a very simple method to store the tags into a MySQL table.

First we need to create the table. I named it “tags” and it only has two fields: one to store the word and one to store the number of times it was found in the given text.

 
CREATE TABLE IF NOT EXISTS `tags` (
`tag` text NOT NULL,
`count` int(4) NOT NULL DEFAULT '0',
UNIQUE KEY `tag` (`tag`(20))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Please note I added an UNIQUE index key to the ‘tag’ filed to make sure the same word doesn’t get inserted twice. Now we can do the insert in a single query:

INSERT INTO tags
SET tag = "new_word",
count =1 ON DUPLICATE KEY UPDATE count = count +1

The first time it will insert the word, the rest it will increase the counter.
Simple as I promised.

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Categories

Archives

Calendar

December 2014
M T W T F S S
« Jul    
1234567
891011121314
15161718192021
22232425262728
293031