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.

This entry was posted in PHP. Bookmark the permalink.

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

  1. John says:

    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. Goofey says:

    I should have found this from the beginning. Wasted a day, trying to implement the formula over at wikipedia page “Tag cloud”.

    Of course percentage is better =) Thanks!

    @WMI: Hn-tags is for headings. Use span or a tags for tags and set the sizes in the css, just like the blogpost explains. more about h-tags at w3schools.com

    -goof

  3. Yusuf Tokgoz says:

    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?

  4. Darmie says:

    Thanks so much, this actually saved me a lot of time….

  5. Webber says:

    Thank you very much for the post and the code. Great job…
    Guess what I will be modifying tonight :)

  6. Sameer says:

    I like your idea. It really works. Thanks for your time!

  7. Uday says:

    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

  8. Very simple but effective code. I was working with an overcomplicated class from phpclasses.org. Your example is saving me from a headache.

  9. Roger says:

    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?

  10. Pingback: Anonymous

  11. Works for me. The way you separated the SQL and view is perfect for the MVC framework I am currently using.

  12. Cristiano says:

    Very simple but effective code.

    Thanks so much

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>