Tagcloud with PHP and MySQL

Create a tagcloud with mysql and phpA tagcloud is a great way to illustrate what content on your site is the most popular.

In this post we’ll build a tagcloud script based on PHP and MySQL.

The script uses very basic HTML and CSS that you can easily customize to reflect the look and feel of your site.

MySQL setup

The example tagcloud is created for displaying the most popular brands on a ecommerce site. Each time a user browses a page with a specific product, the following data is written in a table named t_shop_tags:

  • timestamp
  • tag (ecommerce brand)

Based on this information we can create SQL code that creates groups of brands, and selects the 20 most popular.

$result = mysql_query("SELECT tag, COUNT(tag), brand FROM t_shop_tags group by tag ORDER BY COUNT(tag) DESC LIMIT 20");

The output of this SQL statement is the following data in the  $result variable:

tagCOUNT(tag)
Hunter22
Gustav12
Soya11
Signal10
Rieker10
Konrad7
Vagabond6
Navlepiercinger6
Cashott6
Merrell6
Humoor6
HAY5
Khrio5
Hugo_Boss5
Bloomingville5
Minus5
Ecco5
Superfit5
Nike5
Heartmade5

Final PHP and MySQL tagcloud script

Based on this $result variable, we’re creating a PHP function that can generate a tagcloud with the 20 rows. You can easily change the min/max font sized in the tag cloud by changing the values of the $min_font_size and $max_font_size.

function get_tag_data() {
 $result = mysql_query("SELECT tag, COUNT(tag), brand FROM t_shop_tags group by tag ORDER BY COUNT(tag) DESC LIMIT 20");
 while($row = mysql_fetch_array($result)) {
 $arr[$row['brand']] = $row['COUNT(tag)'];
 }
 ksort($arr);
 return $arr;
}

function get_tag_cloud() {
// Default font sizes
$min_font_size = '12';
$max_font_size = '25';

// Pull in tag data
$tags = get_tag_data();

// largest and smallest array values
$max_qty = max(array_values($tags));
$min_qty = min(array_values($tags));
echo ("Max: $max_qty<br/>Min: $min_qty<br/><br/>");

// find the range of values
$spread = $max_qty - $min_qty;
if ($spread == 0) { // we don't want to divide by zero
 $spread = 1;
}

echo ("Spread: $spread<br/>");

//Finally we start the HTML building process to display our tags.
$cloud_html = '';
$cloud_tags = array(); // create an array to hold tag code
foreach ($tags as $tag => $count) {
 // $size = $min_font_size + ($count - $minimum_count)
 $size = $min_font_size + ($count - $min_qty)
 * ($max_font_size - $min_font_size) / $spread;
 $cloud_tags[] = '<a style="font-size: '. floor($size) . 'px'
 . '" href="#" title="click this link...">'
 . htmlspecialchars(stripslashes($tag)) . ' ('.$size.' - '.$count.')</a>';
}
$cloud_html = join("\n", $cloud_tags) . "\n";
return $cloud_html;
}

print get_tag_cloud();
?>

See example output from script here

Tagcloud based on PHP and Mysql script

Conclusion

To get the script up and running, you need to create data in MySQL for the tagcloud. A simple logging function should be sufficient.

When data is ready in MySQL you need to change the MySQL table and field names in the script, and then you should be up and running.

The last thing to do is to create some nice styling to the tagcloud.

Share and Enjoy

  • Facebook
  • Twitter
  • Delicious
  • LinkedIn
  • StumbleUpon
  • Add to favorites
  • Email
  • RSS
fold-left fold-right
About the author
Jørgen Nicolaisen has been passionately interested in everything online since 1995. His experience is based on working with small hobby projects as well as high volume websites. Jørgen is currently focused on the PHP based programming framework - Codeigniter, and WordPress naturally

One Reply to Tagcloud with PHP and MySQL

  1. Heretic says:

    Thank you, great script! :)

Comments are now closed for this article.