My monitored file download script

[Dylan Leblanc]

I wanted to keep stats on some files I have available for download, so I wrote this simple
script to do so. It keeps track of who downloads what files, and how many times.

Users are identified by $memberID

The FileDownload table stores the access information, and contains these columns:

filename - the name of the file downloaded, PK

memberID - the user who downloaded the file, PK

count - number of times the file is downloaded by the user, default 0

PHP Code:

<?

// setup enviroment

require_once('global.php');


// valid file types and their MIME content-types

$contenttypes = array(
    'png' => 'image/png',
    'pdf' => 'application/pdf'
);


// the file for download is passed as the query string

$filename = basename($_SERVER['QUERY_STRING']);

$file = "/usr/home/www/download/{$filename}";

// if file does not exist, or not valid content-type, then 404

if (($filename == '') || (!in_array($fileextension, array_keys($contenttypes))) || (!file_exists($file))) {
    header("HTTP/1.0 404 Not Found");
    exit;
}


// file specified is valid

// see if user has previously downloaded this file

$row = mysql_fetch_object(mysql_query("SELECT count FROM FileDownload WHERE filename = '{$filename}' AND memberID = {$memberID}"));

// if they have then increase count, if not then count = 1

if ($row) {
    $count = $row->count + 1;
} else {
    $count = 1;
}

// do a REPLCE to store new count value

mysql_query("REPLACE FileDownload (filename, memberID, count) VALUES ('{$filename}', {$memberID}, {$count})");


$fileextension = substr($filename, -3);

$contenttype = $contenttypes[$fileextension];

$filesize = filesize($file);

// send HTTP headers to present user with download and save dialog

header("Content-type: {$contenttype}");
header("Content-Disposition: attachment; filename=\"{$filename}\"");
header("Content-Length: {$filesize}");

// now spit out the file

$filepointer = fopen($file, 'r');

print fread($filepointer, $filesize);

fclose($filepointer);

exit;

?>

The one thing that I wish could be cleaned up is the SELECT and REPLACE queries, and accompanying code to increase $count,
but I see no other way to get the current count from the database otherwise. At first I tried:
REPLACE FileDownload (filename, memberID, count) VALUES ('{$filename}', {$memberID}, count + 1)

but the "count + 1" part does not work because REPLACE actually deletes the row and then inserts it, so the count value is lost.

[Ökii]

I think you can do that within an UPDATE call with MySQL

$update_row = mysql_query("UPDATE `table` SET fieldname=fieldname+1 WHERE .........");

am sure that worked for me a while back - might even have been SET fieldname++

any chance you could confirm my suspicions? ie tell us if that works?

[Dylan Leblanc]

I use REPLACE so that if the necessary row does not already exist it will be created. If the user has never downloaded a file, then there would be no row in the database to update.

Although good suggestion. I could do UPDATE first, and then if it returns 0 rows affected I could then INSERT, acheiving the same result I already have but with the chance of fewer queries (if row already exists).

[sitami]

so you know Okii its fieldname=fieldname+1