tag cloud question

[n8w]

I would like to create a tag cloud .. but not from a defined array .... but from a column in a database with many entries within a date range ... it could have 100s of entries

 

my table has the following columns

date, column1, column2, rating

 

 

I basically want to find out which are the most common words in column 1 and column 2

 

a row with date might look like this

 

record 1

date:2009-07-16 09:10:33Z

column1:red, blue, green

column2: hot, sunny

rating:3

 

record 2

date:2009-07-15 09:00:13Z

column1:red, blue

column2: cold, wet

rating:1

 

 

what is the best approach to doing this?

 

Here is a tutorial I found on tag clouds .. but it's based off a defined array .. I want to base my off my columns that might have hundred of tags and duplicates .. etc

 

function createTagCloud($tags)  
{     
    //I pass through an array of tags  
    $i=0;  
    foreach($tags as $tag)  
    {  
        $id = $tag['id']; //the tag id, passed through  
        $name = $tag['tag']; //the tag name, also passed through in the array  
          
        //using the mysql count command to sum up the tutorials tagged with that id  
        $sql = "SELECT COUNT(*) AS totalnum FROM tutorials WHERE tags LIKE '%".$id."%' AND published = 1";  

 

[n8w]

or if someone know of a good tag cloud api .. that would work

[fenway]

I don't understand the question.

[n8w]

I want to count how many times words exist in various entires ... so for example if I had these three entries:

 

#1 - health, sun, dog, cat

#2 - health, sun, cloud, rain

#3 - health, apple, ice cream, cloud

 

I would like the the script to return the number of times each word is used

(3) health

(2) sun

(2) cloud

(1) dog

(1) cat

(1) rain

etc

[fenway]

MySQL doesn't have built-in functions for that.