Sorry if this is too in depth, not sure how you site is currently set up and you haven't mentioned if you currently track the clicks.
The normal way to track link clicks would be to have a tracking script which deals with the recording of the click along with the redirect of the user.
For example, you would need the following
A links table, with a unique id for each link and the url to the product page (this could be integrated into the products table)
A tracking table to log the clicks for each link
A tracking and redirect PHP script
You would then need to replace all the links in your site that you want tracked with a link to the tracking script, and include them in your table.
Code: Select all
<a href="http://www.yoursite.tld/products.php?id=1">Sweater</a>
becomes
Code: Select all
<a href="http://www.yoursite.tld/link.php?id=1">Sweater</a>
With a record in the links table with an id of 1 and a url field of
http://www.yoursite.tld/products.php?id=1
The php script would be along the lines of
Code: Select all
<?php
//code for link.php
//get the link id
$linkID = (isset($_GET['id'])) ? $_GET['id'] : 0;
//validate
if ($linkID != 0){
//connect to db
$db = new mysqli('localhost','user','pass','database');
//add a tracking record to the tracking table
//sql statement
$sqlTrack = "INSERT INTO `tracking` SET `linkID` = '" . $linkID . "', `dateClicked` = unix_timestamp();";
//execute
$db->query($sqlTrack);
//get url for product page and redirect
//sql statement
$sqlGetLink = "SELECT `linkURL` FROM `productLinks` WHERE `linkID` = '" . $linkID . "' LIMIT 1;";
//execute
$rstGetLink = $db->query($sqlGetLink);
$getLink = $rstGetLink->fetch_assoc();
//tidy up db connections
$rstGetLink->close();
$db->close();
//redirect to the url
header('location: ' . $getLink['linkURL']);
exit(0);
} else {
//echo an error message and end
echo 'There has been an error';
}
?>
Once you have this data you could then aggregate it into weekly reports based on the timestamp and link id. Should you want to prevent the table getting too large (depending on the traffic your site gets) you could export the table weekly, monthly etc to the text file.
The SQL statement for getting your weekly reports would be along the lines of
Code: Select all
//get timestamps for last sunday and this sunday
$start = strtotime('last sunday');
$end = strtotime('this sunday');
//generate sql statement
$sql = "SELECT `linkID`, COUNT(`linkID`) AS `clicks` FROM `tracking` WHERE `dateClicked` BETWEEN '" . $start . "' AND '" . $end . "' GROUP BY `linkID`;";