PHP & MySQL File Download Counter

Download

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.

i11.png
Download Counter Interface

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.

i21.png
Hover State With CSS

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.

i31.png
Structure of the download_manager Table

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?

Bootstrap Studio

The revolutionary web design tool for creating responsive websites and apps.

Learn more

Related Articles

Great write-up! I didn't know about the .htaccess trick. Good to know. Could you also post the MySQL table structure in the tutorial?

Martin Angelov

Thank you for bringing that up. I added the table structure to Step 4.

um Martin Angelov you are from bulgaria?

very good tutorial

Thank u for this tutorial. But I think there is one big problem in this method.
When I press button 'download', counter is incremented. I may cancel download and press 'download' again...
May be solution is add check session. In this way u may Increment counter only in one session. What do u think?

Bloggerzbible

Its very nice. Good work

Taylor Hunt

Gurza: Though, jquery/javascript is client-side, the counter script is server-side, so the methodology is correct. Whether you hit cancel or not is up to the client, but the server should still respond that an attempted download has occurred. The only way for a 'completed count' to happen would be a 3rd party application/active x control. A web-server will never know whether a client side download has fully completed without a client-side app. If you are looking for a jquery/flash(active x) solution that gives percentage of download with callback upon finish, I'd suggest uploadify ( http://www.uploadify.com/). Implementing a session based download manager would not be able to establish whether a completed download has occurred.

Martin Angelov

@ Gurza
@ Taylor Hunt

To be able to track when a download was completed server-side, you could optionally read the file and output it entirely with PHP (with the appropriate header) instead of redirecting the browser.

This way, when you read and output the last few bytes of the file, you would know for sure that the client downloaded it successfully, and update the data_manager table.

This, however, is above the level of the tutorial and the current solution (with a redirect) will be sufficient for most users.

@Taylor Hunt
@Martin Angelov

Yes, methodology is correct and this tutorial will be sufficient for MOST users. But...
We can write into section variable when download was started by user. And check this variable if download will be started again.

Daniel Bidmon

very nice to use in a selfmade cms system!

Great tutorial I will be sure to use it when I put up the download section of my website.

Great work, amazing tutorial thank you

nice tutorial .. thank you

Ótimo trabalho.

Parabéns tutoriazine.

LOVE THIS! Would love to see a way though to keep track of who is downloading. Like a shadow box popup or a slide out or down that asked people for basics like Name, Email, Phone.... I want to keep track of who is downloading my files for rights and credit issues.

anyways, LOVE the site, Love all this awesome stuff I'm learning. Thanks so much!!

jason

Nikola M.

Great tutorial, thank you

MadTogger

Excellent tutorial, thank you.

I had been trawling the internet for a decent Download Counter for my Drupal site and this does everything that I need it to.

I have made a few alterations of my own to make it fit better within my site, see here:-

www.madtogger.co.uk/page/software

still in the early stages of customizing it as yet but the main thing is that the bare bones works.

Great job Martin I really appreciate it.

Cheers..,

as_you_2007

Good tutorial!!One question,many times we do not really download the file even we click the download link(we cancle for a lot of reasons later),but you added the count....

admin@technofreaky

Great tutorial and it will really help me in starting download section in my website.

I installed this script on my website and I want to add a mouse over effect to show what the file description for the file name is on my server. I saw how MadTogger did it and it looks pretty good, but to minimize space, I am hoping someone could help me with the mouse over effect. Since the name of the file is not actually in the php file, its pretty hard for me to use it. Any suggestions I would appreciate. Great script by the way!

search videos

thanks can i make this for views ?? How many views for a page ?

Hugo Richel

Excellent ! A great tutorial again. Keep up :D

how can i limit it so that the person can only download once for each file?

Brett Widmann

This was an awesome tutorial! Thanks for sharing.

@ Jason
Would love to see a way though to keep track of who is downloading.

Yes
This is exactly my query. Maybe we could throw in together and ask Martin to do it (I am sure there are others who would be interested in it, too)

Very nice tutorial. Thanks a lot for sharing the method with us.

Hi Martin. Thanks for this tutorial. It's help me with my course project in my academy.
Screen of the result:

http://img.sxsoft.ru/v.php?id=1ea48da84222409421a92c8b1e68c5d5.png

Thank you verry much for this tutorial.
I copied the files to my site (non commercial hobby site), editted the layout from the page a bit (title changed, deleted "back to tutorial" link etc)
But at the bottom there is still a link to this page as my thanks to you guys.
Email me if you want the link to the page.

Tomcent

macvajal

I would like to see something, as I can split downloads into folders, I mean in the folder appears configuration.php $ directory = 'files',.

Now I need to go loading files from multiple folders and these can be divided in this way

Its a great tutorial....excellent it works for my web page...Thanks

I have only one question considering the CSS. I would like to make p.ContactUs to stay at the bottom of the page (as it is) without covering the other elements of the page when I resize the page at my browser. I've tried to change the position element but the whole section doesn't stay at the bottom.

Is there any simple solution? Thanks!

Any way to dive into folders. So if there is a folder listed i can click on it and open it then download the files listed. right now it thinks folders are files

Fantastic tutorial! Thanks for this!

But my question would be something like this.

Whenever you click download button, the counter increases by one. But, is there any chance that counter doesn't increase if you download the file from the same IP?

Thanks!
Alex

Or maybe to integrate some feedback form so the download works only if feedback is sent :D

nice work..thank you

I had the problem that it did not work trying to download PDF-files.
Took me some time to figure out, but finally changing the
.htaccess file to

<Files .>
ForceType application/octet-stream
Header set Content-Disposition attachment
</Files>

did the job.

NICO

laurencal

Hi

Thanks for this. I am not getting the table updating although the table seems to be created correctly and the database is being connected to (I assume this because I see an error if I deliberately change the database password to be incorrect).

If I access download.php using the web browser, it says parameter incorrect, I am not sure if this indicates the problem or if that is expected?

Martin, i want to thank you so much! I know almost nothing to PHP, but thanks to your great example i've been able from my iPad to build a site that keeps track of my user downloads. Before i was sharing code without knowing if anybody was using it. This tutorial and code package is so easy to use. Thanks again.

I'm trying to connect your Login slide panel system to this download manager. Having some issues but I'll get there. Your site and the things you put on it are unbelievably amazing! Thanks alot!

Thanks for the code. How do you tell the page to access the .htaccess file inside the files directory? Sory I'm very new to this. Thank you!

Hi, this is just what it needs in most case, great work !

Cornélio José Wiedemann

valeu ai nerdao
tu é o cara dos phpinlovis

awesome tutorial
tks