Generating Tag cloud with PHP and MySQL

Posted: December 1, 2012 in Php
Tags: , , , , , , , , , , ,

Empire Avenue Tag Cloud

The basic idea this time is to present a way to form a tag cloud from user input text and text entered in the past. A tag cloud is a visual depiction of user-generated tags, or simply the word content of a site, used typically to describe the content of web sites.

for this we will create an HTML form that will accept user text & also allow user to see tag cloud generated from mysql database which contains the text entered in the past.

<!--?php
    echo 'php” name=”gen_tag_db”>’;
    echo 'Input your text here:
<textarea name=”tag_input” rows=”20″ cols=”80″>

';

    echo '<input type="submit" name="submit">';
    echo '</form>';
?>
<br />
<h3>OR</h3>
<br />
<p>see the current tag cloud here</p>
<!--?php
    echo 'php”>’;
    echo '<input type="submit" value="show current tag cloud" >';
    echo '</form>';
?>

The entered text will be tokenized into single words with php function strtok(), each of which will have its frequency counted and the pair will go into an array. This array will then be stored into a mysql database, we can optionally keep a coloumn in the mysql database table to store links if any for future expansion of this project.

1) tag_id —- int,primary key,auto increament

2) keyword — varchar(20),unique

3) weight — int

4) link — varchar(256).

Next make a php file and name it tag_cloud_gen.php . The php code written in following lines just make an array ‘$words’ which has keyword in lower case & its frequency association from input text. The pairs from the array are then feed into the mysql database ‘tagcloud_db’ which has a table callet ‘tags’ whose columns are listed above. On encountering error the mysql _errrno() returns error number. While feeding the word array to the tags table duplication may occour, because of the previous entries, so we check whether this is the case by comparing the number returned to ’1062′ which indicates duplicate field present in the table (the keyword coloumn of table has unique constraint). On encountering this we simply update the mysql database to include the count of this input word/tag too.

<!--?php
///////////////////////////////////////////////////////////////////////////////////////////////////////
/**
* 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 &amp;amp; current count in the input.
*/
function update_database_entry($connection,$table,$keyword,$weight){
    
    $string=$_POST['tag_input'];
    $connection = mysql_connect("localhost", "root", "");
    /**
    * now comes the main part of generating the tag cloud
    * we would use a css styling for deciding the size of the tag according to its weight,
    * both of which would be fetched from mysql database.
    */
    $query="select * from `tagcloud_db`.`tags` where keyword like '%$keyword%'";
    $resultset=mysql_query($query,$connection);
    if(!$resultset){
        die('Invalid query: ' . mysql_error());
    } else {
        while($row=mysql_fetch_array($resultset)){
        $query="UPDATE `tagcloud_db`.`tags` SET weight=".($row[2]+$weight)." where tag_id=".$row[0].";";
        mysql_query($query,$connection);
    }
}
}
?>
<?php
/*
* get the input string from the post and then tokenize it to get each word, save the words in an array
* in case the word is repeated add '1' to the existing words counter
*/
    $count=0;
    $tok = strtok($string, " \t,;.\'\"!&-`\n\r");//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) {
        echo "Word=$tok<br />";
        $tok = strtok(" \t,;.\'\"!&-`\n\r");
        if(strlen($tok)>0) {
        $tok=strtolower($tok);
        if($words[$tok]>=1){
            $words[$tok]=$words[$tok] + 1;
        } else {
            $words[$tok]=1;
        }
    }
}
print_r($words);
echo '<br /><br />';
/**
* now enter the above array of word and corresponding count values into the database table
* in case the keyword already exist in the table then update the database table using the function 'update_database_entry(...)'
*/
$table="tagcloud_db";
mysql_select_db($table,$connection);
foreach($words as $keyword=>$weight){
    $query="INSERT INTO `tagcloud_db`.`tags` (keyword,weight,link) values ('".$keyword."',".$weight.",'NA')";
    if(!mysql_query($query,$connection)){
        if(mysql_errno($connection)==1062){
            update_database_entry($connection,$table,$keyword,$weight);
        }
    }
}
mysql_close($connection);
?>

Make another file and name it style.css . Put the following code in it.

HTML, BODY
{
padding: 0;
border: 0px none;
font-family: Verdana;
font-weight: none;
}
.tags_div
{
padding: 3px;
border: 1px solid #A8A8C3;
background-color: white;
width: 500px;
-moz-border-radius: 5px;
}
H1
{
font-size: 16px;
font-weight: none;
}
A:link
{
color: #676F9D;
text-decoration: none;
}
A:hover
{
text-decoration: none;
background-color: #4F5AA1;
color: white;
}

This will make our tag cloud look pretty, save it as style.css .
again make a new php file and name it show_tag_cloud.php .
In the php code that follows we connect to mysql database, fetch back all the tags, its weight and link.

Then it calculates the size for each tag using its weight & minimum assumed size for tags, it also associates each tag the link retrieved from the database or with a google link if no link was there i.e. ‘NA’

<?php
    $connection = mysql_connect("localhost", "root", "");
    $table="tagcloud_db";
    $words=array();
    $words_link=array();
    mysql_select_db($table,$connection);
    $query="SELECT keyword,weight,link FROM `tagcloud_db`.`tags`;";
    
    if($resultset=mysql_query($query,$connection)){
        while($row=mysql_fetch_row($resultset)){
            $words[$row[0]]=$row[1];
            $words_link[$row[0]]=$row[2];
        }
    }
// Incresing this number will make the words bigger; Decreasing will do reverse
$factor = 0.5;
// Smallest font size possible
$starting_font_size = 12;
// Tag Separator
$tag_separator = '&nbsp;    ';
$max_count = array_sum($words);
?>
<!--DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
    <HEAD>
        <TITLE> Tag Cloud Generator </TITLE>
        <META NAME="Keywords" CONTENT="tag, cloud, php, mysql">
        <META NAME="Description" CONTENT="A Tag Cloud using php and mysql">
        <LINK REL="stylesheet" HREF="style.css" TYPE="text/css">
    </HEAD>
<BODY>

Tag Cloud using php

and mysql </h1><div align='center' class='tags_div'>

<?php
foreach($words as $tag => $weight )
{
    $x = round(($weight * 100) / $max_count) * $factor;
    $font_size = $starting_font_size + $x.'px';
    if($words_link[$tag]=='NA') echo "<span style='font-size: ".$font_size."; color: #676F9D;'>.$tag."&meta='>".$tag."</a></span>".$tag_separator;
    else echo "<span style='font-size: ".$font_size."; color: #676F9D;'>.$words_link[$tag]."/'>".$tag."</a></span>".$tag_separator;
}
?>
</div></center>
</BODY>
</HTML>

now put them all in your webserver’s root directory and watch the results. Each query will give you new results over time as the database grows.

a sample output from my tag cloug looks like this:

a sample tag cloud on my computer

This tag cloud was generated using the following data:

tag_id;keyword;weight;link
“1″;”vimal”;”7″;”www.zeeshanakhter.com”
“2″;”scet”;”5″;”NA”
“3″;”engg”;”2″;”NA”
“4″;”0″;”1″;”NA”
“7″;”google”;”5″;”NA”
“8″;”cool”;”2″;”NA”
“9″;”orkut”;”3″;”NA”

Advertisements
Comments
  1. christi parks says:

    Hello, sir i would like to ask that what is the scope of Php mysql training, what all topics should be covered and it is kinda bothering me … and has anyone studies from this course http://www.wiziq.com/course/5871-php-mysql-with-basic-javascript-integrated-course of core and advance java online ?? or tell me any other guidance…
    would really appreciate help… and Also i would like to thank for all the information you are providing on java concepts.

    • hi dear that’s Course outline is great if you completed that then you can easily start working as a professional in Php ….. and remember all the languages have same background if you work hard and get full command on one language then its not a big deal to shift to other language or technology ….

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s