This is a project I was assigned at work. It uses JavaScript, PostgreSQL and PHP, but as I think it's the JavaScript that's not behaving as expected I am posting it to this forum.
Database layout:
Code:
es_trackhits
============
hit_id integer NOT NULL nextval('es_trackhits_hit_id_seq'::regclass)
hit_count integer NOT NULL
hit_week integer NOT NULL
hit_period integer NOT NULL
hit_prodcode character varying(16) NOT NULL
hit_cide character varying(32) NOT NULL
hit_pla character varying(32) NOT NULL
hit_cre character varying(32) NOT NULL
hit_query character varying NOT NULL
Hit_id is a primary key. Hit_query is defined as a unique key.
PHP code: (index.php)
PHP Code:
<?php
ignore_user_abort (true);
// Debug options
define ("DEBUG_QUERY_ECHO", false); // Show SQL queries
// SQL Database options
define ("CFG_DB_HOST", 'localhost');
define ("CFG_DB_PORT", '5432');
define ("CFG_DB_DATABASE", 'eshot');
define ("CFG_DB_USERNAME", '********');
define ("CFG_DB_PASSWORD", '********');
// Tables
define ("TBL_TRACKHITS", 'es_trackhits');
// Cookie options
define ("CFG_CK_EXPIRE", 2147483640); // Timestamp neat the highest possible
define ("CFG_CK_SEP", ' '); // Value seperation character
// Regex
define ("CFG_REGEX_TRACKSTRING", '/^code=[a-zA-Z0-9]+&cide=[a-zA-Z0-9_]+&pla=p[0-9]+w[0-9]+&cre=[a-zA-Z0-9_]+$/');
define ("CFG_REGEX_SPLIT_PERIOD", '/[pw]/');
function createTrackHitCount ($trackData)
// Create a new track hit counter
{
if (is_array ($trackData))
{
$query = 'INSERT INTO '.TBL_TRACKHITS.' (
hit_count,
hit_week,
hit_period,
hit_prodcode,
hit_cide,
hit_pla,
hit_cre,
hit_query
) values (
1,
'.intval ($trackData ['week']).',
'.intval ($trackData ['period']).',
\''.pg_escape_string ($trackData ['code']).'\',
\''.pg_escape_string ($trackData ['cide']).'\',
\''.pg_escape_string ($trackData ['pla']).'\',
\''.pg_escape_string ($trackData ['cre']).'\',
\''.pg_escape_string ($trackData ['queryString']).'\'
);
';
// Display query for debugging
if (DEBUG_QUERY_ECHO)
{
echo (htmlspecialchars ($query)."<br />\n");
}
// Perform query
return (@pg_query ($query));
}
}
function updateTrackHitCount ($trackData)
// Update track hit counts
{
if (is_array ($trackData))
{
$query = 'UPDATE '.TBL_TRACKHITS.'
SET hit_count = hit_count +1
WHERE hit_query = \''.pg_escape_string ($trackData ['queryString']).'\';
';
// Display query for debugging
if (DEBUG_QUERY_ECHO)
{
echo (htmlspecialchars ($query)."<br />\n");
}
// Perform query
return (@pg_query ($query));
}
}
function logHit ($trackData)
// Log a product hit
{
$result = updateTrackHitCount ($trackData);
if (($rowCount = pg_affected_rows ($result)) === 0)
{
$createResult = createTrackHitCount ($trackData);
$rowCount = pg_affected_rows ($createResult);
}
return (intval ($rowCount));
}
// Prevent browser cacheing
header ("Expires: Mon, 26 Jul 1997 05:00:00 GMT" ); // disable IE caching
header ("Last-Modified: ".gmdate ("D, d M Y H:i:s")." GMT");
header ("Cache-Control: no-cache, must-revalidate");
header ("Pragma: no-cache");
// Check the IP address is not an internal one
if (1)
{
// Check for correctly formatted query string
if (preg_match (CFG_REGEX_TRACKSTRING, $_SERVER [QUERY_STRING]))
{
// Check for multiple submissions
if (strpos ($_COOKIE ['visited'], md5 ($_SERVER [QUERY_STRING])) === false)
{
// Parse the query string into a format suitable for committing to the database
$periodSplit = preg_split (CFG_REGEX_SPLIT_PERIOD, $_GET ['pla'], 2, PREG_SPLIT_NO_EMPTY);
$trackInfo = array (
'code' => $_GET ['code'],
'cide' => $_GET ['cide'],
'pla' => $_GET ['pla'],
'period' => $periodSplit [0],
'week' => $periodSplit [1],
'cre' => $_GET ['cre'],
'queryString' => $_SERVER [QUERY_STRING]
);
// Connect to the database
if ($dbHandle = @pg_connect ( 'host='.CFG_DB_HOST
.' port='.CFG_DB_PORT
.' dbname='.CFG_DB_DATABASE
.' user='.CFG_DB_USERNAME
.' password='.CFG_DB_PASSWORD))
{
// Commit tracking info to database
if (logHit ($trackInfo))
{
// Store tracking info in cookie to prevent multiple logging
if ($_COOKIE ['visited'])
{
setcookie ('visited', $_COOKIE ['visited'].CFG_CK_SEP.md5 ($_SERVER [QUERY_STRING]), CFG_CK_EXPIRE);
}
else
{
setcookie ('visited', md5 ($_SERVER [QUERY_STRING]), CFG_CK_EXPIRE);
}
}
// Close database connection
pg_close ($dbHandle);
}
}
}
}
// Send a transparent 1 pixel GIF
header ('Content-type: image/gif');
readfile ('spacer.gif');
// Debug
if ($fHandle = fopen ('hits.log', 'a'))
{
fwrite ($fHandle, time ().' - '.$_SERVER [QUERY_STRING]."\n");
fclose ($fHandle);
}
?>
The logging code at the bottom is just for debugging and will be removed for the final version. The IP check hasn't been implemented yet. It will prevent IPs on the local subnet from generating hits when it is.
Javascript code: (hitlogger.js)
Code:
var hitLogger = 'http://localhost/tracker/index.php';
function getProductCode (thisURL)
// Parse the access URL to get the product code
{
// Get start and end points
startIndex = thisURL.lastIndexOf ('/');
endIndex = thisURL.indexOf ('?');
// The product code is located between the final '/' character and the first '?' character
if ((startIndex != -1) && (endIndex != -1))
{
thisURL = thisURL.substring (startIndex + 1, endIndex);
}
else
{
thisURL = null;
}
return (thisURL);
}
function makeQueryString (thisURL)
// Create a query string for the hit counter script
{
// Get product code
if (codeString = getProductCode (thisURL))
{
trackURL = '?code=' + codeString;
// Append query string
if (queryString = thisURL.substring ((thisURL.indexOf ('?')) + 1))
{
trackURL = trackURL + '&' + queryString;
}
else
{
trackURL = null;
}
}
else
{
trackURL = null;
}
return (trackURL);
}
window.onload = function ()
{
if (queryString = makeQueryString (window.location.href))
{
// Create an image node to invoke the hit logger
trackLink = document.createElement ('img');
trackLink.setAttribute ('src', hitLogger + queryString);
trackLink.setAttribute ('width', 0);
trackLink.setAttribute ('height', 0);
trackLink.setAttribute ('alt', '');
trackLink.setAttribute ('style', 'display: none;');
document.body.appendChild (trackLink);
}
}
The goal:
I work for a sales company that sells various items through several online stores. From time to time we run promotions by sending out emails (they are explicitly subscription only, we have no intention of spamming our customers) with links to products. These links have a query string tacked on at the end so we can track the popularity of our promotions The query strings only identify the promotion, not the user.
An example URL would be
http://example.com/products/ABCD1234...=p10w3&cre=sbu
ABCD1234 is the internal code we use to identify a product (a SKU). cide is always set to eshot_core. pla is an internal company period and week within the period. We split a year into 13 periods of 4 weeks each, so p10w3 means the third week of period 10. The cre token is a simple text string used to identify a specific promotion.
We use a tracking company that runs the storefronts in a sandbox that allow for very accurate tracking information, but we are only given updates at the end of given intervals (at the end of each month I think), so I was asked to write a tracking system that would give us a reasonable ball-park tracking figure in real time to give us a rough idea of what to expect from the tracking company when they hand us their results. We wanted to log into a postgres database but couldn't do the scripting server side, so it was instead decided to use a javascript to invoke a hit logger located on another machine.
The idea is that the javascript will be linked to on pages that we want to track hits on. The javascript on load reformats the URL of the page being hit into a format suitable for use by the hitlogger. It then generates an image DOM node and inserts it into the document at the bottom, thus invoking the PHP hit logger.
The PHP code checks a cookie to see if this particular user has already visited this particular page. If not then it will store a page hit in the database and update the user's cookie (URLs are stored in the cookie as MD5 hashes partly because it saves space and partly for privacy reasons).
The problem:
I created a set of test pages and another page that links to them for testing. I tried several senarios with IE 6, FireFox 2 and Opera 9 to ensure the logger works over a wide variety of browsers. I tried visiting a page, using the back button and revisiting it by following the link again, using the back button and then the forward button to revisit a page, rapid reloading of the same page, and rapidly going back and visiting different pages.
At this point, I discovered that when I visited a page, went back immediately and followed a link to a different page I could sometimes provoke Internet Explorer into invoking the hit counter twice instead of only once. Additionally the second invokation occured before the cookie for the first invokation had been set. This resulted in 2 hits being logged instead of just one as intended. I couldn't replicate this behaviour in either Opera or FireFox.
Is there some problem with the way I am invoking the hit counter in my JavaScript? Is it a bug in the script, or is there a bug in Explorer itself? If there is a bug in Explorer can I work around it somehow? The counter doesn't have to be absolutely accurate, it only needs to give a ball-park estimate of how often a particular link is being visited, but that doesn't mean I can just ignore known problems like that as I would like the results to be reasonably accurate.
Before you post, read our: 
