PHP & MySQL File Download Counter

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.

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.

Download Counter Interface

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.

Hover State With CSS

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.

Structure of the download_manager Table

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?

Join our newsletter and get our PSDs!19,630 people learn about HTML5, JS and more. Join them!

by Martin Angelov

Martin is a web developer with an eye for design from Bulgaria. He founded Tutorialzine in 2009 and it still is his favorite side project.

42 Comments

  1. Michael says:

    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?

  2. Martin Angelov says:

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

    1. tonev says:

      um Martin Angelov you are from bulgaria?

  3. Djb says:

    very good tutorial

  4. gurza says:

    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?

  5. Its very nice. Good work

  6. Taylor Hunt says:

    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.

  7. Martin Angelov says:

    @ 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.

  8. gurza says:

    @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.

  9. very nice to use in a selfmade cms system!

  10. Q says:

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

  11. Fernando says:

    Great work, amazing tutorial thank you

  12. denbagus says:

    nice tutorial .. thank you

  13. Artur says:

    Ótimo trabalho.

    Parabéns tutoriazine.

  14. Jason says:

    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

  15. Nikola M. says:

    Great tutorial, thank you

  16. MadTogger says:

    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..,

  17. as_you_2007 says:

    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....

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

  19. mrteetd says:

    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!

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

  21. Hugo Richel says:

    Excellent ! A great tutorial again. Keep up :D

  22. kerron says:

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

  23. This was an awesome tutorial! Thanks for sharing.

  24. Heather says:

    @ 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)

  25. Nat says:

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

  26. Thomas says:

    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

  27. Tomcent says:

    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

  28. macvajal says:

    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

  29. BRIONES says:

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

  30. Alx says:

    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!

  31. alex says:

    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

  32. Alex says:

    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

    1. Alex says:

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

  33. sunny says:

    nice work..thank you

  34. NICO says:

    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

  35. laurencal says:

    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?

  36. Jmv38 says:

    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.

  37. Auguste says:

    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!

  38. Aleš says:

    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!

  39. uriel says:

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

  40. valeu ai nerdao
    tu é o cara dos phpinlovis

    awesome tutorial
    tks

Add Comment

Add a Reply

HTML is escaped automatically. Surround code blocks with <pre></pre> for readability.
Perks:   **bold**   __italics__   [some text](http://example.com) for links