Subscribe to Tutorial Feed

Flash and PHP Bible

The Flash and PHP Bible has been released! The book can be found on Amazon or wherever fine books are sold in your area.

The Flash and PHP Bible has a forum for quick support.

Scriptplayground » tutorials » php » Tag Cloud with Database

Tag Cloud with Database

A continuation of the Tag Cloud tutorial using a database instead of a static array.

A few weeks ago we built a Tag Cloud using PHP and being fed by an array. This works well but really only is for concept since you would always need to be updating your array. This time we are going to build the same basic end result, but using a database instead of and array.

We will start off with creating the SQL table and that is done using CREATE TABLE. This table will have 3 columns (id, tag, count).

CREATE TABLE tags ( 
id int(11) NOT NULL,
tag varchar(100) NOT NULL,
count int(11) NOT NULL DEFAULT '0'
);

Using whatever method you use, create the table in your database and then we will continue.

Next we need to modify the function "get_tag_data()" that read in the array in the previous tutorial.

function get_tag_data() { 
  mysql_connect('localhost', 'username', 'password');
  mysql_select_db('database');
  $result = mysql_query("SELECT * FROM tags GROUP BY tag ORDER BY count DESC"); 
  while($row = mysql_fetch_array($result)) { 
    $arr[$row['tag']] = $row['count'];
  } 
  ksort($arr); 
  return $arr; 
}

That is the new function, lets go through each step to get a better understanding of how it all works. The first step is to connect to the database, if you unfamiliar with this process check out this database connection tutorial. Then we query the database, using a "while" loop we grab our tags and build an array. The end result of this process is the same as the previous one, except for one major difference, we no longer have to edit the array. Use "ksort" to rearrange the tag list and finally return the new array just like we did before.

That entire process is pretty straightforward and from this point you use all the same code from the previous tutorial, only substituting the function.

If you have any questions, please post away.

Follow Scriptplayground on Twitter (@scriptplay)

Comments: Tag Cloud with Database

 thomas  Sat Jul 8, 2006 1:56 am  
it's awesome, thank you so much, i left a post on the other one about what happened.
 thomas  Sun Jul 9, 2006 3:58 am  
well, i got it working, and added it to my "add page" script and used so amyn if else statements and after hours of tweakin finally got it working with posting and everything i want. So i was just wondering, if i want it to go into another table and look for the tags there in a column called tags and then from there i want it to count the duplicates and then delete them and show them as the count. Wow, that seem skinda complicated in a way, but is it possible, if so, could i get an example?
 Matthew Keefe  Sun Jul 9, 2006 8:19 pm  
Are you wanting this to be done in PHP or entirely in SQL? If you want it in SQL the table would need a unique column or key.

Matt
 Max  Mon Aug 28, 2006 5:38 pm  
Hi Matthew,

Thanks for the great tutorials. Forgive my lack of MySQL knowledge, but I have my system setup a bit different. In the Articles table there is a column "tags". In "tags" I have the tags separated by a comma. So for example, in one row the column tags might be "html, easy html, code".

The problem is, I have no idea how to alter your get_tag_data() function to adjust to this setting. Could you please help me?

Thanks
 Matthew Keefe  Mon Aug 28, 2006 6:35 pm  
Hey Max,

That is a fairly simple task, you can use explode() and generate an array of tags. However I am not sure where or if you are using a count? You could write a function to count the tags, but I suggest you cache this result to ensure your server isn't bogged down on every request. If you have any other questions just let me know.
 Max  Mon Aug 28, 2006 6:53 pm  
Matthew,

No, I'm not using any sort of count. Holding down the server is the least of my concerns, at this point.

I'll see what I can do. As I said, my MySQL skills are very limited. I'll make sure to post then code when/if I figure it out.

Thanks again!
 Matthew Keefe  Mon Aug 28, 2006 7:09 pm  
Since you learn from doing I will let you tackle this one, but if you have any questions you can catch me on aim: thephpdeveloper

Matt
 Bill  Thu Dec 14, 2006 6:57 am  
Hi Matthew

In this tutorial you have each tag leading to a search term on Google. I'd like to change this to lead to a separate page within my site. The problem is, if I have a tag called "Tag 1" and the file on the server is "tag_1" - what do I need to change in the $cloud_tags = array(); to make this possible?

I'm thinking possibly adding another field in the SQL which will contain the file name so that "Tag 1" is still displayed in the cloud but when you click on it you are taken to the "tag_1" file. But my lack of knowledge has me puzzling over the code!

I hope this makes sense? - Bill
 Tim  Tue Dec 19, 2006 3:34 am  
Matt,
Great idea... i'll impliment this hopfully tonight when i get home... pregnant wife allowing! lol
it's just what i have been looking for!!! many thanks

Tin
 Matthew  Tue Dec 19, 2006 9:06 am  
@Bill,
In order to use them as a link you would need to add a link column in your sql and assign that to an href. The SQL will be mostly the same, just add the link column: link varchar(255) NOT NULL, and replace the reference to "Google" in your code with the same format as the name/count uses. Post away if you have questions.

@Tim,
Good luck and if you have questions just ask.
 ElTioFabi  Wed Apr 11, 2007 3:19 pm  
I do follow the steps as you describe in this topic, but in the Tags cloud, there appears allways that I have a "count of 0" if I put the mouse over a tag.
I do have some tags more than once, but it allways shows 0 value. As a result, the font size does not change at all in the tags.
What do I'm doing wrong?
 mkeefe  Wed Apr 11, 2007 5:01 pm  
make sure you are connected to the database and getting back a good response. Sometimes using "print_r()" is a good way to make sure you are getting data back.
 ElTioFabi  Wed Apr 11, 2007 7:22 pm  
mkeefe, I'm getting back a good response. In fact I'm getting the tags back, but the problem is that it swhows the tags with the same font size and saying that there is "count of 0" of every tag.
 mkeefe  Wed Apr 11, 2007 7:39 pm  
If you use the "array based" example does everything work properly? Reason being only one function is changed in this version. Let me know.
 ElTioFabi  Wed Apr 11, 2007 7:48 pm  
Yes, with the "array based" it works properly.
Here you have the example in my site database based http://www.qdlinks.com.ar/tools/tags/index22.php so you can check the result. The "Liniers" tag have 3 entries on the table, but there is no change on the font size and there is also no changes on "count of" because it appears always in 0.
(Sorry to bother you with this)
 mkeefe  Thu Apr 12, 2007 1:27 am  
You shouldn't have to apologize that is what I am here for. Can you change the extension of that page to .phps so I can see the code. Or you can email it to me (make sure you remove the database credentials)
 ElTioFabi  Thu Apr 12, 2007 8:19 am  
mkeefe

there you have the file renamed at http://www.qdlinks.com.ar/tools/tags/index22.phps

Thanks again
 mkeefe  Thu Apr 12, 2007 9:20 am  
After looking at the code I noticed it all looks good, however do you have the column names identical "count" since that could also cause a problem.
 ElTioFabi  Thu Apr 12, 2007 9:33 am  
Yes, the column names are id tag and count.
In fact, if I modify the count value on the table to any tag manually, the font sizes does change.
I do also try with a separate table, but with the same results.
 mkeefe  Thu Apr 12, 2007 10:22 am  
so if you change a count manually the fonts change? That means the viewer portion is working, but the tag data isn't being updated.
 ElTioFabi  Thu Apr 12, 2007 10:37 am  
But the tag data is actually beeing updated, because I can see it on the table.
 mkeefe  Thu Apr 12, 2007 7:04 pm  
if you view the database then you see the count updating? If that is the case then the last thing I know could be a messed up column (as in the incorrect type)... maybe. :)
 ElTioFabi  Thu Apr 12, 2007 7:13 pm  
I mean, I see the table. I could see the tags on the table, but the count value (on the table) remains 0 for every tag.
But this I though it was because the value for count is DEFAULT '0' when I create the table.
So I have '0' for each tag entrance in the table.
And if I have more than one tag more than once, the count remains in '0' on the database.
 mkeefe  Thu Apr 12, 2007 7:39 pm  
Could you possibly be adding a new tag each time instead of updating the existing one?
 ElTioFabi  Thu Apr 12, 2007 7:46 pm  
ohhhh I see
You're right, I'm adding a new tag each time.
So now I have to see how to do it in other way not to add a new tag each time
Thank you very much for your help!
 mkeefe  Thu Apr 12, 2007 7:57 pm  
No problem, basically you want to check for the tag first and then update the count if it already exists or add it if it doesn't exist. :)
 tullyman  Fri Apr 13, 2007 2:10 pm  
how would i setup the while statement to make this access a phpbb2 database? i wanna display something along the lines of the latest posts by subject.
 mkeefe  Fri Apr 13, 2007 2:59 pm  
In order to work with the PHPBB system you would access the table "phpbb2_posts" or something similar. I am not 100% sure why you would use this system for latest posts, unless you are going also grab a post count to differ the tags.

Hope this helps.
 Scott  Thu Apr 19, 2007 7:59 pm  
Thanks for the great tutorial, I'm having a problem getting the explode function to work with tags separated by commas though.

Right now I'm working with the sample code and I'm not sure whether "$result" or "$arr" is what I'm trying to explode. I've tried it both ways and I either get a bunch of line errors or a tag of "0". Thanks in advance for any guidance.
 mkeefe  Thu Apr 19, 2007 8:30 pm  
Are you loading in a custom array? The reason I ask is because this database version returns an array already separated for easy use. Let me know if I misunderstood your question.
 Scott  Sat Apr 21, 2007 4:53 pm  
Sorry, I'm new to PHP so I probably wasn't clear enough...

My setup is similar to Max above (8/28/06) in that I have a column for tags associated with all the content in the rest of the row. What I'm trying to do is return only the tags associated with the rest of the content (like a blog entry, essentially) instead of one big tag cloud. But when I do it they come out as one tag (so, for example, "tag1, tag2, tag3" as opposed to "tag1", "tag2", "tag3").

I assumed that I needed to use the explode function somehow to break them up so they would be separate links. But I'm not sure what part of the code needs to be exploded, or really if I'm even on the right track.
 mkeefe  Tue Apr 24, 2007 8:44 am  
Something doesn't seem right here, but the array should be individual elements with the tag name as the key, so for instance:

$arr['myTag'] = 5;

That is essentially how the code writes out the array, which is why I commented on needing to explode the array.
 Nmixer  Mon May 14, 2007 6:09 pm  
Well, this is a great tutorial as every one else has said, i had some problems to getting it to work with the DB, but i'm trying to rewrite the code differently.

I have a question. I have a function thats giving this as its output: 'apple'=>4,'cat'=>2,'ect'=>55,

I was hopping it would be possible to use that function in another function to create an array, but it doesn't seem to work heres what i have

function make_data() {
$c_t = array(get_data());
echo $c_t[2];
}

any ideas?
[i have about 3 days of searching on php tutorials of experience, just in case your wondering ]
 mkeefe  Mon May 14, 2007 7:12 pm  
By chance are you using print_r() or a similar function, because that is the visual structure of an Multidimensional Array. Normally you wouldn't see that... unless you meant to. Feel free to sign up over at splounge.com and ask any and all scripting questions.
 Nmixer  Mon May 14, 2007 9:27 pm  
Hey thanks for the quick replay, i really appreciate it. I'll check out splounge.com :) and no i'm not using print_r()... i'll check what it does. Thanks mkeefe.
 Jhecht  Tue Jun 5, 2007 7:54 pm  
Hey Matt, thought i would just put up my solution to this.

What i did was create 2 tables, one called cloud_tags with three columns,
cloud_id int unique not null auto_increment,
cloud_tag varchar(255) not null,
cloud_postid int not null

the other table was called posts with these columns:
post_id int not null auto_increment,
post_title varchar(50) not null,
post_text text not null

In my code i simply just looped through the post rows, and called a query to the cloud_tags table like this: SELECT c.cloud_tag,count(c2.cloud_id) as cloud_count FROM cloud_tags c, cloud_tags c2 WHERE c.cloud_postid = ".$post_id_var." AND c2.cloud_tag = c.cloud_tag"; (something along those lines, if that doesn't work then i'll actually post up the code that i used).

If you want to download an example of the thing i did, check out the forums for script playground at splounge.com, it's under the php forums.
 Mellowman  Wed Aug 22, 2007 10:27 pm  
Hi, I have the tags working and have the DB tables created, but how do I tie my existing website search function into this table to catch the search terms that my users are typing in and log them?
 ady  Sat Sep 29, 2007 5:42 am  
cool tutorial
 kan  Sun Dec 2, 2007 1:40 pm  
how i can use str_replace
to print many words from full text
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
 cedric x  Mon Dec 17, 2007 9:50 pm  
Hi Jhecht, could you please post your code? I think I am figuring the same that you did. Thanks in advance.
 susitra  Wed May 14, 2008 7:14 am  
how to usse multiple database... any idea in it?
 Kat  Sun Jul 20, 2008 9:41 am  

Would someone explain to me the point of using a database table for tags, since tags, by their very nature, get repeated over and over? My bf and I are in the process of developing a couple sites that will use tags fairly intensely, but we keep running into snags, as far as normalization and unique IDs are concerned.

One of my bf's possible ideas was to create the code so each new tag would create its own table, but he scrapped the idea once he realized that creating all those tables automatically would use up a ton of space. I've got the intuition that maybe tag clouds mostly get controlled by regular site code (PHP, etc).

Another intuition I have that there might be code, either in PHP or SQL that might be used to track how many times each tag was used, and then connect that tracking code to the dynamic code on the site that makes each tag bigger according to usage/popularity, etc. If this is not how it's usually done, okay. I'm just throwing that out there.

So if anyone's got any clues as to how tags are USUALLY tracked by using a database on other well known sites like Flickr or WordPress, I'd love to know, because it might help solve some issues. Again, just tossin' ideas out there.

 Dennis  Thu Jul 24, 2008 4:10 pm  
hi Kat, the behaviour of each tag in your cloud is obviously influenced by the 'weight' of a tag. so if a particular tag is 'repeated' more often than another tag, it will be of more weight, and thus bigger.
So the behaviour of your cloud is influenced by the contents of the database, I recon that's exactly what you'd want in a tag cloud.
What kind of tags are you talking about? If it's about the number of times certain words are used in your site, maybe you could consider indexing it by sphider or something simular, and then use the contents of the sphider-tables to create your tag-cloud.
I presume you don't really mean each tag would create its own table, that would be plain silly. You could, however put each tag as a record in a table, or, if you're really really smart, use a table with a tag and a count-column. First check if a certain tag is already in your table. If so, take the value of the counter, raise it by one and replace the value in the table by the new value. No rocket-science needed here ;-)
 Dennis  Thu Jul 24, 2008 4:33 pm  
@kan: "how i can use str_replace
to print many words from full text
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 "
Why would you want to use str_replace? I think you'd better explode the text by space and put the words into an array
 Download  Tue Feb 16, 2010 3:54 pm  
wow , i have to go through a days, that is cool
 Lisa  Tue Mar 9, 2010 10:15 am  
I managed to get the tag cloud working when it was based on the array, but am having a few problems with the database based tag cloud...

Warning: ksort() expects parameter 1 to be array, null given in ... on line 29

Warning: array_values() [function.array-values]: The argument should be an array in ... on line 45

Warning: Wrong parameter count for min() in ... tagcloud.php on line 45

Warning: array_values() [function.array-values]: The argument should be an array in ...tagcloud.php on line 46

Warning: Wrong parameter count for max() in ...tagcloud.php on line 46

Warning: Invalid argument supplied for foreach() in ...tagcloud.php on line 55


Does anyone know why I'm getting these errors- and is there a source code for this so I can check I've constructed it right etc.

Thanks in advance!
 Oswaldolover  Thu Mar 11, 2010 3:39 pm  
Please could u help me to solve my problem. I cant get the tags from my database. THIS IS DE CODE:
------------------------------------------------------------
/*
CONNECT TO THE DATABSE AND UPDATE AN ENTRY IN A SPECIFIC TABLE IN THE DB
*/
function db_connect($params=array()){
$params = (!is_array($params) ? array($params) : $params);
$defaults = array(
'db' => array(
'host' => 'XXXXXXXX',
'db_name' => 'XXXXXXXXX',
'username' => 'XXXXXXXX',
'password' => 'XXXXXXXXX',
)
);

$params = array_merge($defaults,$params);
$db_connected = mysql_connect($params['db']['host'], $params['db']['username'],$params['db']['password']);
return $db_connected;
}

$dbParams = array(
'db' => array(
'host' => 'XXXXXXXXX',
'db_name' => 'XXXXXX',
'username' => 'XXXXXXXXXXX',
'password' => 'XXXXXXXXX',
)
);
$db_connected = db_connect($dbParams);
mysql_select_db($dbParams['db']['db_name']);

if($db_connected == false){
//FAILED TO CONNECT TO DB
die('COULD NOT CONNECT TO DB: ' . mysql_error());
}

$db_table ='tags';
$tagString = (isset($_GET['tag']) ? $_GET['tag'] : null);
$count=0;
$tok = strtok($tagString, " t,;.'"!&-`nr");//considering line-return,line-feed,white space,comma,ampersand,tab,etc... as word separator
if(strlen($tok)>0) $tok=strtolower($tok);
$words=array();
$words[$tok]=1;
while ($tok !== false) {
$tok = strtok(" t,;.'"!&-`nr");
if(strlen($tok)>0) {
$tok = strtolower($tok);
if($words[$tok]>=1){
$words[$tok]=$words[$tok] + 1;
} else {
$words[$tok]=1;
}
}
}


function get_tag_data() {
mysql_connect('host', 'db_name','username', 'password');
mysql_select_db('XXXXXXXXXXX');
$result = mysql_query("SELECT * FROM tags GROUP BY tag ORDER BY count DESC");
while($row = mysql_fetch_array($result)) {
$dbparams[$row['tag']] = $row['count'];
}
db($arr);
return $dbparams;
}

//print_r($words);
//return;
foreach($words as $keyword => $weight){
$query="INSERT INTO `{$dbParams['db']['db_name']}`.`{$db_table}` (keyword,weight,link) values ('".$keyword."',".$weight.",'NA')";
//ADD THE KEYWORD TO THE DB IF IT DOES NOT YET EXIST, IF IT DOES THEN UPDATE THE TABLE IN THE DB
if(!mysql_query($query)){
if(mysql_errno($connection)==1062){
update_database_entry($dbParams,$db_table,$keyword,$weight);
}
}
}


/**
* this function will update the mysql database table to reflect the new count of the keyword
* i.e. the sum of current count in the mysql database & current count in the input.
*/
function update_database_entry($dbParams=array(),$db_table,$keyword,$weight){
$db_connected = db_connect($dbParams);
$query="SELECT * FROM `{$dbParams['db']['db_name']}`.`{$db_table}` WHERE keyword LIKE '%{$keyword}%'";
$results=mysql_query($query,$db_connected);
if(!$resultset){
die('Invalid query: ' . mysql_error());
} else {
while($row=mysql_fetch_array($results)){
$query="UPDATE `{$dbParams['db']['db_name']}`.`{$db_table}` SET weight=".($row[2]+$weight)." WHERE tag_id=".$row[0].";";
mysql_query($query,$db_connected);
}
}
}




//KILL TO CONNECTION TO THE DB
mysql_close($db_connected);
?>

ps the connection details are not including for my data protection
 Avinash D  Tue Apr 6, 2010 3:05 am  
Hi,

Would this be appropriate for wordpress which already has tags?
 cihip  Wed Sep 1, 2010 11:34 pm  
Tag Cloud with Database for thanx code.
 seo-agentur  Wed Feb 9, 2011 10:02 am  
Hi, I'm using a script getting the keywords from the referrer, it looks like that:


$url = parse_url($_SERVER['HTTP_REFERER']);
$possibles_params = array('q','qt','p');//&q= &qt= &p=
parse_str($url['query'], $params);
foreach($possibles_params as $val)
{
if($params[$val]) echo "

Welcome!

We're happy you found us at
".$url['host']."
"."with the Keyword

".$params[$val]."

";
}

?>

Now it would be a very good idea to count the keywords, keyword hits, and store them at a MySQL table. At the output it was useful to resize the tags dynamicly with css proporational to the count value.
Also I'm making use of a php content spinner script, cause it's fantastic for SEO, google loves fresh content ; )

A keyword blacklisting feature oder check against preferred keywords is necessary for this solution, so you won't get unwanted search terms / keywords in the tagcloud. What do you think, or better said, how would you do it, it's just a suggestion!
Add a comment
The ability to add new comments has been disabled in this archived version.

Loading
Main | Tutorials | Articles