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