How to make a tag cloud in PHP, MySQL and CSS
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!
For my example, first you'll need a database that stores the keywords / search terms.
The way mine works is every time a search is run, I check to see if the term has been searched before. If it has, I update the counter field + 1. If the term has not been searched for before, I insert it with a counter of 1.
Alternatively you may store every search individually and use the GROUP BY clause to determine how many times the search has been run.
My query looks like this:
-
// don't forget to connect to DB first!
-
-
$maximum = 0; // $maximum is the highest counter for a search term
-
-
-
{
-
$term = $row['term'];
-
$counter = $row['counter'];
-
-
// update $maximum if this term is more popular than the previous terms
-
if ($counter> $maximum) $maximum = $counter;
-
-
-
}
-
-
// shuffle terms unless you want to retain the order of highest to lowest
Next we'll setup a bit of css. Feel free to adjust these as you see fit.
-
#tagcloud {
-
width: 300px;
-
background:#FFFFCC;
-
color:#0066FF;
-
padding: 10px;
-
border: 1px solid #FFE7B6;
-
text-align:center;
-
}
-
-
#tagcloud a:link, #tagcloud a:visited {
-
text-decoration:none;
-
}
-
-
#tagcloud a:hover, #tagcloud a:active {
-
text-decoration: underline;
-
color: #000;
-
}
-
-
#tagcloud span {
-
padding: 4px;
-
}
-
-
.smallest {
-
font-size: x-small;
-
}
-
-
.small {
-
font-size: small;
-
}
-
-
.medium {
-
font-size:medium;
-
}
-
-
.large {
-
font-size:large;
-
}
-
-
.largest {
-
font-size:larger;
-
}
Finally we just want to loop through the array and display it with the appropriate css class.
-
// start the output to the page
-
echo "<h3>Popular Searches</h3>
-
<div id=\"tagcloud\">
-
<div>\n";
-
-
foreach ($terms as $k) // start looping through the tags
-
{
-
// determine the popularity of this term as a percentage
-
-
// 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';
-
}
-
-
// output this term
-
}
-
-
// close the output
-
echo "</div>
-
</div>\n";
And thats it! Hope this saves someone some time. Add your comments below!
gave very good starting concept
Awesome, thank you very much, the tag cloud script is really cool.
Great job! This was really helpful! Keep on!
Great script - a couple of suggestions though:
* Most tag clouds are sorted alphabetically, you can do this by changing the shuffle() to a sort().
* This should probably be outputted as an ordered list ( with 's for the terms), then CSS used to make it look like a tagcloud.
Jason, I agree with your suggestions, however I wonder, how could you make an ordered list replicate a tag cloud? I'm sure its possible however i'm no wizz when it comes to css.
Gr8 work, keep it up
Dear
I m just jusing the code on my site and it works very fine, thx for it!
Got a problem, i want to put a 3th option in my arry, for example category. How can i put a third option in the array and how do i have to take out of the array?
So here i have to put the third option :
while ($row = mysql_fetch_array($query))
{
$term = $row['term'];
$counter = $row['counter'];
$terms[] = array($term => $counter);
}
And hiere i have to take out :
foreach ($terms as $k => $v)
{
foreach($v as $k2 => $v2)
{
$percent = floor(($v2 / $maximum) * 100);
if ($percent = 20 and $percent = 40 and $percent = 60 and $percent $k2 ";
}
}
So i can put him into the url (a href as a parameter)
Great thanks for the help / feedback.
Pieter
PS : sorry for my english but it s a little be difficult
Good thought, had the exact same thought myself
Hey thanks for the code. I used this idea to make something similar in Java.
Very nice tutorial. thanks
Hye men !
Thx very much. It id very useful. I've just changed it with a link in the database in order to keep traffic on my site.
See you soon
Jul'
[...] Tag cloud resources: How to make Tag clouds using PHP, MySQL, CSS Tag clouds using [...]
this tag cloud seems to simple and is constrained by specific sizes that are defined in your css. why not develop a better algorithm that calculates a font percentage between ranges of 75%-250%.
chris thats a fair point i'm sure it would look better with more font size variation. Any suggestions for such an algorithm? Obviously it would revolve around refactoring the current percentage that is calculated in the loop. and embedding the style in the tag
Nice tut, I always wandered how this stuff worked. Not as hard as I thought it would be.
Was just researching tagging to incorporate in a new web site I am building. I never thought of utilising search statistics to affect the ranking of tags. Very nice, I will use it to supplement how I intend to do this myself.
@ Steve [re: css formatting ordered list]
just set the display type of the li's to 'inline' and they'll act like span's
@ Chirs [re: font percentage calculation]
its quite simple - you use the percentage to calculate where it should be placed within your font percentage boundaries then add the lower bound:
// tag font size ranges (in percent)
$minSize = 75;
$maxSize = 250;
$tagSize = (floor($v2 / $maximum) * ($maxSize - $minSize)) + $minSize
[...] The Cloud of Tags in pages Jump to Comments http://stevethomas.com.au/php/how-to-make-a-tag-cloud-in-php-mysql-and-css.html [...]
good script, i have try
Thanks a lot!
Thank you for this very helpful tutorial.
Thanks a lot Steve,
this is what I call the nice looking functional code,
I'll use it in my upcoming free cms (Aikicms),
if you allow me
[...] How to make a tag cloud in PHP, MySQL and CSS - A great tutorial that shows you how to create a tag cloud using PHP, MySQL and CSS. [...]
No problems Bassel.
thank you, this has been very useful
Great post, nice concise outline, well formatted code - good work.
Had just been asked by a client to include a cloud in their site, came across this method and had one up in less than an hour, including css formatting and rewriting some of the code for percentage based resizes and to pass along a third parameter (url).
There was a comment before asking how to add the url in, so i'll just paste the change I made:
while($row = mysql_fetch_array($articleRow))
{
$title = $articleRow["Title"];
$score = $articleRow["Score"];
$id = $articleRow["ArticleID"];
$titles[] = array( Title => $title, Score => $score, Id => $id );
}
This way you can remove the second foreach loop, and can call the desired attribute through $v["Title"], or however you name your variables.
Thanks for your feedback Alex, I have used it to revise and improve my code.
That was a great post. Thank you, Steve.
I am a newbie to web development. I have a small doubt.
Can we somehow control the tag cloud. I mean, if I have a university search page, and someone wants to deface it, he/she could enter an obscene search term a 1000 times and it will always remain the largest tag in the cloud.
Hi Deepak,
Yes you definitely can filter out "bad words". An implementation i've used before is to just create an array of distasteful words, then as you are processing the search, check if (in_array($search_term, $bad_words)) show an error to the user and do not add the term to the search table.
great article. an improvement I made was to use a database call to set the maximum field rather than hard coding it
$query = "SELECT max(counter) as maxcounter from searches order by date desc, counter desc;";
$result = $db1->Query($query);
$arr_result = $db1->FetchArray($result);
return $arr_result[0]['maxcounter'];
Thanks
Great tutorial for a beginner. Thank you very much Mr. Steve
Nice stuff you put together. However, I can't stop wondering if the term "tag" is appropriate, since none of your elements in the cloud are tags. They are search words, and thus the term "search cloud" is more appropriate. Anyways, I'm only being pedantic because I would have loved to had seen an implementation of actual tags.
[...] Cómo hacer una nube de tags en PHP, MySQL y CSS >> Tres tutoriales de la página de Steve Thomas. [...]
If you execute the query:
SELECT term, counter FROM search ORDER BY counter DESC LIMIT 30
you can be sure that maximum counter is in the first row of your result, so just read it
Inspite of shuffle, I use this SQL
select * from
(select term, count(term) as cnt
from tbl_search_terms
group by term
order by cnt desc
limit 25
) subqTerms
order by term asc
Nice Post... I'm thinking of implementing such a tag cloud for various artists on my lyrics page. It would be interesting, if someone knew how to tell google or some other search engine bot the relevance of some certain tag. I know it's possible using sitemaps and the priority tag... does google parse the css?
regards
dominik
nah I don't think they parse css, only interest is in HTML like h1, h2 etc.
I have to add a tags searcher on one of my sites. I do not know ( now) how to make the sql. It is a nice article about usual cloud tags.
Awesome,the tag cloud script is really cool.
I will definitely use this tag cloud in the redesign of my site. Great Tutorial. Simple and easy to follow.
Interesting. I will try this one.
Great thank,
very nice piece of code for tag cloud.
Hi Steve, this script looks like it fulfils my needs, but I am fairly new to all this and am failing at the first hurdle, ie the database! I want to create a dynamic cloud that is based on how many times users click certain pages on my website. I have created a table inside a database called clickcloud. Within that I have created a field for each page that can be clicked, ie Page A and Page B. Then if someone clicks Page A I increment the value of Page A by one in the database. So then I may have Page A with 4 clicks and Page B with 2. Where I am getting confused is with your script you seem to have a term and a counter, so would you create a field called Page A a field called Page A counter, Page B and Page B counter? Could you share the code that creates and increments the fields as that may help me!!
Hi Greg,
I think you have your table structure wrong. You should have a row for each page rather than a column.
Here you could replace "term" with "page". Another advantage is that you won't have to change the table structure every time you add a new page, you just add a new row.
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`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Man, this is great!!!
thank´s
Hi Steve,
Thanks for the reply, I'm now up and running
One further question, I seem to have a huge range of hits since I went live, ie the most popular link has many hundreds of links, whereas the least popular has 50-70. This means 90% of my links end up as the smallest category. Is there a way to account for such a large spread of clicks at all?
Hi Steve,
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`title` VARCHAR(255) DEFAULT NULL,
`blogdate` DATE NOT NULL DEFAULT '0000-00-00',
`keywords` VARCHAR(255) DEFAULT NULL,
`entry` TEXT,
This is my table structure. the field "keyword" contain keywords for a blog entry space sepated for example
"xp microsoft operating system"
"google books"
"google browser"
I want to seperate keywords for each row and then want to make the tag cloud, any idea ?
Hi Steve, can you tell me how you add 1 in the counter field for each searched term? i have similar script in my site but it adds the term in a new row for each search. i know it's stupid ... but i am too
Thank you