How to make a tag cloud in PHP, MySQL and CSS

Update May 2011: Almost 4 years on, this post continues to get heaps of traffic and comments so i’m going to attempt to answer some of the most FAQ. I have added the MySQL table structure and demonstrated how a basic search page would work to increment the search term counter. I’ve also added some pretty CSS3 and added a last search date for each term. If you just want the files to download, skip to the end.

Sample Tag Cloud

Today I wanted to make a tag cloud, but all my searching proved fruitless – so I decided to stop being lazy and just work it out myself!

First you’ll need a database that stores the keywords and search terms.

Each time a user performs a search, we will check to see if the term has been searched before. If it has, we will increment the counter for that term. If the term has not been searched for before, we will insert the search term with a counter value of 1.

Lets begin with a basic MySQL table structure for the search table:

CREATE TABLE `search` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `term` varchar(255) NOT NULL DEFAULT '',
  `counter` int(11) NOT NULL DEFAULT '1',
  `last_search` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `term` (`term`)
) ENGINE=InnoDB;

Now lets make a search page. Here is some basic HTML to get started with:

<form id="search" action="?action=search" method="get">
  <input id="term" type="text" name="term" />
  <input id="submit" type="submit" name="submit" value="Search" />
</form>

In the next section, we are going to accomplish three things:

1. Connect to the database
2. Handle new searches so that the counter can be updated
3. query the database searches to build a PHP array of search terms

// connect to the database
$conn = mysql_connect('localhost', 'username', 'password');
if (!$conn or !mysql_select_db('my_database', $conn)) die('cannot connect to db');

// handle new searches
if (isset($_GET['action']) and $_GET['action'] == 'search')
{
    // get the current time
    $now = date("Y-m-d H:i:s");

    // get the submitted term and prepare it for the database query
    $term = mysql_real_escape_string(strip_tags(trim($_GET['term'])));

    // check if the term has been submitted before
    if (mysql_result(mysql_query("SELECT COUNT(id) FROM search WHERE term = '$term'"), 0) > 0)
    {
        // the term exists - update the counter and the last search timestamp
        mysql_query("UPDATE search SET counter = counter+1, last_search = '$now' WHERE term = '$term'");
    } else {
        // the term does not exist - insert a new record
        mysql_query("INSERT INTO search (term, last_search) VALUES ('$term', '$now')");
    }
}

// prepare the tag cloud array for display
$terms = array(); // create empty array
$maximum = 0; // $maximum is the highest counter for a search term

$query = mysql_query("SELECT term, counter FROM search ORDER BY counter DESC LIMIT 30");

while ($row = mysql_fetch_array($query))
{
	$term = $row['term'];
	$counter = $row['counter'];

	// update $maximum if this term is more popular than the previous terms
	if ($counter > $maximum) $maximum = $counter;

	$terms[] = array('term' => $term, 'counter' => $counter);

}

// shuffle terms unless you want to retain the order of highest to lowest
shuffle($terms);

Next we’ll setup a bit of css. Feel free to adjust these as you see fit.

#tagcloud {
	width: 300px;
	background:#CFE3FF;
	color:#0066FF;
	padding: 10px;
	border: 1px solid #559DFF;
	text-align:center;
    -moz-border-radius: 4px;
    -webkit-border-radius: 4px;
    border-radius: 4px;
}

#tagcloud a:link, #tagcloud a:visited {
	text-decoration:none;
    color: #333;
}

#tagcloud a:hover {
	text-decoration: underline;
}

#tagcloud span {
	padding: 4px;
}

#tagcloud .smallest {
	font-size: x-small;
}

#tagcloud .small {
	font-size: small;
}

#tagcloud .medium {
	font-size:medium;
}

#tagcloud .large {
	font-size:large;
}

#tagcloud .largest {
	font-size:larger;
}

Finally we just want to loop through the array and display it with the appropriate css class.

<h3>Popular Searches</h3>
<div id="tagcloud">
<?php 
// start looping through the tags
foreach ($terms as $term):
 // determine the popularity of this term as a percentage
 $percent = floor(($term['counter'] / $maximum) * 100);

 // determine the class for this term based on the percentage
 if ($percent < 20): 
   $class = 'smallest'; 
 elseif ($percent >= 20 and $percent < 40):
   $class = 'small'; 
 elseif ($percent >= 40 and $percent < 60):
   $class = 'medium';
 elseif ($percent >= 60 and $percent < 80):
   $class = 'large';
 else:
 $class = 'largest';
 endif;
?>
<span class="<?php echo $class; ?>">
  <a href="search.php?search=<?php echo $term['term']; ?>"><?php echo $term['term']; ?></a>
</span>
<?php endforeach; ?>
</div>

And thats it! Hope this saves someone some time. Add your comments below!

I have combined this tutorial into a single file,  download the full example here.