Php: Log Clicks on Links to External Sites - - Web Development, Programming, SEO

Thursday, May 21, 2009

Php: Log Clicks on Links to External Sites

The following demonstrates a method of logging clicks on hyperlinks to external sites using Php and MySQL. This can be used to generate statistics for content on your site such as "Popular links/sites", or keeping track of a frequent user or member's actions (ie. Recently Browsed).

The Php uses the PEAR MDB2 Package to create a connection to the database. For information on installing or using PEAR and the MDB2 package, see
Once the processing has completed for the item/link, the user is redirected to the address of the selected url.




$url = "";
$item_id = null;

//Create/get an instance of the DBController
$db = DbController::instance();

//get item url from query string

$item_id = $_GET['id'];
//Get url of item

if ($item_id != "")
$item = $db->getItem($item_id);

$url = "./";
$url = $item['url'];

//log hit statistics
//Not currently logging members
$success =  $db->logStats($item_id, NULL);

if ($success == true)
print "Logged Successfully<br/>";
print "Logging Failed<br/>";
else //No id supplied - redirect to site root
$url = "./";
else //No url found - redirect to site root
$url = "./";

Header ("Location: $url");
//print $url;



The DBController class provides a constructor, which uses MDB2 to construct a DSN and connect to the database. When the instance() method is called, an instance is created if required, or retured if an instance exists. Currently, the id, the current date/time and member id is captured for each click on a hyperlink or item.


class DbController {

private static $instance;
private $db;

//This function is called to create an instance of
//the DBController class, or return the instance if it
//already exists
public static function instance() {
if(isset(DbController::$instance)) {
return DbController::$instance;
} else {
$instance = new DbController;
return $instance;

//Constructor - create database connection
private function __construct() {
//connect to the db
$host = 'localhost';
$port = '6345';
$user = 'dbuser';
$pass = 'password';
$dbase = 'dbname';

//Construct DSN and connect to the database
$dsn = "mysql://$user:$pass@$host:$port/$dbase";
$this->db = MDB2::factory($dsn, array('debug' => 4));  

//This function takes the id of the link or item which was clicked
//on and creates a record in the database.  Changes are not applied
//if an error occurs.
public function logStats($item_id, $member_id = NULL) {
$sql = "INSERT INTO `dbname`.`statatisticss` (


$params = array($item_id, $member_id);
$types = array("integer","integer","timestamp","integer");
$stmt = $this->db->prepare($sql, $types);
$res = $stmt->execute($params);

if($res == false)

return false;

return true;



Post a Comment