« Return

Event tracking using MySQL database and htaccess

MySQL table structure

CREATE TABLE `events` (
`source` VARCHAR( 64 ) NOT NULL,
`media` VARCHAR( 64 ) NOT NULL,
`label` VARCHAR( 64 ) PRIMARY KEY,
`results` BIGINT( 20 ) NOT NULL,
`comments` VARCHAR( 128 ) DEFAULT NULL,
`created` DATETIME NOT NULL
) ENGINE = MyISAM DEFAULT CHARSET = utf8

 
API example:
http://domain-name.com/event-tracking/?source=publisher&media=magazine&label=foo&redirect=about.php
 

PHP

<?php
$dbh = new PDO('mysql:host=localhost; dbname=some_database', 'username', 'password', 
array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));

define("CLIENT_URL", "http://domain-name.com/");

$source    = $_GET['source'];         // publisher name
$media     = $_GET['media'];          // magazine, online, other, etc
$label     = $_GET['label'];          // a redirect name, an unique value
$redirect  = $_GET['redirect'];       // for # hash urls, use PHP urlencode function. If redirect var is empty, it defaults to the index page

if (isset($redirect)) { $redirect = urldecode($redirect); } // decode url if necessary
                                                            
$ip_address = $_SERVER['REMOTE_ADDR'];
$bypass = array('72.214.3.999', '72.214.3.111');

if (in_array($ip_address, $bypass)) :  // bypass client(s) IP address
    header("Location:" . CLIENT_URL . $redirect);
else:

  // do the stuff
  /* ========================================================================================== */
  $stmt = $dbh->prepare('SELECT * FROM events WHERE label = ?');
  $stmt->execute(array($label)); 
  $row = $stmt->fetch(PDO::FETCH_ASSOC);
  
  if ( $row ) {  
  $dbh->query("UPDATE events SET results = results+1 WHERE label = '$label'");
  } else {
  $dbh->query("INSERT INTO events VALUES('$source', '$media', '$label', '1', NULL, NOW())"); // add new entry
  }
  
  if ( $dbh != false ) {
  header("Location:" . CLIENT_URL . $redirect);
  } else {
  echo 'an error has occurred';  
  }
  /* ========================================================================================== */
endif;
?>