Data Management

Create the table

In this Daily Feature, Vincent Danen shows you just how easy it is to use a small PHP script to track downloads from your Web site.


In this Daily Feature, we’re going to look at a very small and simple PHP script to track file downloads. Many sites offer files for download—documents, programs, pictures, or just about anything else they decide is worthy of sharing with others. Have you ever wanted to be able to track how many downloads a particular file (or any file you offer for download) receives? Using MySQL and a few lines of PHP code, you can do so easily.
If you're an IT professional who relies on the power and flexibility of Linux, I'll present a Linux shell script each month that is sure to meet your demanding needs.
What we’re talking about is a counter and redirector script. Here’s how it works: The user clicks on the file to download from your Web site. The link is actually a link to your redirector script, which is passed a variable to match against in the database, as well as the real link for the file.

For instance, my Web site, Freezer Burn, offers a number of RPM files for download. The information for the RPMs is stored in a database. The database contains a few columns that provide a unique ID number, the path of the file (to do a timestamp check), and the name of the RPM, as well as a column to track the number of downloads a particular package receives. While we’re not looking at a database containing this kind of information in this article, you can expand this technique to be more than just a simple file counter.

I can illustrate this best with a live example. Let’s suppose that we want to download a file called document.zip. Instead of making a link like this:
<a href="ftp://mysite.com/document.zip">document.zip</a>

we would use one like this:
<a href="redirect.php?file=document.zip&go=ftp://mysite.com/document.zip">document.zip</a>
This code passes two arguments to the redirect.php script. The first is $file, which contains the name of the file and is used to match the file against our database entry. The second is $go, which is the real URL to download the file.

For a very simple database that tracks only downloads of files, you’d need three columns. The first is a unique ID number, the second is the filename, and the third is the number of hits. Let’s use a database with the name counter and use the following structure for a table we call files. Launch MySQL using
mysql -u root -p

Then, at the MySQL prompt, create the database and switch to it using
CREATE DATABASE counter;
USE counter;


Now you can make the table itself using this:
CREATE TABLE files (id INT(4) UNSIGNED ZEROFILL NOT NULL PRIMARY KEY AUTO_INCREMENT,
file VARCHAR(20) NOT NULL,
hits INT(8) UNSIGNED);
Next, we come to the actual redirect.php script. Your database is complete and ready to be used with the following PHP file:
<?php
if ($file) {
 mysql_pconnect("localhost","user","")
 or die("Unable to connect to SQL server");
 $query = "SELECT * FROM files WHERE file = \"$file\"";
 $tmp = mysql_db_query(counter, "$query") or die("Select failed");
 $filex = mysql_fetch_array($tmp);

 $id = $filex['id'];

 $query = "UPDATE files SET hits = hits + 1 WHERE (id=$id)";
 $result = mysql_db_query(counter, "$query");
 if (!$result) {
 echo "There was an SQL error!<br />";
 echo mysql_errno() .": ".mysql_error()."<br />";
 } else {
 Header("Location: $go");
 }
} else {
 Header("Location: http://mysite.com/downloads.php");
}
?>


This script is extremely simple and works like a dream. It initially connects to the database and retrieves the ID number from the database that corresponds to the filename passed in the $file variable. It then assigns the value of $filex['id'], which is the retrieved ID number, to the variable $id. For some reason, PHP doesn’t like a MySQL query string that contains a variable like $filex['id'], so we reassign it to a variable name it likes better.

Then we use the SQL UPDATE command to update the database's hit counter. We increment the value of hits by one each time redirect.php is called, using the SQL query itself to do the math. If there is an error, the script will display the SQL error number and description. If the update is successful, the user is redirected to the URL stored in the variable $go, which is the true download location for the file.

As a safe measure, we make sure that if someone loads redirect.php without a valid $file variable, we redirect them to the site's download page (or any other page you feel is appropriate).

From this point, you can use the information stored in the database however you like. The whole download process is entirely transparent to users. When they click on the filename, they will immediately be prompted with the download dialog box of their browser. The SQL transaction is so fast that it is virtually nonexistent.

As you can see, building a file counter is very easy, and you use a very small script to accomplish the task. You can even go so far as to have your download page pull the value of the hits column from the database and display it beside the filename so that your visitors can see how many downloads a particular file has received. Or you can keep the information entirely internal, and the user will never be the wiser.

Obviously, the script can be expanded to do more than simply count the number of downloads a file has received. With a little work, it can be used to deny downloads to certain IP addresses or domain names, to record the IP address of the person who downloads the file, and so forth. For this illustration, it is a simple file counter, but with a little creativity, it can become much more than that.
The authors and editors have taken care in preparation of the content contained herein but make no expressed or implied warranty of any kind and assume no responsibility for errors or omissions. No liability is assumed for any damages. Always have a verified backup before making any changes.

About Vincent Danen

Vincent Danen works on the Red Hat Security Response Team and lives in Canada. He has been writing about and developing on Linux for over 10 years and is a veteran Mac user.

Editor's Picks

Free Newsletters, In your Inbox