PHP Tag Cloud Tutorial

Aug 22, 2006PHP, tutorial

Well, it’s actually so simple it’s not really a tutorial at all so much as a snippet with a bit of explanation. But I’ve had some people ask how it’s done, so here’s how I do it. I tried to go through and add some comments about what’s happening.

<?php

// connect to database at some point

// In the SQL below, change these three things:
// thing is the column name that you are making a tag cloud for
// id is the primary key
// my_table is the name of the database table

$query = "SELECT thing AS tag, COUNT(id) AS quantity
  FROM my_table
  GROUP BY thing
  ORDER BY thing ASC";

$result = mysql_query($query);

// here we loop through the results and put them into a simple array:
// $tag['thing1'] = 12;
// $tag['thing2'] = 25;
// etc. so we can use all the nifty array functions
// to calculate the font-size of each tag
while ($row = mysql_fetch_array($result)) {
    $tags[$row['tag']] = $row['quantity'];
}

// change these font sizes if you will
$max_size = 250; // max font size in %
$min_size = 100; // min font size in %

// get the largest and smallest array values
$max_qty = max(array_values($tags));
$min_qty = min(array_values($tags));

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

// determine the font-size increment
// this is the increase per tag quantity (times used)
$step = ($max_size - $min_size)/($spread);

// loop through our tag array
foreach ($tags as $key => $value) {

    // calculate CSS font-size
    // find the $value in excess of $min_qty
    // multiply by the font-size increment ($size)
    // and add the $min_size set above
    $size = $min_size + (($value - $min_qty) * $step);
    // uncomment if you want sizes in whole %:
    // $size = ceil($size);

    // you'll need to put the link destination in place of the #
    // (assuming your tag links to some sort of details page)
    echo '<a href="#" style="font-size: '.$size.'%"';
    // perhaps adjust this title attribute for the things that are tagged
    echo ' title="'.$value.' things tagged with '.$key.'"';
    echo '>'.$key.'</a> ';
    // notice the space at the end of the link
}

?>

Should give you something that looks like this (but as links if you so choose):

Thing 1 Thing 2 Thing 3 Thing 4 Thing 5 Thing 6 Thing 7 Thing 8

Hope someone finds this useful—I think it’s a really good way to visualize the popularity of any sort of categories: blog post tags, membership per country, songs per artist in your favorite playlist, etc.

EDIT 2006-09-03: Changed the font sizing to percentages (that’s somewhat better from an acessibility standpoint—text-size changing in MSIE—and how I usually do it in any case).

EDIT 2006-10-07:

After a few of the questions I’ve received, here’s a bit of an expansion on this technique. (A few others are answered in the comments, so be sure to read those, too!)

If you need more parameters than just the tag name to build your links, you can add anything else you need to an auxiliary array with the same index (e.g., the tag name. You could also use the primary key for your tag/category if your database is structured that way. The important thing is to have all the related data using the same index—you’re basically building a relational database in your array(s).)

while ($row = mysql_fetch_array($result)) {
    $tags[$row['tag']] = $row['quantity'];
    // same index as tags array
    $category_id[$row['tag']] = $row['category_id'];
}

Then, when you’re actually building the tag link within the for loop, you can access your other data with $key as the array index:

foreach ($tags as $key => $value) {

    $size = $min_size + (($value - $min_qty) * $step);

    echo '<a href="index.php?cat_id='.$category_id[$key].'"
      style="font-size: '.$size.'%"
      title="'.$value.' things tagged with '.$key.'">'
      .$key.'</a> ';
}

Prev: New comment policy
Next: War: A Collage

80 Comments

AnandAug 31, 2006

Any snippets to take URL from users and display the cloud for their density.
Can u please share if you have one ?
Thanks in advance

JennyAug 31, 2006

I’m not sure what you mean by ‘density’—I would need to know the structure of your source data. Can you explain? Are you thinking of some sort of visitor page view statistics?

MikeSep 13, 2006

How can I limit the cloud to a specific size? Let’s say I want it to only show 50 words MAX, maybe by date (since i store the date each time a tag is added), but more importantly, how do we limit to just X amount of words, otherwise it continues forever. Thanks!

JennySep 13, 2006

You would need to do that in your SQL query—that would probably be the easiest place.

SELECT thing AS tag, COUNT(id) AS quantity FROM my_table GROUP BY thing ORDER BY quantity ASC LIMIT 50

will pull out the 50 most popular tags (ordered most to least popular—you may want to sort the $tags array to alphabetize it).

If you want only tags used in the last month, try something like this (where ‘added’ is the timestamp column):

SELECT thing AS tag, COUNT(id) AS quantity FROM my_table WHERE added > DATE_SUB(NOW(), INTERVAL 1 MONTH) GROUP BY thing ORDER BY thing ASC

fabrizioSep 30, 2006

Hello! I have a table with a list of websites, and a table with a list of tags/categories. Than I have a third table (site_category) with the relations between “site_id” and “category_id” (the primary keys of the two first tables). If a certain tag/category has been used four times, I’ll find this information in this third table in form of four records. How would you do the SQL statement in this case?

JennySep 30, 2006

I think you’ll want something like this:

SELECT category AS tag, COUNT(site_id) AS quantity FROM site_category JOIN categories USING (category_id) GROUP BY categories.category_id ORDER BY category ASC

assuming your categories table is called ‘categories’. I don’t think you need to use the sites table at all in this case.

fabrizioSep 30, 2006

It works perfectly, thank you very much!

reverendOct 5, 2006

I’ve a table with rows for posts and a colum for tags (seperated by komma) in each row. So the solution mentioned above doesn’t really work fine for me… Any other idea?

JennyOct 5, 2006

I think you can still do a tag cloud something like this, you will just need to do the counting of tags yourself.

Retrieve only the tags column from your database, then do something like this in the while loop (assuming your database column is called ‘tags’):

while ($row = mysql_fetch_array($result)) {

    $temp_tags = explode(',', $row['tags']);

    foreach ($temp_tags as $tag) {
        if (isset($tags[$tag])) {
            $tags[$tag]++;
        } else {
            $tags[$tag] = 1;
        }
    }
}

This should populate the array so that you can just plug in the rest of the code (but I haven’t tested it, so not 100% sure).

MicahOct 12, 2006

First off, thanks for the great tutorial. Its been a lot of help for me.

Now I do have a question though. What if I want to get the tags from more than 1 row in the table? A friend of mine has a table has 3 rows I’d like to get the tags from, tag1, tag2, tag3. Not even close to an efficient way to do it, but I’d rather not have to change the way he stores his data because it would be a pain in the rear to do that when I’m certain there is just a little bit of a better mysql query I can use to “merge” the rows.

Any help with this problem would be greatly appreciated.

JennyOct 13, 2006

You could try a query like this:

SELECT tag1 AS tag, COUNT(id) + (SELECT COUNT(id) AS quantity FROM my_table WHERE tag2 = tag GROUP BY tag2) + (SELECT COUNT(id) AS quantity FROM my_table WHERE tag3 = tag GROUP BY tag3) AS quantity FROM my_table GROUP BY tag1 ORDER BY tag

It’s not very elegant, and pretty slow with all of those subselects, but it seems to work. (then again, I tested it on a table with 22,000 records, so it might be fast enough for your purposes.)

MårtenOct 20, 2006

Thank you so much for the tutorial, it looks great on my soon-to-be-released website macforbeginners.com.

However, one very very easy question. How can i include one extra thing from the database in every link?

while ($row = mysql_fetch_array($result)) {
echo ”.$key.’ ‘;
// notice the space at the end of the link

Would this be possible,

Thanks,
Mårten

JennyOct 20, 2006

If you look at the EDIT 2006-10-07 part at the bottom, I’ve addressed just that—you need to store any extra information in another array with the same indexes.

SallyOct 31, 2006

This is great, but I cannot get your explode suggestion to work for a column containing multiple, comma separated, tags. I replaced the entire while loop:

while ($row = mysql_fetch_array($result)) {

    $tags[$row['tag']] = $row['quantity'];
}

…with your new code exploding the row but nothing comes back…any suggestions?

JennyOct 31, 2006

Is your query returning a column named ‘tags’? (Or did you change the while loop code to match your column name?) Also, you should be retrieving every tag set, so no grouping in the SQL.

I’d really have to see your code and some sample data to debug it further (email me that if you like), but the first thing I would try is inserting some echo statements to see what is going on in the loop. Try echo $row['tags']."<br />/n"; and then echo $tag."<br />/n"; within the for loop. Also a print_r($tags); at the end of the while loop to see what is in there at the end.

Dan RussoNov 4, 2006

Great tutorial!

I’m having trouble though. I want to tag them by “title”. My titles are normally like 5 words. How would I separate the tile where there is a a space. (Example: PHP Tag Cloud Tutorial) Each tag would be “PHP” “Tag” “Cloud” “Tutorial”. So how would I separate each word in the title row? I tried the comment you post previously but it didn’t work. I know you would have to use explode(), just not sure how. Thanks Alot!

JennyNov 4, 2006

You need to do the same thing I mentioned before, but explode around spaces. You may also want to sort your tags array by key so it’s alphabetical when you’re done. Something like this:

$query = "SELECT title AS tag
  FROM my_table";

$result = mysql_query($query);

while ($row = mysql_fetch_array($result)) {

    $temp_tags = explode(' ', $row['tag']);

    foreach ($temp_tags as $tag) {
        // normalize to lower case
        $tag = strtolower($tag);
        if (isset($tags[$tag])) {
            $tags[$tag]++;
        } else {
            $tags[$tag] = 1;
        }
    }
}

ksort($tags);

mad4Nov 9, 2006

Has anyone got the code to work for a column of multiple tags separated by spaces or commas? Nothing seems to work for me.

Susan KitchensDec 5, 2006

Beautiful snippet. It’s a huge help. Huge. Many thanks.

I’m not highly proficient at PHP, so it took me a bit of struggle to implement it with my tortured sql query to pull out all the bits.

I appreciate the 2006-10-07 edit with the code for bringing additional items in. Like I said, took a bit to get it to work (now understand arrays a bit better), but it does now and so I am thrilled. The issue had been one of those back-burnered, “yeah, I really need to figure out how to do that” things, and I feel much lightness having done so.

ArthurDec 28, 2006

Thanks. Really nice code, with multitag upgrade it is better than i was even searching for.

RamaJan 13, 2007

Excellent script!!!
But…how do I split word in my tag?
If i have a description or a tag like “Hello my name is peter”
i want to generate tags like “Hello” and “my” and “name” and “is”…etc…

How could i do it?

BentisJan 18, 2007

Article gave a good pointer on how to solve my tag cloud problem.
Had the tables all set up before i started on the cloud, with two tables: a video table with a unique video_id, and a tag table with id,tag and video_id to which it belonged.
This is the query I ended up with:

SELECT t.name AS tag, COUNT(t.video_id) AS count FROM tags t JOIN videos v USING (video_id) GROUP BY t.name

Maybe it can help someone else =)

felipeJan 28, 2007

jenny, if you have some time can you help me with this, is about the tag cloud, how can i make it not to count the rows, since i have this

TAGS1 | VOTES

hola 2
dog 5
little house 2
casa 3

so the votes tells the value or how many times the tag repeats. any help will be great, thank you for your time… felipe

adastraFeb 1, 2007

Very good tutorial! But how can I include a function which not only shows different font sizes, but also a different color values (or maybe opacity)?

MehFeb 3, 2007

You would need to do that in your SQL query— that would probably be the easiest place. SELECT thing AS tag, COUNT(id) AS quantity FROM my_table GROUP BY thing ORDER BY thing ASC LIMIT 50 will pull out the 50 most popular tags.

Er, why should that pull out the most popular tags? It’s sorted by the name of the tag, rather than its quantity. That query will pull out the 50 tags nearest to A in the alphabet, which seems rather pointless…

JennyFeb 3, 2007

@Rama: Try what I’ve got in this comment.

@Felipe: Since you’ve already got your tags counted up, you could use a database query as simple as this:

"SELECT tags1 AS tag, votes AS quantity FROM my_table ORDER BY thing ASC"

@Adastra: Not really sure. For opacity, you could play with something very similar to how the font size is calculated, but make the min and max values something like 0.2 and 1.0.

I experimented with colors once for a bar graph function, where lower values are more blue-green and higher values are more yellow-orange, and this is what I came up with. Perhaps it might help you (I found it sort of inflexible):

$max_size = 250; // max font size in %
$min_size = 100; // min font size in %

$max_sat = hexdec('f');
$min_sat = hexdec(0);

$max_qty = max(array_values($tags));
$min_qty = min(array_values($tags));

$spread = $max_qty - $min_qty;
if (0 == $spread) { // Divide by zero
    $spread = 1;
}
$step = ($max_sat - $min_sat)/($spread);

foreach ($tags as $key => $value) {

    $color = $min_sat + ($value - $min_qty) * $step;

    $r = dechex($color);
    $b = dechex($max_sat - $color);
    $g = 'c';

    $size = $min_size + (($value - $min_qty) * $step);

    echo '<a style="background: #'.$r.$g.$b.'; font-size: '.$size.'%;">'.$key.'</a>';
}

Alternatively, you could have a range of CSS classes defined (perhaps .tag1 through .tag9), and calculate a size between 1 and 9, then round up to a whole number and use that to name the class of your link.

$max_size = 9;
$min_size = 1;

// other stuff as before

foreach ($tags as $key => $value) {

    $size = ceil($min_size + (($value - $min_qty) * $step));

    echo '<a class="tag'.$size.'">'.$key.'</a>';
}

@Meh: Oops—you’re completely right. That should be “ORDER BY quantity ASC LIMIT 50“. And then perhaps sort the array in PHP if you want it alphabetized. Fixed now.

ericbFeb 4, 2007

Nicely done. I’ve implemented something with your snippet, but it turns out that the distribution of my tags is such that there are tons of small names, and a handful of big ones. This is clearly because out of 10,000 rows in my tag data (146 tag names) a couple have counts 2000 , but most are between 2 and 50. Can you think of a different method of allocating the font size that takes that into account? It’s probably just math, but that stuff always slows me down.

Thanks again for the great snippet.

JennyFeb 5, 2007

You could have a look at this algorithm: Tag Cloud Font Distribution Algorithm

It looks like it would take care of exactly your problem, but the math is definitely more complicated.

MikeFeb 12, 2007

Finally some PHP code, thanks brother!

KerryFeb 13, 2007

Jenny,

Would you be kind enough do give me some example code of how to sort a array in php to display the 50 most popular tags in alphabetical order? I have everything working but sorting the array. Thanks in advance!

JennyFeb 13, 2007

Kerry, all you need is to put this right after the while loop:

ksort($tags);

Alex EzellMar 9, 2007

Just wanted to say thanks for this code. It came in very handy and is nicely done.

Anon.Mar 17, 2007

Great tutorial! I modified it significantly for pulling the top 50 tags and then sorting them alphabetically by doing the first query sorted by quantity DESC, limited to the top 40. I put those results into a temporary table, and then queried the temp table sorting by tag. Here’s my first query:

$query = “CREATE TEMPORARY TABLE `mytempcloud` SELECT tagname AS tag, count(tagname) AS quantity FROM `tags` GROUP BY tagname ORDER BY quantity DESC LIMIT 40″;

Then:

$query = “SELECT * FROM `mytempcloud` ORDER BY tag”;

VolgMar 22, 2007

You can employ more than one color with discretion.

One way to use colors in your cloud tag:
- select a main color, assign it to the biggest font;
- “blend” this color for smaller fonts.

A “coloring” example at
http://www.featurepics.com/editorial/tag-cloud.aspx

timsApr 23, 2007

Hey, thanks very much for this little snippet. I have a table with a column called tags which contains tags separated by commas (as several people have mentioned earlier). The first suggestion worked fine, though there was a missing ; which may be why it didn’t work for others.

[EDIT: Thanks! missing ; fixed. —Jenny]

NickApr 27, 2007

How can i shuffle the array?

I tried to switch ksort($tags) to shuffle($tags) but it just returned numbers…

Any idea?

JennyMay 1, 2007

Because: Note: This function assigns new keys for the elements in array. It will remove any existing keys you may have assigned, rather than just reordering the keys.

You need to keep the array keys intact—those are your tag names. Have a look at this php.net shuffle() comment; it may help.

DelJun 2, 2007

Very nice database driven tag cloud.

Thanks for sharing!

AzamJun 7, 2007

Excellent script. And Jenny is really very helpful person. I asked for it she provided me very quickly and exactly what I wanted. Thanks a lot.

sylvainJun 7, 2007

great, many thanks, it looks so simple now !

yours,

sylvain

adkdevJun 12, 2007

Thanks for your idea.

jasonJun 14, 2007

Jenny,
In using your code with my tag cloud function, utilizing the max/min sizes, the output of the cloud only results in the max and min sizes. What could I be doing wrong?

// Following 3 functions for tag cloud
function EntryExist($out, $value) {
	foreach ($out as $K => $v) {
        	if ($K == $value) { return TRUE; }
    	}
    	return FALSE;
}

function keyword_array () {
    	$out = array();
    	connect_to_db();
    	$db = new Database(s('dbname'));
    	$query = "SELECT * FROM " .s('prefix'). "articles WHERE published = 1";
    	$result = $db->query($query);
    	while ($r = $result->fetch_array()) {
        	$keywords = explode(', ', $r['keywords_meta']);
        	foreach ($keywords as $value ) {
            		if ( !EntryExist( $out, $value )) {
	              		$out[$value] = 1;
            		} else {
                		$out[$value]  = 1;
            		}
        	}
    	}
	ksort($out);
    	return $out;
}  

function keyword_cloud() {
	$keywords = keyword_array();
	$max_size = 250;
	$min_size = 90;
	$max_qty = max(array_values($keywords));
	$min_qty = min(array_values($keywords));
	$spread = $max_qty - $min_qty;
	if (0 == $spread) {
		$spread = 1;
	}
	$step = ($max_size - $min_size)/($spread);
	foreach( $keywords as $K => $V ) {
	    	$size = $min_size + (($V - $min_qty) * $step);
	//    	echo '<a class="t'.$V.'" href="index.php?search='.$K.'">'.$K.'</a> ';
    		echo '<a href="index.php?search='.$K.'" style="font-size: '.$size.'%"';
		echo ' title="Things tagged with '.$K.'"';
    		echo '>'.$K.'</a> ';
	}
}

JennyJun 14, 2007

Jason, the problem is where you use !EntryExist in your keyword_array function. You are always setting the tag count to 1. What you want there is:

if ( !EntryExist( $out, $value )) {
    $out[$value]++;
} else {
    $out[$value] = 1;
}

Otherwise, your code seems to work fine for me. Possibly your data set doesn’t have any intermediate values?

EirikJun 19, 2007

I love the result of this snippet, but I need one more field from the database. Since Norwegian characters is the wrong way to go for the POST data (witch is the way I send the info to another page, and I’m Norwegian), I can’t use the tagName in the URL, I must use the tagID instead. But how do I do that with in the array? I want the result to be < anchor “url/file.php?=tagID”>tagName</ end anchor >. Any Ideas?

EirikJun 20, 2007

Sorry about that… you’ve already answered my question.

I wanted the different sizes to have a slightly different color, so a added this to give different CSS classes.

 	  if ($size > 220) {
                $class = "XXL";
            } else if ($size > 190) {
                $class = "XL";
            } else if ($size > 160) {
            	$class = "large";
            } else if ($size > 130) {
            	$class = "small";
            } else if ($size > 110) {
            	$class = "xsmall";
            } else {
            	$class = "xxsmall";
            }

And added class=”‘.$class.’” to the anchor tag.

Thanks for a good tutorial, it came in very handy, and I learned from it.

royalJun 28, 2007

cool, cool, cool !

thanks

Lars KoudalJul 2, 2007

Thanks for the excellent code! I’m currently working on a new version of a popular video site, where I wanted to implement a tagcloud based on number of keyword-references vs. views of the individual videos. This is perfect, and it worked quite easily with a bit of tweaking.

Thank you again Jenny, you have a new fan! :-)

MattAug 2, 2007

Hi Jenny, I love your snippet and have used it on a few of my sites. I’ve run into a problem though, I am currently running a site that has a large amount of tags and they have been separated into various tables depending on the category such as sports etc. So how can I join multiple tables ,four, when they have the same structure: id, tag, addedBy, to make one big super cloud of all of someone’s tags on their profile page?

Thanks for all your help!

Fernando F.OliveiraAug 6, 2007

Have you tryed to use UNION SQL command on your 4 (four) tables to join the tags ?

martinAug 14, 2007

Just a quick note to say thanks. Took me 10 minutes to get it working, and only that long to my unfamiliarity with My SQL. One gotcha is the COUNT(id) in the query. it took me a few minutes to realise since tag was the primary key of table, I could use COUNT(*) i.e.

$query = “SELECT tag, COUNT(*) AS quantity FROM tags GROUP BY tag ORDER BY tag ASC”;

once again thanks for the code :)

BarryAug 15, 2007

Hi Jenny

Well I dont have any queries or explainations however I have to say you have a really nice snippet here that has turned most def into a tutorial! Thanks alot for keeping it up to date, consider it bookmarked and I will be back later when I have my coding hat on :)

Cheers

Barry

AdamAug 23, 2007

I needed to do an inner join to combine my two tables, here’s how:

SELECT title_top AS tag, COUNT(title_top) AS quantity, id_top AS link FROM blg_topic_top INNER JOIN blg_article_art ON idtop_art = id_top GROUP BY title_top ORDER BY RAND()

John CSep 1, 2007

This is outstanding! Thank you very much, from Washington DC

airbuzzSep 10, 2007

very nice !! thanks a lot for sharing it works perfectly !

Anon.Sep 11, 2007

Nice code, a great time saver!!! keep up the good work!

AlexanderSep 17, 2007

I tweaked your script to make a stock symbol cloud: http://investsourceinc.com/cloud.php

Problem was that change for one symbol could be 9000% while for majority of the rest under 100%. So I had to limit change to something reasonable, so anything above the limit would have same size.

SDOct 14, 2007

Jenny,

Thanks for the code. I had built a tag cloud, but wanted to improve it to query the top X number of tags and then sort them alphabetically. Your code for putting the MySQL result into an array worked perfectly and saved me a ton of time.

As an aside, some of my tags include spaces (e.g. “New York”), which seems to work fine as a key. I can’t find anything on the internet regarding permitted characters in array keys, but I suspect it’s probably not a best practice to use spaces.

supakNov 27, 2007

good looking and skilled, what a combiantion :) thanks.

mattDec 3, 2007

thanks for this good tutriolis
how i can print many words from full text (db)
example:
if i have this text: Enter the key you see above
and want display it as tag like
enter,the,key,you,see,above

how to do it

David HallDec 3, 2007

Jenny,

Great snippet / Tutorial!!!

I have a DB with 1 table called bookmarks with 4 fields (id, name, url, cat). Your code above creates the cloud beautifully. I would like to make the cloud linkable so that when I click on one of the cloud links it pulls everything with that cat into a page for me.

Right now all the cloud links end up like this in the browser and link to nothing. /index.php?cat_id=

Any help would be greatly appreciated.

Thanks

Here is the code:

$query = “SELECT cat AS tag, COUNT(id) AS quantity
FROM bookmarks
GROUP BY cat
ORDER BY cat ASC”;

$result = mysql_query($query);

// here we loop through the results and put them into a simple array:
// $tag[’thing1′] = 12;
// $tag[’thing2′] = 25;
// etc. so we can use all the nifty array functions
// to calculate the font-size of each tag
while ($row = mysql_fetch_array($result)) {

$tags[$row[’tag’]] = $row[’quantity’];
}

// change these font sizes if you will
$max_size = 120; // max font size in %
$min_size = 75; // min font size in %

// get the largest and smallest array values
$max_qty = max(array_values($tags));
$min_qty = min(array_values($tags));

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

// determine the font-size increment
// this is the increase per tag quantity (times used)
$step = ($max_size—$min_size)/($spread);

// loop through our tag array
foreach ($tags as $key => $value) {

$size = $min_size + (($value—$min_qty) * $step);

echo ‘<a href=”index.php?cat_id=’.$category_id[$key].’”
style=”font-size: ‘.$size.’%”
title=”‘.$value.’ things tagged with ‘.$key.’”>’
.$key.’</a> ‘;
}

JennyDec 4, 2007

David, you’re just missing one thing. You need to select your tag ID, and save it to an array so you can access it when building your cloud.

The first bit of your database select should be:
SELECT cat AS tag, COUNT(id) AS quantity, id as tag_id

And then you need this inside your while loop:
// same index as tags array
$category_id[$row[’tag’]] = $row[’tag_id’];

David HallDec 4, 2007

Jenny,

Thanks I get the output but apparently I have something else wrong. Here is the cloud page.

http://www.racinnation.com/sandbox/bookmark/test.php

Notice when you click on a link all the records are displayed, it does not pull only those records with the cooresponding tag.
My DB has 1 Table (bookmarks) with 4 fields (id, name, url, and cat). I have a form putting my tags in the cat field separated by commas. Here is my code.

// Connect to server and select databse.
mysql_connect(”$host”, “$username”, “$password”)or die(”cannot connect”);
mysql_select_db(”$db_name”)or die(”cannot select DB”);

$query = “SELECT cat AS tag, COUNT(id) AS quantity, id as tag_id
FROM bookmarks
GROUP BY cat
ORDER BY cat ASC”;

$result = mysql_query($query);

}

// change these font sizes if you will
$max_size = 120; // max font size in %
$min_size = 75; // min font size in %

// get the largest and smallest array values
$max_qty = max(array_values($tags));
$min_qty = min(array_values($tags));

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

// determine the font-size increment
// this is the increase per tag quantity (times used)
$step = ($max_size—$min_size)/($spread);

// loop through our tag array
foreach ($tags as $key => $value) {

$size = $min_size + (($value—$min_qty) * $step);

echo ‘<a href=”index.php?cat_id=’.$category_id[$key].’”
style=”font-size: ‘.$size.’%”
title=”‘.$value.’ things tagged with ‘.$key.’”>’
.$key.’</a> ‘;
}
?>

Any idea ho I can fix it?

Edward HanDec 5, 2007

Hi Jenny

I am having problem with showing my tag as individual word. Currently the tags are typed in the format separated by commas or spaces. Example:
Tags: thing1, thing2 thing3, thing4 thing5 thing6, thing7 and so forth
These are type in by users where I have no control over and it is being saved as text in the mysql database under the field as Tags.

Using your tutorial, the result of the Tags ended up as:

Tag Cloud:
thing1, thing2 thing3, thing4 thing5 thing6, thing7 (as a single link when you have mouse over instead of a single word like thing1 when mouseover).

How do I overcome this to show as individual word for the link?

JennyDec 6, 2007

@Edward: See this comment: #c1163

You will need to split your text around spaces and commas, so the explode line in that comment should be something like this for you:

$temp_tags = preg_split(”/[\s,]+/”, $row[’tags’]);

mattDec 6, 2007

jenny can you tell me how i use
$temp_tags = preg_split(”/[s,]+/”, $row[’tags’]);
in this fucations please
//tag news
function tag_tot() {
$result = mysql_query(”SELECT * FROM news GROUP BY title ORDER BY Rand() DESC Limit 4″);
while($row = mysql_fetch_array($result)) {
$arr[$row[’title’]] = $row[’count’];
}
//ksort($arr);
return $arr;
}

function tag_news() {

$min_size = 20;
$max_size = 60;

$tags = tag_tot();

$minimum_count = min(array_values($tags));
$maximum_count = max(array_values($tags));
$spread = $maximum_count—$minimum_count;

if($spread == 0) {
$spread = 1;
}

$cloud_html = ”;
$cloud_tags = array();

$step = ($max_size—$min_size)/($spread);

foreach ($tags as $tag => $count) {
$size = $min_size + ($count—$minimum_count)
* $step;

// $size = ($max_size + $min_size)/$spread;
$cloud_tags[] = ‘<a style=”font-size: ‘. floor($size) . ‘px’
. ‘” class=”tag_cloud” href=”http://game.paramegsoft.com/tags-’ . $tag
. ‘.html” title=”” . $tag . ” ÇáÚÇÈ ‘ . $count . ‘”>’
. htmlspecialchars(stripslashes($tag)) . ‘</a>’;
}
$cloud_html = join(”n”, $cloud_tags) . “n”;
return $cloud_html;

}

Edward HanDec 12, 2007

Hi Jenny

How do I assign font color for different size?
Thanks!

coldclimateDec 18, 2007

You are amazingly patient at repling to all the people who have problems with this—impressive.

IgorJan 7, 2008

Hi,
Awesome pice of work!

I did, however, had to tweak it a little, and it took some time to figure out where the issue was.

case: i have several tag fields which hold the exact same keywords (tags) for that respective article. Though they are 2 different articles, they may share the same keywords (tags) in the same order. Through your functions it didn’t show all the keywords, meaning to say, it did show the keywords, but the count of how many there were was off.

I have simply (i say now, but not while i was searching for it) added the id field to the GROUP BY parameters, this because the SELECT statement drops every bit of data except for the data which was mentioned in the GROUP BY statement, meaning to say, it recognized the words correctly, but not the count of how many instances.

SELECT keywords AS tag, COUNT(id) AS quantity FROM articles GROUP BY keywords, id ORDER BY keywords ASC

Keep up the good work!

LynnFeb 1, 2008

Merci for this post ;)

Andy ClarkeFeb 4, 2008

Hey,

Thanks for the tag cloud code. Just what I was after. I’ve modified it slightly, just to order my results by rand(). If it’s OK with you i’m going to use this code snippet in my final year uni assignment (referenced to you of course).

Regards,

Andy Clarke
BSc Information Systems Engineering
The University of Manchester, England

JennyFeb 19, 2008

@Andy: Perfectly fine. I should probably attach some sort of official license to the code, but to be brief, I don’t care what you do with it. It’s so simple I don’t really care about attribution (although your professors may).

MaximusFeb 21, 2008

I was wondering if someone could tell me how to display a link instead of the letters of the url when i call it from mysql to display on my web site. http://www.pennsylvaniarednecks.com/companies1.php if you go there you will see that the url is written totally out. I would like just to display it as a link. Can anyone tell me how its done? Thanks

SachinFeb 21, 2008

Very good tutorial.
Thanks

thawootahMar 14, 2008

yeah pretty good tut!

I’ve been searching around for the best way to implement a Folksonomy tag feature for a custom CMS.

so far i came across some really good ideas.

if you have tons of rows and tags like Delicious.
http://forge.mysql.com/wiki/TagSchema

this guy went all out and ran some tests. His whole blog is pretty much dedicated to queries and tag clouds. This is definitely a good read for anyone wanting to compare schema designs for tagging.
http://www.pui.ch/phred/archives/2005/06/tagsystems-performance-tests.html

peace

AndreasMar 29, 2008

Hi Jenny,

Just want to say thanks for explaining the tag-cloud with PHP code. I hope it’s ok that I embedded snippets of it into the OpenSource project phpfspot. A link back to your website is added into the credit page.

Cheers,

Andreas

ShaiApr 30, 2008

Hi Jenny,

Thanks for the great tutorial. I am having trouble getting it to work though!

My database schema is similar to fabrizio’s:

Table1 contains articles and article IDs (column: article_id)
Table2 contains individual tags (column: tag) and tag IDs (column: tag_id)
Table3 associates article IDs (column: article_id) with tag IDs (column: tag_id)

When I tried altering your suggested code of
SELECT category AS tag, COUNT(site_id) AS quantity FROM site_category JOIN categories USING (category_id) GROUP BY categories.category_id ORDER BY category ASC
to create a tag cloud with my table names and columns, it just doesn’t work (mysql_fetch_array(): supplied argument is not a valid MySQL result resource).

I am confused I guess because you told fabrizio he wouldn’t need to join the third table (site_category) yet it appears in your suggested code! I also don’t understand why the site_id (aka article_id for me) is being counted. Also, where is the actual text tag being selected from? If I understand correctly, in fabrizio’s example the tag should be in categories, not site_category (which should only have integers). So…I also don’t understand how fabrizio could have said it works perfectly!

If you could help, much appreciated!

Shai

NucleocideMay 1, 2008

Just wrote a PHP tag class and was seeing how others did it, our code is quite similar. I have a tutorial site as well and I’d like to give you kudos for answering so many of these peoples questions; I never go as far as to write an SQL query for a visitor.

ShaiMay 3, 2008

: ) I was able to figure it out! For whatever reason, the problem was the syntax of the query. Instead of the suggested query, I had to use:

SELECT tag, COUNT(*) AS quantity FROM tags, article_tags WHERE tags.tag_id = article_tags.tag_id GROUP BY article_tags.tag_id ORDER BY tag ASC

Hope that helps someone else!

LesterMay 3, 2008

Hi Jenny, i have seen your tag code but i have no idea how i can apply your code to my site.

I’m buidling a youtube like website and i need to tag all my videos so my user can easily link to any videos from the tag. Do you know how?

My mysql table name is "video"
And under my table i have
video
description
image
title
genre
tag
video_id (primary key)

Hope you can help me with this thanks

FreudMay 5, 2008

Jenny!

I want to add a search cloud to my homepage, and its a dictionary. therefore it will contain a lot of "tags". I only want it to display the 50 (or so) moast searched words. can you please help me?

Jon

Feed: Comments

Things to know:

No HTML please—use BBCode for [i] [b] [q] [code] and [url=]
Read the comment policy if you’re curious.

PHP Tag Cloud Tutorial

80 Comments

Leave A Comment

Things to know: