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.
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:
- Connect to the database
- Handle new searches so that the counter can be updated
- 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.
Just try placing tags around the css part, if that is where you are having your difficulties, then start the new blocks with the php open and close tags.
Hey Steve,
Thanks for your script. I am a newbie at php and I’m currently learning it so bare with me for the simple ‘stupid’ question yet important to me. I am trying to implement your script and I get this error: Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING, could you direct me to what is wrong here and possibly a solution out of it?
Much thanks!
Hey Steve,
Thanks for the script! Implementation is really nice. Took me about 4 mins to get this up and running.
-Brandon
Hi Steve,
Been looking for something exactly like this! Seen a few examples online that over-complicated things, this one does what it says on the tin. Nice job.
Thanks alot for the script! Works flawlessly! I could easy combine it with an other script of mine.
Gr,
Anthony
Great job, it took me some time to understand but it worked like a charm.
Thanks
Thank you, Excellent piece of code..it took just few minutes to tag cloud up.
Nice one Steve.
I was having a difficult time looking for a straight forward script that could be customized for my website and this script does the job just right!
Cheers mate.
Nice example here Steve.
I’ve made a few modifications to my implementation of this, and have wrapped it all into an easy to use function.
Would it be OK if I share that function on my site? I would obviously make references to this post of yours.
no worries Tyler, you can reprint some/all of this code with acknowledgment.
Cool Steve. I’ll let you know once it’s published.
Steve, post is up:
http://www.longren.org/2010/02/24/how-to-build-a-tag-cloud-with-php-mysql-and-css/
You can delete this comment if you want, just wanted to let you know it was up and I don’t have your email or any other way to contact you.
This is super useful, exactly what I was looking for, and I had it up and running in minutes! I changed it slightly so I could show the number of items in the categories in my database by changing the sql to “SELECT category, COUNT(category) as counter FROM table GROUP BY category”, so the categories with the most items are the largest.
Thanks!
Thanks a lot for this, it works great
very good.
Thanks bro!!
Managed to use some of this code on my project and it works great!
Thanks for the script! Its working fine. Exactly what I was looking for, and I had it up and running in minutes.
Thank you! Took 5 minutes to implement in my project. Great tutorial!
Thank you for sharing such invaluable information on Web Programming.
Thank you! It’s very great tutorial!
Thanks for the great script. It just took me 5 Minutes to implement this into my apnaudaipur website
Why with this code .??
Warning: Division by zero in C:\xampp\htdocs\xampp\2010\catalog\clickbank\tag_clouds.php on line 159
arisandria beta
Warning: Division by zero in C:\xampp\htdocs\xampp\2010\catalog\clickbank\tag_clouds.php on line 159
best
this the code on line 159: $percent = floor(($k['count'] / $maximum) * 100);
Help Me please..
@aries
This indicates that you are trying to divide by zero, perhaps your query is broken or you didn’t set the maximum? Try outputting the values of $k['count'] and $maximum to find which one is broken.
i need sql database and tables! !
Pingback: How To: Build a Tag Cloud with PHP, MySQL, and CSS « T. Longren
hi ,
i also need sql database and tables of this tag scripts.
thanks,
Hi there, thx so much for this script. It really helps me.
Hey thanks mate u made it so easy
Nice script, now with a slight change of colors in the css will be perfect.
Thanks.
Good script, is just what I was looking for. Thank you very much.
in the name of god
hi
thank you.
A very good script and helped lot to solve a problem
Hi! I was taking a look and I think is awesome… I just wonder in which part the $count variable increases +1… Thanks!
Thank u
Awesome work
Thanks for the script! Its working fine. Exactly what I was looking for, and I had it up and running in minutes.
Hello
I am a phpMotion user and I am trying to adapt the default use of the tags to my needs. By default the tags are created with one space… so every keyword in transformed into a tag… So in the tag cloud everything is being created like this: “keyword space keyword space keyword etc”
Now I would like to make tag groups so that a group of 2 or 3 keywords together would become a single link that would be pointing to a content which contains identical tag group….
I have been thinking that if I use hyphen (-) to separate the tags groups, everytime the keywords are found within the hyphen they would obey the tag group command….
So let’s say I have this pair:
red car
If I leave like that, it would be 2 tags pointing at contents with the keyword red on the one hand, and car on the other… But if we write it like this:
red car – green tree – etc
The red car tag group and the green tag group would be pointing to contents containing the 2 keyword… And would never find contents that would contain only one of the words… (red for instance)…
If anyone could point me into the right direction about how to do this… It’d be great…
Thx very much
Alberto
regidordigital.com
Pingback: Using Tag Clouds effectively
Hi–
This seems like a great script.
My skills in PHP (and virtually everything else except Flash) are less than minimal. I was wondering, could this script be adapted so that the tag cloud remains on my site and then can by changed/evolved by text inputs from visitors to my site?
What I am really looking for is a kind of word cloud that each visitor to my site can contribute to so that it constantly evolves…
I have a problem with the width. The with is set to 200 px , but since i get more words, it just overrides that width, any way to solve it?
Hey Steve !
everything is perfect, but my counter is not increasing in table. Do i need ti change few things there .
Please suggest .
thank in advance.
Thank you for the code i get
Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in C:\wamp\www\social\search.php on line 46
I am new to php please help
Doesnt work. The queries retrieving from the db work, but the INSERT and UPDATE functions do not. There are no error messages. All I know is that the db is not storing and updating search terms. Can anyone help?
First of all, thank u Steve….. awesome script.
Flying-
You can try this, it worked for me.
Change form method to post
$term to post instead of get
$term = mysql_real_escape_string(strip_tags(trim($_POST['term'])));
INSERT query has a small syntax error. Change to following:
mysql_query(“INSERT INTO search (term, last_search) VALUES (‘$term’, ‘$now’)”);
@viperphp
Thanks for pointing that out, yes there was in fact a missing closing bracket in the insert sql. I have updated the tutorial and zip file accordingly.
hey Steve, Thanks a lot man.. this is exactly what i wanted..
There is a mistake in the CSS:
#tagcloud .largest {
font-size:larger;
}
SHOULD SPECIFY:
font-size:x-large;
see http://www.w3schools.com/cssref/pr_font_font-size.asp
thanks