Hello, I have a website with a listing of products. I would like to calculate the number of hits/clicks on each product each week.
The website is database driven so I assume the output value can be stored in a column called 'weekly_clicks', which gets displayed in the admin page, and rolls over each week - ending & starting from Sunday 00:00. I would also like the output value to be stored in a .txt file so that I have a record of each week's statistics heading with each product's name or id.
My php knowledge is quite basic so any help would be much appreciated.
Thanks in advance.
Number of clicks during the week
Moderator: General Moderators
-
edwardinnz
- Forum Newbie
- Posts: 1
- Joined: Tue Oct 14, 2008 9:37 pm
Re: Number of clicks during the week
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.
becomes
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
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
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>
Code: Select all
<a href="http://www.yoursite.tld/link.php?id=1">Sweater</a>
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';
}
?>
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`;";