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.

Tag Cloud

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.

117 thoughts on “How to make a tag cloud in PHP, MySQL and CSS

  1. In the current state the terms field in database will get filled fast and with some misspelled terms or similar terms is there a way to filter out those ?

    I used ur script btw with sqlite works ok ;p

    Regards.

  2. hi , just a quich question, can we implement this search function instead of searching within our website, can we use the search term fromn google. ie. what they searched on google and came to our website?

  3. Hi

    I am new to coding and i do not know how create a data base so can kindly help by explain how to create the db that you mentioned above I am using cpanel to create the db.

    Regards
    Uday

  4. Hi Steve. I read your tutorial. I download the file put and set de database correctly but is not working for me. I have to add the tags by my self in the dadatbase or they are added when i submit words in the search form? Because i tryed like this and i don’t see any tags also i dont get any php errors. Do i have to link this script to another search form to show me the tags that i searched?

  5. Pingback: Anonymous
  6. Ottimo script Grazie.
    Aiuto! vedi link errore…

    Warning : mysql_fetch_array () si aspetta parametro 1 per essere risorsa, booleano dato in / home/mhd-01/www.invalleditria.com/htdocs/tagcloud/tagcloud.php on line 33

    ciao e grazie

  7. Hallo,
    I downloaded the file and uploaded to my server again. Created the database and enter the connection information. The connection to the database is good. When I type the words in the input field, these are not entered into the database.

    I have manually entered words into the database, it works. What or where I make the mistake? About an answer I would be very happy. Thanks in advance.

    Regards

  8. This works well if the data is somewhat evenly distributed.

    The problem with doing it this way is that one tag can overwhelm the database. I have two tags that have 200 and 150 entries respectively. No other tag has more than 30. As a result, even the tag with 30 gets put in the smallest percentage class, because 30/200 = 15%. This results in a bunch of small size tags and two very large tags. While I understand this is a (semi-)accurate representation of the database, it doesn’t make sense to not use 3 of the 5 font sizes.

    I found this website providing a solution to this problem: http://dburke.info/blog/logarithmic-tag-clouds/

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>