SiteBuilderStudio Diary of some Web Dev

8Jun/100

php tag cloud for a mysql table of links

So I went looking for a tagcloud class that I could plug in.  Tried a few and none of them made me at all happy.  So I built what I needed from Scratch.

Tagcloud code creation..  Let me know if you have any questions.

Requirements: each url in the 'Sites' table or the 'Index' table has a row for tags which are associated with it.  These tags are displayed in a cloud format, with the font sizes varying according to how many urls are tagged with that keyword.

Should this be done with a 'tags' table, constructed each time a new site is submitted, adding weight to each tag that already exists and inserting a new tag if it's not already entered?  This will work.

When a new site is submitted, the form input for 'tags' will contain multiple words that are separated by spaces.  For the purpose of entering these tags into the 'tags' table, we can break that single form input string into an array of separate tags, by using the php function explode();

This array of tags will then run through a loop, checking to see if it's already in the 'tags' table and inserting it if it is or adding 'weight' to it if it's already in there.

Then when we create the array for the tag cloud page, we will have all of our tags and weights coming from the 'tags' table.

Here is the sql to create the Tags table:

CREATE TABLE IF NOT EXISTS `Tags` (
`id` int(9) NOT NULL auto_increment,
`tag` varchar(30) NOT NULL,
`rate` int(9) NOT NULL,
PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

And here is the script for taking the tags variable and 'exploding' it into separate tags, then either inserting or updating the Tags table and tag 'rate':

$tagsArray = explode(" ",$tags);
foreach($tagsArray as $key  => $value) {
//select all from 'tags' where tag = value limit 1
$sql = "SELECT * FROM `Tags` WHERE `data` = '$value' LIMIT 1";
$q1 = mysql_query($sql,$dbi);
$sq1 = sql_fetch_array($q1);
$rate = $sq1['rate'];
$rows = mysql_num_rows($q1);

if($rows > '0'){
$rate = $rate + 1;
$update = sql_query("UPDATE `Tags` SET `rate` = '$rate' WHERE `data` = '$value' LIMIT 1",$dbi) or die("failed to update tags");
}else{
$insert = sql_query("INSERT INTO `Tags` (`data`,`rate`) VALUES ('$value','1')",$dbi) or die("failed to insert tags");
}
}

Here we have the code that displays the tag cloud.  Notice how the font size is adjusted with a percent added to font-size, according to a base ten number 10-100 which is created according to the ration of how many sites have that tag, compared to the total number of tags..

$query = mysql_query("SELECT * FROM Tags",$dbi) or die("failed to get tags");

//get all the 'rate' and add them together to make $total_tags
$sql3 = "SELECT * FROM Tags";
$query3 = sql_query($sql3,$dbi);
$total_tags = 0;
while($row = mysql_fetch_array($query3)){
$ratez = $row['rate'];
$total_tags = $total_tags + $ratez;
}

while($tags = mysql_fetch_array($query)){

$id = $tags['id'];
$name = $tags['tag'];
$rate = $tags['rate'];

//get the number-of-tag-occurances as a percentage of the overall number
$ratio = (100 / $total_tags) * $rate;

//round the number to the nearest 10
$ratio =  round($ratio,-1);

$word = $tag['tag'];
echo " <a href=\"/sites.php?x=$name\" style=\"font-size:1$ratio%\">$name</a> ";

}

And build the search page for displaying each link that has been tagged with the tag included in the get var of the url that the user clicks on in the tag cloud.

if(isset($_GET['x'])){
$tag = $_GET['x'];
}

$query = "SELECT * FROM Sites WHERE tags LIKE '%".$tag."%' AND linked2us = 1";

//$sql = "SELECT COUNT(*) AS totalnum FROM tutorials WHERE tags LIKE '%".$id."%' AND published = 1";

?>
<div class="row">
<div class="cell">Username</div>
<div class="cell" style="width:220px;">Site Name</div>
<div class="cell">Content</div>
<div class="cell">Date Added</div>
</div>

<?php
$items_q = sql_query($query,$dbi) or die("failed to get sites");
while ($items_r = sql_fetch_array($items_q)) {

//show the sites
$id = $items_r['id'];
$buid = $items_r['uid'];
$siteName = $items_r['siteName'];
$url = $items_r['url'];
$dateAdded = $items_r['dateAdded'];

echo "<div class=\"row\">";
echo "<div class=\"cell\">";
//convert to username
$sql3 = "SELECT * FROM Users WHERE uid = $buid LIMIT 1";
$query3 = sql_query($sql3,$dbi);
$array = sql_fetch_array($query3);
$username = $array['username'];
echo "<a href=\"$baseDir/profileView.php?uid=$buid\">$username</a>";
echo "</div>";

echo "<div class=\"cell\" style=\"width:220px;\">";
echo "<a href=\"$url\">$siteName</a>";
echo "</div>";

echo "<div class=\"cell\">";
//link to search page show results from this rssFeed
echo "<a href=\"$baseDir/search.php?site=$id\">View</a>";
echo "</div>";

$dateAdded = date("m.d.y",$dateAdded);
echo "<div class=\"cell\">";
echo $dateAdded;
echo "</div>";
echo "</div>";
}

That's it!  Have Fun!

or die("failed to insert tags");
Share and Enjoy:
  • Print
  • Digg
  • Sphinn
  • del.icio.us
  • Facebook
  • Mixx
  • Google Bookmarks
  • Blogplay
  • Add to favorites
  • blogmarks
  • HackerNews
  • Live
  • StumbleUpon
  • Suggest to Techmeme via Twitter
  • Technorati
  • Twitter
Comments (0) Trackbacks (0)

No comments yet.


Leave a comment

You must be logged in to post a comment.

No trackbacks yet.