<?php

/**
 * This script receives one GET argument - 'url'. This URL is logged into a links
 * table called ct_Links if it does not already exist and an ID is assigned to it.
 * A 'hit' is then logged against the provided link and the IP and user agent of
 * the user is logged. This script is called from an image tag, so we return
 * a 1x1 transparent GIF at the end.
 *
 * Author: James Caws
 * Website: http://www.jamescaws.co.uk
 * Original post:
 * http://www.jamescaws.co.uk/2008/07/dynamically-count-exit-link-clicks-throughs-using-javascript-php/
 */

/**
 * MySQL table structures:

CREATE TABLE `ct_Log` (
  `logID` smallint(8) unsigned NOT NULL auto_increment,
  `linkID` smallint(6) unsigned NOT NULL default '0',
  `logTime` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `ip` int(10) unsigned NOT NULL default '0',
  `useragent` varchar(128) default NULL,
  PRIMARY KEY  (`logID`),
  KEY `linkID` (`linkID`)
);

CREATE TABLE `ct_Links` (
  `linkID` smallint(4) unsigned NOT NULL auto_increment,
  `hash` varchar(32) NOT NULL default '',
  `url` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`linkID`),
  UNIQUE KEY `hash` (`hash`)
);

 */

/**
 * Use the PHP PEAR DB abstration layer to make things easier.
 * More info: http://pear.php.net/package/DB
 *
 * If you don't want to use this, then simply insert your own DB connection
 * code here and adjust queries below
 */
require_once('DB.php');

/**
 * Create DB connection
 */

$db =& DB::connect('mysql://db_username:db_password@db_hostname/db_name', array('debug' => 2, 'portability' => DB_PORTABILITY_ALL ^ DB_PORTABILITY_LOWERCASE));
$db->setFetchMode(DB_FETCHMODE_ASSOC);

/**
 * Import 'url' param that we want to log a click through against
 */

$sURL = isset($_GET['url']) ? $_GET['url'] : false;

/**
 * Very basic URL validation. This could be improved by only allowing
 * specific scheme types (http, ftp...), verifying host names etc
 */
$aURL = parse_url($sURL);

if (!isset($aURL['scheme']) || !isset($aURL['host'])) {
	$sURL = false;
}

/**
 * If we have a URL, do something with it
 */
if ($sURL) {
	/**
	 * We'll use a hash of the URL for some queries as it means we can set a DB table
	 * with a fixed 32 char column, indexed.
	 */
	$sHash = md5($sURL);
	$sIP = $_SERVER['REMOTE_ADDR'];
	$sUserAgent = $_SERVER['HTTP_USER_AGENT'];

	$iLinkID = local_getLinkID($db, $debugObj, $sHash);

	if ($iLinkID === false) {
		$iLinkID = local_logNewLink($db, $debugObj, $sHash, $sURL);
	}

	local_logLinkClick($db, $debugObj, $iLinkID, $sIP, $sUserAgent);
}

/**
 * Output transparent 1x1 gif, even though the image will be hidden
 */
header('Content-type: image/gif');
$sTransGIF = 'R0lGODlhAQABAIAAAAAAAAAAACH5BAEAAAAALAAAAAABAAEAAAICRAEAOw==';
echo base64_decode($sTransGIF);

/**
 * Here endeth the script
 */
exit;

/**
 * Functions
 */

/**
 * Based on a hash of our loggable URL, get its linkID from our links table, return
 * false if it doesn't exist
 */
function local_getLinkID($db, $debugObj, $sHash) {
	$sql = sprintf('SELECT linkID FROM ct_Links WHERE hash = %s', $db->quoteSmart($sHash));

	$debugObj->output("local_getLinkID($db, $debugObj, $sHash) : $sql");

	$iID = $db->getOne($sql);

	if (!$iID || $iID == 0) { return false; }

	return $iID;
}

/**
 * Log a new link in the DB and return its linkID
 */
function local_logNewLink($db, $debugObj, $sHash, $sURL) {
	$sql = sprintf('INSERT INTO ct_Links (hash, url) VALUES (%s, %s)',
		$db->quoteSmart($sHash),
		$db->quoteSmart($sURL)
	);

	$debugObj->output("local_logNewLink($db, $debugObj, $sHash, $sURL) : $sql");

	$db->query($sql);

	return $db->getOne('SELECT LAST_INSERT_ID()');
}

/**
 * Prior to calling this function, one or both of local_getLinkID and local_logNewLink
 * should have been called. We'll add an entry to the log table to indicate a URL
 * has been accessed. IP and useragent are logged for information purposes.
 */
function local_logLinkClick($db, $debugObj, $iLinkID, $sIP, $sUserAgent) {
	$sql = sprintf('INSERT INTO ct_Log (linkID, ip, useragent) VALUES (%d, INET_ATON(%s), %s)',
		$iLinkID,
		$db->quoteSmart($sIP),
		$db->quoteSmart($sUserAgent)
	);

	$debugObj->output("local_logLinkClick($db, $debugObj, $iLinkID, $sIP, $sUserAgent) : $sql");

	$db->query($sql);

	return;
}

?>