Affiliate statistics

This script can be used to keep statistics about the number of visits you refer to your affiliates or advertisers, for example, but it can also be used for your downloads. Here we'll create a click counter that will count total clicks, clicks this month and clicks last month for each site.

First we make a dump in MySQL for the table:

CREATE TABLE `affiliates` (
`id` int(4) NOT NULL auto_increment,
`url` varchar(250) NOT NULL default '',
`name` varchar(100) NOT NULL default '',
`this_month` int(5) NOT NULL default '0',
`last_month` int(5) NOT NULL default '0',
`month_count` tinyint(2) NOT NULL default '0',
`total` int(6) NOT NULL default '0',
PRIMARY KEY (`id`)
)

We'll create two files to make it simpler: one to count clicks (afi.php) and another to show the table content and statistics (affiliates.php).

Click count: afi.php

<?php $id $_GET['id'];
$id trim(htmlentities(strip_tags($id))); //Clean ID

if (!$id) { //If the ID is not set, redirect to the affiliate page
    
header('Location: affiliates.php');
} elseif (
$id) {

    
//Database connection information
    
$hostname="localhost";
    
$dbase="your_db_name";
    
$user="your_db_username";
    
$pass="your_db_password";
    
$connection mysql_connect("$hostname"$user"$pass");
    
$db mysql_select_db($dbase $connection);

    
$q="SELECT * FROM affiliates WHERE id='$id'";
    
$resultmysql_query($q) or die ("Could not execute query : $q." .mysql_error());

    while (
$row=mysql_fetch_array($result)) {
        
extract($row);

        
$current_month date('n'); //We retrieve the current month

        
if ($current_month == $month_count) {
            
//If the current month is the same than the month stored at the DB, add a click
            
$q="UPDATE affiliates SET this_month=this_month+1, total=total+1 WHERE id='$id'";
            
$resultmysql_query($q) or die ("Could not execute query : $q." mysql_error());
        } else {
            <
i>//Else, change this month's clicks to last months', change stored month and start the current month from 0
            
$q="UPDATE affiliates SET last_month='$this_month', this_month=1, month_count='$current_month', total=total+1 WHERE id='$id'";<br />
            
$resultmysql_query($q) or die ("Could not execute query : $q." mysql_error()); <br /><br />

        }
        
header('Location: '.$url);

    }
?>

What we do is get the affiliate id from the URL (afi.php?id=1, afi.php?id=2...) and clean it so that noone enters malicious text there. We check if the ID is set, if someone enters the page directly (afi.php), it redirects to the affiliate page. Once it has the ID, we put the database connection information and extract all data from the table. We retrieve the current month and compare it with the month saved at the database, if they're the same, it will just add one click to the current month and to the total, but if it's not the same month, we'll pass the number of clicks stored at the current month to the past month, change the month number stored and start the current month count from 1.

Show statistics: affiliates.php

Now we'll create a nifty page to show all sites and their statistics.

<?php //Database connection information
$hostname="localhost";
$dbase="your_db_name";
$user="your_db_username";
$pass="your_db_password";
$connection mysql_connect("$hostname"$user"$pass");
$db mysql_select_db($dbase $connection); ?>

<table cellspacing="1" cellpadding="2" border="0">
<tr>
<td><b>Site</b></td>
<td><b>Clicks this month</b></td>
<td><b>Clicks last month</b></td>
<td><b>Total clicks</b></td>
</tr>

<?php
$q
="SELECT * FROM affiliates ORDER BY name ASC";
$resultmysql_query($q) or die ("Could not execute query : $q." mysql_error());

while (
$row=mysql_fetch_array($result)) {
    
extract($row); ?>

    <tr><br />
    <td><a href="afi.php?id=<?php echo "$id"?>"><?php echo "$name"?></a></td>
    <td><?php echo "$this_month"?></td>
    <td><?php echo "$last_month"?></td>
    <td><?php echo "$total"?></td><br />
    </tr>

<?php }

echo 
"</table>"?>

That's it, it will look something like what I've done at the advertising page. If you have any question or problem with this tutorial, please ask at the forum.

Tutorial © Melfina. Do not reproduce in any way without previous permission from the author. Printed from http://celestial-star.net