LMNOpc

For a bunch of years I’ve had my own database-driven links page I call LINK’d. I built it way before the terms “social bookmarking” or “tags” entered the vernacular, and while sometimes I think about exporting the database and moving the whole thing over to del.icio.us or ma.gnolia.com, I’m kind of attached to my site and it sometimes gets me thinking about things I wouldn’t normally think about.

This article is a result of one of those random thoughts.

Google Hates LINK’d

I was digging through LINK’d looking for something when my eye wandered into my browser’s Google Toolbar and I realized that LINK’d wasn’t getting much love from Google. Its page rank was a whopping zero, and the AdSense ads on the site never really tied into the content, which led me to believe something wasn’t working quite right. It got me thinking about what was screwy about the page.

I’d already given the page a CSS-makeover a couple of years ago and it makes use of semantically-correct markup using header and paragraph tags to display the links in different categories, so that probably wasn’t it; search engines should love the page.

It finally dawned on me — maybe my links stink.

Lousy Links

Back when I first created LINK’d, I thought it would be kind of neat to see what people were clicking on. I wrote some link tracking code that worked using a redirect, so the links didn’t actually go to the site being linked to. Instead of linking straight to a page, all the links looked something like this:

http://www.lmnopc.com/links/index.php?action=jump&url=http%3A%2F%2Fwww.google.com&id=1

The action=jump told my web app that the code was to fire the redirect script. url specified what site to redirect the user to, and id specified what link counter record to update in the database.

Google and other search engines aren’t going to like that very much because to a spider, all my links point right back at the page that sourced them. Not good.

Fixing My Links

The first thing I did was nuked the nutty jump URLs and replaced them in the code with links straight to the sites they really were supposed to point. Next, I went in and added some javascript to pass the link_id used to reference that link in the database to a function like this:

<a onclick="countClick(1); return true;" href="http://www.google.com/" />

Adding Ajax

The next step is pretty obvious — write a function named countClick(). countClick is where Ajax (more accurately xmlHttpRequest) comes into play. What countClick is going to do is make an asynchronous request back to my index.php file with a URL containing a query string telling the script that a link’s been clicked and the PHP will run a query that will UPDATE the counter of the specified link.

countClick is really simple

function countClick(id)
{
   loadXMLDoc('/links/index.php?click=' + id, ajaxCallback);
}

loadXMLDoc is based off some code I found over at Apple’s Developer Connection. You can find that code here: http://developer.apple.com/internet/webcontent/xmlhttpreq.html. My version is a tiny bit different. I changed it so that instead of always falling back to the same request handler, my function accepts a function as a parameter that it uses for the xmlHttpRequest callback. My changes are highlighted:

var req = false;
function loadXMLDoc(url, funcProcess) {
   req = false;
   if(window.XMLHttpRequest) {
      try {
         req = new XMLHttpRequest();
      } catch(e) {
         req = false;
      }
   } else if(window.ActiveXObject) {
      try {
         req = new ActiveXObject("Msxml2.XMLHTTP");
      } catch(e) {
         try {
            req = new ActiveXObject("Microsoft.XMLHTTP");
         } catch(e) {
            req = false;
         }
      }
   }
   if(req) {
      req.onreadystatechange = funcProcess;
      req.open("GET", url, true);
      req.send(null);
   }
}

I bolded my awesome (haha) changes.

Here’s my callback function:

function ajaxCallback()
 {
   if (req.readyState == 4)
   {
      if (req.status == 200)
      {
         if (req.responseText.length)
         {
            if (req.responseText != '1')
            {
               alert('countClick Failed');
            }
         }
      }
      else
      {
         alert("There was a problem retrieving the XML data:\n" + req.statusText);
      }
   }
}

99% of that code is standard request handling stuff. I highlighted the one line that’s not standard. All it does is show an alert to say something didn’t happen right. We’re not trying to make a web-based word processor or email application here, so I’m not going overboard here. If I wanted some kind of fault handling, I’d use one of the dozen or so Ajax frameworks out there for PHP instead of going the quick and dirty route that I’m taking here.

The Server Side

Now that we have the client side of this app done, we have to write a little PHP to handle requests made by the Javascript to update the counter.

Before talking about the code, I should probably talk a little bit about the schema behind this application. Here are the fields I’m focusing on:

link_id               Each link has a unique id stored as an integer
url                   The target site's URL
views                 The number of times the link has been clicked
last_viewer           The ip address of the last person to click on this link
last_viewer_timedate  The time/date that the counter was last incremented

I’m not going to go into a ton of detail on the PHP side of things. I think the comments do a pretty good job describing what’s going on in this snippet:

// look for querystring that tells program to increment
// the click counter on a given link_id
if (isset($_GET['click']))
{
	// retrieve the incoming link ida
	$link_id = $_GET['click'];

	// make sure id is numeric
	if (is_numeric($link_id) == false)
	{
		exit('0');
	}

	// retrieve the user's ip address
	// this is used to prevent someone from
	// clicking on a counter a zillion times
	// to artificially inflate a counter
	$ip = wrap_quotes(getenv('REMOTE_ADDR'));

	// assemble the SQL statement incrementing our counter
	$sql = "UPDATE links_links
	      SET views=views+1, last_viewer=$ip, last_viewer_timedate=NOW()
	      WHERE link_id=$link_id AND last_viewer!=$ip;";

	// execute the SQL statement
	mysql_query($sql);

	// if we get to this point, we can assume that everything
	// went as planned, so we print the '1' the Ajax script is
	// expecting to signal success
	exit("1");
}

Good luck!

I hope you find this article helpful, and if you run into any problems, feel free to write and I’ll try to answer any questions you may have.

This entry was posted on Monday, October 9th, 2006 at 11:56 pm and is filed under Feature, Projects. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.