PHP & MySQL File Download Counter

It has been a while since we've done a proper PHP & MySQL tutorial here, at Tutorialzine, so today we are creating a simple, yet robust, file download tracker.

Each file will have a corresponding row in the database, where the total number of downloads is saved. PHP will update the MySQL database and redirect the visitors to the appropriate files.

To track the number of downloads, you just need to upload your files to the files folder, and use a special URL to access them.

Note: This tutorial is quite old and doesn't work in PHP7 and above. We are keeping it online only as a reference.

Step 1 - XHTML

The first step is to lay down the XHTML markup of the tracker. It is pretty straightforward - we have the file-manager div, which contains an unordered list with each file as a li element.

The files, that are going to be tracked, are put into the files folder in the script root directory (you can see how the file structure is organized in the demonstration zip file). PHP then loops through all the files and adds each one as a separate li element to the unordered list.

demo.php

<div id="file-manager">

    <ul class="manager">

        <!-- The LI items are generated by php -->
        <li><a href="download.php?file=photoShoot-1.0.zip">photoShoot-1.0.zip
            <span class="download-count" title="Times Downloaded">0</span> <span class="download-label">download</span></a>
        </li>
    </ul>

</div>

Notice the href attribute of the hyperlink - it passes the name of the file as a parameter to download.php. This is where the download tracking happens, as you will see in a moment.

You are not limited to this interface in order to provide download tracking - you can just post the links to download.php in your blog posts or site pages, and all downloads will be correctly tracked.

Step 2 - CSS

With the XHTML markup in place, we can now concentrate on the presentation side of script. The CSS rules below target the file-manager div by id (with the hash symbol), as it is present only once in the page, and the rest of the elements by class names.

styles.css

#file-manager{
    background-color:#EEE;
    border:1px solid #DDD;
    margin:50px auto;
    padding:10px;
    width:400px;
}

ul.manager li{
    background:url("img/bg_gradient.gif") repeat-x center bottom #F5F5F5;
    border:1px solid #DDD;
    border-top-color:#FFF;

    list-style:none;
    position:relative;
}

ul.manager li a{
    display:block;
    padding:8px;
}

ul.manager li a:hover .download-label{
    /* When a list is hovered over, show the download green text inside it: */
    display:block;
}

span.download-label{
    background-color:#64B126;
    border:1px solid #4E9416;
    color:white;
    display:none;
    font-size:10px;
    padding:2px 4px;
    position:absolute;
    right:8px;
    text-decoration:none;
    text-shadow:0 0 1px #315D0D;
    top:6px;

    /* CSS3 Rounded Corners */

    -moz-border-radius:3px;
    -webkit-border-radius:3px;
    border-radius:3px;
}

span.download-count{
    color:#999;
    font-size:10px;
    padding:3px 5px;
    position:absolute;
    text-decoration:none;
}

The interesting part here is that the download label is hidden by default with display:none. It is shown with display:block only when we are hovering over its parent <a> element, and thus the right label is shown without a need for using JavaScript. A bit of CSS3 is also used as well to round the corners of the download label.

Step 3 - PHP

As mentioned earlier, PHP loops through the files folder, and outputs each file as a li element in the unordered list. Now lets take a closer look at how this happens.

demo.php - Top Section

// Error reporting:
error_reporting(E_ALL^E_NOTICE);

// Including the DB connection file:
require 'connect.php';

$extension='';
$files_array = array();

/* Opening the thumbnail directory and looping through all the thumbs: */

$dir_handle = @opendir($directory) or die("There is an error with your file directory!");

while ($file = readdir($dir_handle))
{
    /* Skipping the system files: */
    if($file{0}=='.') continue;

    /* end() returns the last element of the array generated by the explode() function: */
    $extension = strtolower(end(explode('.',$file)));

    /* Skipping the php files: */
    if($extension == 'php') continue;

    $files_array[]=$file;
}

/* Sorting the files alphabetically */
sort($files_array,SORT_STRING);

$file_downloads=array();

$result = mysql_query("SELECT * FROM download_manager");

if(mysql_num_rows($result))
while($row=mysql_fetch_assoc($result))
{
    /*  The key of the $file_downloads array will be the name of the file,
        and will contain the number of downloads: */

    $file_downloads[$row['filename']]=$row['downloads'];
}

Notice how we are selecting all the rows from the download_manager table with mysql_query(), and later adding them to the $file_downloads array with the filename as a key to the number of downloads. This way, later in the code, we can write $file_downloads['archive.zip'], and output how many times this file has been downloaded.

You can see the code we use to generate the li items below.

demo.php - Mid Section

foreach($files_array as $key=>$val)
{
    echo '<li><a href="download.php?file='.urlencode($val).'">'.$val.'
        <span class="download-count" title="Times Downloaded">'.(int)$file_downloads[$val].'</span> <span class="download-label">download</span></a>
    </li>';
}

It is simple as that - a foreach loop on the $files_array array, and an echo statement which prints all the markup to the page.

Now lets take a closer look at how exactly are the downloads tracked.

download.php

// Error reporting:
error_reporting(E_ALL^E_NOTICE);

// Including the connection file:
require('connect.php');

if(!$_GET['file']) error('Missing parameter!');
if($_GET['file']{0}=='.') error('Wrong file!');

if(file_exists($directory.'/'.$_GET['file']))
{
    /* If the visitor is not a search engine, count the downoad: */
    if(!is_bot())
    mysql_query("   INSERT INTO download_manager SET filename='".mysql_real_escape_string($_GET['file'])."'
                    ON DUPLICATE KEY UPDATE downloads=downloads+1");

    header("Location: ".$directory."/".$_GET['file']);
    exit;
}
else error("This file does not exist!");

/* Helper functions: */

function error($str)
{
    die($str);
}

function is_bot()
{
    /* This function will check whether the visitor is a search engine robot */

    $botlist = array("Teoma", "alexa", "froogle", "Gigabot", "inktomi",
    "looksmart", "URL_Spider_SQL", "Firefly", "NationalDirectory",
    "Ask Jeeves", "TECNOSEEK", "InfoSeek", "WebFindBot", "girafabot",
    "crawler", "www.galaxy.com", "Googlebot", "Scooter", "Slurp",
    "msnbot", "appie", "FAST", "WebBug", "Spade", "ZyBorg", "rabaz",
    "Baiduspider", "Feedfetcher-Google", "TechnoratiSnoop", "Rankivabot",
    "Mediapartners-Google", "Sogou web spider", "WebAlta Crawler","TweetmemeBot",
    "Butterfly","Twitturls","Me.dium","Twiceler");

    foreach($botlist as $bot)
    {
        if(strpos($_SERVER['HTTP_USER_AGENT'],$bot)!==false)
        return true;    // Is a bot
    }

    return false;   // Not a bot
}

It is important to check if, by any chance, the visitor is a search engine robot scanning your links and not a real person. Robots are a good thing, as they get you included in services like Google Search, but in a situation such as this, can skew your download statistics. This is why the database row is updated only after the visitor passes the is_bot() validation.

Step 4 - MySQL

As mentioned in the previous step, the download count is stored as a row in the download_manager table in your MySQL database. First, lets explain how this particular query works:

download.php

INSERT INTO download_manager SET filename='filename.doc'
ON DUPLICATE KEY UPDATE downloads=downloads+1

It tells MySQL to insert a new row in the download_manager table, and set the filename field of the row to the value of the requested file for download. However, the filename field is defined as a unique index in the table. This means that a row can be inserted only once, otherwise a duplicate key error will occur.

This is where the second part of the query kicks in - ON DUPLICATE KEY UPDATE will tell MySQL to increment the downloads column by one if the file already exists in the database.

This way new files will be automatically inserted in the database the first time they are downloaded.

Step 5 - jQuery

To make the download tracking feel almost like real-time, it will be a nice addition to update the counter next to the file name once the user initiates the download. Otherwise they would have to initiate a page refresh so that new stats for the counter are shown.

We will achieve this with a little jQuery trick:

script.js

$(document).ready(function(){
    /* This code is executed after the DOM has been completely loaded */

    $('ul.manager a').click(function(){

        var countSpan = $('.download-count',this);
        countSpan.text( parseInt(countSpan.text())+1);
    });
});

We just assign a click handler to the links that point to the files, and every time one of them is clicked, we increment the number inside of the counter span tag.

Step 6 - htaccess

There is one more thing we need to do, before we call it a day. What download.php does is to redirect the visitor to the requested file that was passed as a parameter. However you may have noticed that, for certain file types, the default browser behavior is to open them directly. We want to initiate a download instead. This is achieved with a couple of lines inside of a .htacess file, found in the files directory:

<Files *.*>
ForceType application/octet-stream
</Files>

With this our File Download Counter is complete!

Conclusion

To run the demo on your own server, you will need to recreate the download_manager table in a MySQL database you have access to. You can find the needed SQL code that will create the table for you in table.sql, which you can find in the download archive.

After this, just add your login details for the database (as provided by your webhost) to configuration.php.

What do you think? How would you improve this example?