Brain Handles

You've seen them as a feature of Web 2.0 web sites, big bunches of keywords called tags or a tag cloud. You've got a large database of items and you'd like to add this feature to your site. It's easier than you might think.

Step 1: Getting The Tags From Your Users

The first part of this is getting the tags from your users. The standard method seems to be to give the users a text input field and letting them type in any tags they want, splitting up multiple tags with commas or carriage returns. We'll look at doing it with commas. First, the form field:

<b>Please enter your tags, separating each one with a comma.<b><br>
<input type="text" name="tags" size=50>

On the back-end form processing script, to convert that into a list of tags, you do this:

$taginput = $_POST["tags"];
$tagarray = explode(",",$taginput);

The explode command chops up that long string of tags into an array of tags, splitting at the commas. Now you've got your list of tags. You'll still need to do some minor clean-up on them before putting them into the database, but we'll get into that when we put them into it.

Step 2: Getting The Tags Into Your Database

There are multiple organizational methods that will work for this, but we're going to go for the simplest. It requires two database tables and assumes that the first table is one you already have, listing all your items you want to tag. That's Table 1 and we'll call it "items", calling your primary unique ID key "uid". These aren't names you have to use. But I have to use something for example purposes. For example purposes, we're also going to assume that the "uid" field in Table 1 is a number (probably an autoincrementing integer).

The second table looks something like this...

CREATE TABLE `tags` (
`uid` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`item_id` INT( 11 ) NOT NULL,
`tag` VARCHAR( 50 ) NOT NULL
) ENGINE = MYISAM ;

What we're basically doing is creating a simple table that will store the ID of an item from Table 1 and an associated tag. We've set the maximum length of the tag to 50 characters. You can go up a bit or down a bit, but unless you're planning on letting people tag stuff with complete sentences, put a limit on length.

Now we go back to $tagarray from Part 1. I'm going to assume you've already put the code into your back-end script that connects it to your database and we'll go from there. I'm also going to assume that your form or some other method has fed an item ID to your script so we know which item will own this batch of tags and that you've checked it to make sure it's a number. We'll call that $itemid.

To put the tags into the database, we do the following:

for($i=0;$i<count($tagarray);$i++){
$usetag = mysql_real_escape_string(stripslashes(ltrim(rtrim($tagarray[$i]))));
if($usetag == "") continue;
$query = "INSERT INTO tags (item_id,tag) VALUES ($itemid,'$usetag')";
mysql_query($query);
}

Now what the heck is $usetag = mysql_real_escape_string(ltrim(rtrim(stripslashes($tagarray[$i])))) doing? It's doing clean-up. First rtrim and ltrim are cleaning off any white space on the ends of the tag. Then stripslashes is removing any slashes your web server might have added to sanitize the input. Last mysql_real_escape_string both sanitizes the input to try to prevent SQL injection attacks and puts in slashes where needed.

If you didn't do the stripslashes first, then mysql_real_escape_string would slash the slashes, causing problems in the future, like couldn't displaying as couldn\'t. If you've ever seen a site with backslashes showing up like that, it means they're not doing good slash management.

You'll also notice that right after that, we state that if $usetag == "" the script should "continue". In this instance, that means "quit executing the tag-handling code for this tag and start over with the next tag". That's to keep you from getting a bunch of entries with no tag value at all.

Now your tags are in the database. How do we get them out?

Step 3: Using The Tags

There are going to be three ways you'll want to use the tags:

• Showing all the tags in your tag cloud

• Showing all the tags for a specific item
• Showing all the items for a specific tag

Showing all the tags in your tag cloud:

Here's your database query:

SELECT count(*) as num, tag FROM tags GROUP BY tag ORDER BY tag ASC

This will return a result set with all the different tags, counted, and sorted in alphabetical order. Here's how you'd turn them into a cloud. We're going to assume that the display page for any tagset is tagitems.php?tagvalue=[url encoded tag].

$query = "SELECT count(*) as num, tag FROM tags GROUP BY tag ORDER BY num DESC";
$result = mysql_query($query);
while($taginfo = mysql_fetch_row($result)){
     $numtags = $taginfo[0];
     $tagname = $taginfo[1];
     $tagurl = urlencode($tagname);
     $tagsize = 11 + intval($numtags/20);
     if ($tagsize > 24) $tagsize = 24;
     echo "<a href=\"tagitems.php?tagvalue=$tagurl\"><span style=\"font-size:" . $tagsize . "pt;\">$tagname</span> ($numtags)</a> ";
}

Now there are a couple of things we do here:

Because a tag might have spaces or other characters that could mess up a URL, we're creating the $tagurl version of the tag to use in the link to the tagitems.php display page. That converts the characters into code that won't mess up the URL. In the tagitems.php script, you'd just use urldecode to convert it back before using it for database queries.

Also, one thing tag clouds tend to do is make the text size correspond to the number of items to which the tag has been assigned. So with $tagsize = 11 + intval($numtags/20) we're setting a base text size of 11 point text and adding one point to it for every 20 items that use the tag. Then we use if ($tagsize > 24) $tagsize = 24 to set a maximum text size of 24 point text. If you want to change the numbers around, that's fine.

Showing all the tags for a specific item:

Now, let's assume that you've fed the script your item ID, made sure it was valid, and named it $itemid, we'd do this.

SELECT tag FROM tags WHERE item_id = $itemid GROUP BY tag

This is the simplest of all the queries. You actually don't need the GROUP BY in there, but I put it in just in case a duplicate tag slipped through. This will condense out any accidental duplicates.

Showing all items for a specific tag:

This is the hardest one because it requires a very simple join to get the item name from your items table. First we need to make sure you've fed your script the tag by some method and sanitized it with mysql_real_escape_string (and possibly stripslashes first). We'll assume that you've put the tag into $tagvalue, and in your items database, you've named the item's name field with item_name.

Again, this isn't how you must do it. But I have to establish some names for them for the sake of example and it's better than calling them John and Mary Smith.

Here's your database query:

SELECT items.item_name,tags.item_id FROM items,tags WHERE tags.tag = '$tagvalue' AND tags.item_id = items.uid GROUP BY tags.item_id

Since I was using fields from two different tables, you'll see I had to preface the field names with the table name. I also did the grouping thing again to warrant against any accidental duplication.

And There You Go

As with any project, you can make this more complex and more complicated, adding more rules and validation. To some extent, you probably should. There are bad people out there who may try to mess with your tag cloud for no other reason than they can.

But, as you can see, creating a tag cloud for your database of items is a lot simpler than it might seem on first blush. So if you're interested in doing that for your site, hopefully it's not so intimidating now.

Tags: mysql, PHP