Codular

HomeWriters RSS

Build It: Download Tracker

Introduction

People love giving away free stuff, for example a collection of awesome icons. One thing people also love is keeping track of how many people download their items. How else are you able to claim a popular item? Using a super simple, but effective download manager is one of the best ways.

Here you'll be guided through the whole process from start to finish, beginning with some htaccess tweaks, all the way through to the database structure and file delivery methods with PHP.

Outline

There will of course be scope for improvement throughout, but the idea is to provide the basics needed for you to then push them forward. We'll be using MySQLi for database connectivity, and some regular expressions too.

We'll build it so that each item is given a unique id that can be used to then identify the file that should be downloaded. The user is then given the file as a named download, with all downloads tracked and counted.

The Database

We're going to be using two tables, one to keep a track of every download that is available and another to allow us to track every download.

`files`

This will have a few columns:

The SQL:

CREATE TABLE `files` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `file` varchar(150) NOT NULL,
    `available` tinyint(1) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
) ENGINE = InnoDB, CHARSET = UTF8;

By default we're making it so that downloads aren't available to download, but you can go in and change this to a 1 to enable that file for download.

`downloads`

We will want to simply track the IP, time and file that was downloaded:

CREATE TABLE `downloads` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `file_id` int(10) NOT NULL,
    `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `ip` varchar(39) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB, CHARSET = UTF8;

Note: Many people will argue that an IP should be stored as an integer for improvements to indexing and searching. We're storing it for the simple reason of a log, it's not a huge issue. If you were to store it as an integer, you'd have to use the specific IP to int conversion methods that are available.

We're using file_id which will act as a foreign key to the id field in the files table, so we know which download the user downloaded.

.htaccess

We're going to be using super simple URLs like /1, where 1 is the id in the files table. For this we'll need to rewrite any URL that is just an integer to a download.php file passing the integer as a GET parameter. For this, we're going to use some .htaccess mixed with some regular expressions and come up with:

RewriteEngine On
# Rewrite integer to download
RewriteRule ^(\d+)$ download.php?id=$1

The Magic

The process behind the PHP file is simple:

  1. Connect to the database
  2. Check if the ID is valid
  3. Check if the download is enabled
  4. Check the file exists
  5. Get the file's MIME type
  6. Log the download
  7. Set the headers and send the file

It's as simple as that, so away we go - remember this PHP code goes in a file called download.php as we highlighted in our .htaccess file:

Database connection

We're going to be using MySQLi for this, and we'll use the OO syntax, and assign it to a variable called $db:

$db = new mysqli('localhost', 'user', 'pass', 'download_mgr');

if($db->connect_errno > 0){
    die('Unable to connect to database [' . $db->connect_error . ']');
}

Check the ID is valid

The ID should be an integer, and will be stored in the GET global array that is available in PHP. We'll want to firstly check that the number is set, then check that it is numeric. If at any point the ID doesn't pass a test, we'll throw an error for the user:

if(!isset($_GET['id']) || !is_numeric($_GET['id']) || empty($_GET['id'])){
    die('You entered an invalid ID.');
}

*Note, it's possible to do this using the filter_var() method that PHP offers. This will return the value if the filter succeeds, or false if it doesn't. In our case, we want to check that the ID is an integer: *

if(filter_var($_GET['id'], FILTER_VALIDATE_INT) === FALSE){
    die('You entered and invalid ID.');
}

Next we need to take that ID and run a SELECT on the database to check that it exists for a download at the moment. Remember, we need to escape the value here, below is the simple SQL query that we'll use, modify and escape as required:

SELECT `file`, `available` FROM `files` WHERE `id` = $_GET["id"];

So throwing that in we check the number of returned rows, and error if there are 0 rows:

$sql = "SELECT `file`, `available` FROM `files` WHERE `id` = " . $db->escape_string($_GET['id']);
if(!$query = $db->query($sql)){
    die('There was an error running the query [' . $db->error . ']');
}
if($query->num_rows === 0){
    die('You entered an ID of a non-existant download.');
}

Check if the file is downloadable

We need to fetch the result from the query, and then just check the value that is set for available:

$result = $query->fetch_assoc();
if($result['available'] === 0){
    die('This file is not enabled for download.');
}

Nothing too complicated here, just basic logic.

Check the file exists

Next, we need to check that the file actually exists where it says it does. There shouldn't be a case where this happens, but we should always be careful. As a little side, we'll also update the database to set the download to not available if the file doesn't exist.

For this whole system, we're going to assume that the files are stored in a directory called 'downloads', and the name of the file is the same as the string that is in the file column in the database.

// Path to the file
$path = 'downloads/' . $result['file'];
// Check if the file exists
if(file_exists($path) === FALSE){
    // The files doesn't exist, so let's set it to not available in the database
    $db->query('UPDATE `files` SET `available` = 0 WHERE `id` = ' . $db->escape_string($_GET['id']);
    die('That file does not seem to exist.');
}

Get the file's MIME type

A MIME type tells us the format that the file has, this is vital so that we can tell the browser what sort of file we're forcing the user to download, and it can then handle it in a suitable way.

To do this we'll be using the Fileinfo feature, this is a class that is available in PHP that helps deal with information about files. Perfect for what we need in this case:

$fileInfo = new finfo();
$mime = $fileInfo->file($path, FILEINFO_MIME_TYPE);

You'll see here that we're sending the path to the file as the first parameter, and then a predefiend constant as the second. This is telling the method that we just want the MIME type of the file.

Note: if this doesn't work, you need to make sure that you're using PHP 5.3

Log the download

We're nearly there, very very nearly! We just need to put some things into the database, and then we'll be ready to start serving the file to our users. This is simple, we're just going to insert a new row with the id of the file, as well as the IP of the user. Remember, the time is the default value for newly inserted values.

$db->query('INSERT INTO `downloads` (`file_id`, `ip`) VALUES (' . $db->escape_string($_GET['id'] . ', ' . $db->escape_string($_SERVER['REMOTE_ADDR'] . ')');

Running that query should then insert a new row in the database for every download, simple, now let's send the file out to the user.

Set the headers and send the file

This ultimately takes three lines:

  1. Set the content type to the mimetype that we found out before
  2. Tell the browser this is a download
  3. Read the file to the browser

For the first two points, we're going to use the header() method:

header('Content-Type: ' . $mime);
header('Disposition: attachment; filename=' . $result['file']);

Finally, we can use the readfile() method to get the contents of the file to the browser:

readfile($path);

Now the user will be provided a file to download, with the name that we put in the database, and the download count will be recorded.

Support and Download

The tutorial shows the basics of how to build the download manager with PHP, there's a lot of scope for improvement and additions such as making downloads password protected. Or making it so that they're only available between a set time frame etc.

Be sure to share the link if you find it helpful, or if you have any questions get in touch with us on Twitter.