php - Tags cloud

Tags cloud

We retrieve search terms along with the numbers of searchs matching each term and display the results inside a tag cloud.

Copy and paste the following codes into your editor and run it (code comments are included).
If you want to see the live result, try it here (new window).

The tag cloud code <? $txt='<p>The tag cloud using a Mysql connection</p><p>'; //As tag clouds are most of the time generated from the results contained inside a database, this script is using a database: $db=mysql_connect('localhost','yourusername','yourpasswd') or die(mysql_error()); mysql_select_db('yourbase'); //First off, let's create a table with few search queries: mysql_query("CREATE TEMPORARY TABLE searchterms (term VARCHAR(150) NOT NULL PRIMARY KEY,nbsearches BIGINT UNSIGNED NOT NULL DEFAULT 1,datesearch DATE NOT NULL)",$db) or die(mysql_error()); //Let's insert some data: mysql_query("INSERT INTO searchterms SET term='1st term',nbsearches=20,datesearch='2007-11-10'",$db); mysql_query("INSERT INTO searchterms SET term='2nd term',nbsearches=40,datesearch='2007-11-11'",$db); mysql_query("INSERT INTO searchterms SET term='3rd term',nbsearches=25,datesearch='2007-12-10'",$db); mysql_query("INSERT INTO searchterms SET term='4th term',nbsearches=200,datesearch='2007-12-01'",$db); mysql_query("INSERT INTO searchterms SET term='5th term',nbsearches=2,datesearch='2007-11-02'",$db); mysql_query("INSERT INTO searchterms SET term='6th term',nbsearches=45,datesearch='2007-12-10'",$db); mysql_query("INSERT INTO searchterms SET term='7th term',nbsearches=120,datesearch='2007-12-11'",$db); mysql_query("INSERT INTO searchterms SET term='8th term',nbsearches=767,datesearch='2007-11-12'",$db); mysql_query("INSERT INTO searchterms SET term='9th term',nbsearches=220,datesearch='2007-12-12'",$db); mysql_query("INSERT INTO searchterms SET term='10th term',nbsearches=10,datesearch='2007-11-13'",$db); mysql_query("INSERT INTO searchterms SET term='11th term',nbsearches=520,datesearch='2007-12-14'",$db); //we assume we have a table containing the searched terms along with the number searches for each one: //we create a temporary table out of it; we filter the eldest ones (we take the 100 most recent): mysql_query("CREATE TEMPORARY TABLE tempsearch SELECT term,nbsearches FROM searchterms ORDER BY datesearch DESC LIMIT 100",$db) or die(mysql_error()); //Create another table from that temporary one to sort the results and extract the wanted amount (10): mysql_query("CREATE TEMPORARY TABLE tempsearchsorted SELECT term FROM tempsearch ORDER BY nbsearches DESC LIMIT 10",$db) or die(mysql_error()); //Take the maximum number of searches for a term $q=mysql_query("SELECT max(nbsearches) FROM searchterms,tempsearchsorted WHERE tempsearchsorted.term=searchterms.term",$db) or die(mysql_error()); $sum=mysql_result($q,0); //Sort by name $q=mysql_query("SELECT searchterms.term,searchterms.nbsearches FROM searchterms,tempsearchsorted WHERE tempsearchsorted.term=searchterms.term ORDER BY searchterms.term ASC",$db) or die(mysql_error()); while($l=mysql_fetch_assoc($q)) { $txt.= '<a href="/yourpage.php?item='.urlencode($l['term']).'" style="font-size:'.($l['nbsearches']/$sum + 0.9).'em">'.$l['term'].'</a> '; } $txt.='</p>'; echo $txt; mysql_close($db); ?>

Notes:
- you can easily adapt this script to your database scheme;
- you can change colors, size... using CSS

Comments

You are welcome to post comments onto this Webmaster forum - php