Number of clicks during the week

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
edwardinnz
Forum Newbie
Posts: 1
Joined: Tue Oct 14, 2008 9:37 pm

Number of clicks during the week

Post by edwardinnz »

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.
User avatar
Kadanis
Forum Contributor
Posts: 180
Joined: Tue Jun 20, 2006 8:55 am
Location: Dorset, UK
Contact:

Re: Number of clicks during the week

Post by Kadanis »

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`;";
 
Post Reply