Php: Log Clicks on Links to External Sites - Code-Tips.com - 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 http://pear.php.net/.
Once the processing has completed for the item/link, the user is redirected to the address of the selected url.

load.php




<?php

require_once('include/DbController.php');

$url = "";
$item_id = null;

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

//get item url from query string
if(isset($_GET['id']))
{

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

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

if(empty($item))
{
$url = "./";
}
else
{
$url = $item['url'];

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

/*
//Debug
if ($success == true)
{
print "Logged Successfully<br/>";
}
else
{
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;

?>






DBController.php

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.

<?php
require_once('MDB2.php');

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` (
`hit_id`,
`item_id`,
`date_time`,
`member_id`
)
VALUES ( NULL, ?, CURRENT_TIMESTAMP, ?)

";

$this->db->beginTransaction();
$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)
{
$this->db->rollback();

return false;
}
else
{
$this->db->commit();

return true;
}
}

}





0 comments:

Post a Comment