File Download Counter with PHP & MySQL

by Resalat Haque on 3:16 AM in CSS, mysql, PHP

Hello there! Today we are going to create a simple file download tracker with php and mysql.

To track downloads, we need to upload our file to uploads directory and add files metadata (like file name and path) to database table. When a user will try to download a file by file id the original file path will retrieve from database table then php will update download count value and file will be forced for download.

Live Demo Download Source

Step 1 - Creating MySQL Table

At first we need to create a mysql table called files. The table will hold three column with an auto increment field. The name file will hold file name, path will hold file path in server and count will hold the total number of downloads.

CREATE TABLE `files` (
  `id` int(8) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  `path` varchar(30) NOT NULL,
  `count` int(10) DEFAULT '0',
  PRIMARY KEY (`id`)
);

Step 2 - File Upload

The upload.php file will allow us to upload file to server. It holds some basic file download handler functionality. When we will upload file it will move uploaded file to uploads directory as well as it will insert file metadata to mysql table. I have used a password verifier to prevent unauthorized acction.

<?php
include 'function.php';
dbConnect();

$password = 'mypass'; // password
$max_upload_size = 1024*1024*2; // max file size

if ($_SERVER['REQUEST_METHOD'] === 'POST')
{
  if (!empty($_FILES['file']) and !empty($_POST['password']))
  {  
    // validate password
    if ($_POST['password'] === $password)
    {
      // validate file size
      if ($_FILES['file']['size'] < $max_upload_size) {

        // generate upload path with unique file name
        $path = 'uploads/' . uniqid() . '.';
        $path .= pathinfo($_FILES['file']['name'], PATHINFO_EXTENSION);
        $name = $_FILES['file']['name'];

        // move uploaded file to upload directory
        if ( move_uploaded_file($_FILES['file']['tmp_name'], $path) )
        {
          // insert new file to mysql db
          mysql_query("INSERT INTO files (`name`, `path`)
                VALUES('$name', '$path')");

          // set success message
          $message = 'File Uploaded!';

        } else {
          $message = 'Upload fail';
        }
      } else {
        $message = 'File is too large';
      }
    } else {
      $message = 'Wrong Password!';
    }
  } else {
    $message = 'File not set';
  }
}

?>
<!doctype html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <title>File download</title>
  <link rel="stylesheet" type="text/css" href="style.css">
</head>
<body>
  <div class="container">
    <h1>Upload File</h1>

    <!--messages-->
    <?php if(isset($message)): ?>
      <p class="alert"><?php echo $message; ?></p>
    <?php endif ?>

    <!--file upload form-->
    <form action="" method="post" enctype="multipart/form-data">
      <input type="file" name="file">
      <input type="password" name="password" placeholder="Password">
      <input type="submit" name="upload" value="Upload">
    </form>

  </div>
</body>
</html>

Step 3 - File Download

The download.php file holds file download functionalities. This file retrieves file from database by it's id passed by file_id parameter. If it found file in database and file is exist in file system it updates counter value and forces file to download. We also need to off error reporting to avoid passing extra data and set php execution time to infinity to allow large file download by slow connection.

<?php
// off error reporting
error_reporting(0);
// set unlimited php execution time
set_time_limit(0);

include 'function.php';
// connect to db
dbConnect();

// read file id from url
$id = (int) isset($_GET['file_id']) ? $_GET['file_id'] : 0;
// retrieve file from db with id
$query = mysql_query("SELECT * FROM files WHERE id = '$id'");

if ($row = mysql_fetch_array($query))
{
  if (file_exists($row['path']))
  {
    // increase counter value
    $count = ++$row['count'];
    // update count column
    mysql_query("UPDATE files SET count = '$count' WHERE id = {$row['id']} ");

    // set file download headers
    header('Content-Description: File Transfer');
    header('Content-Type: application/octet-stream');
    header('Content-Disposition: attachment; filename="'. $row['name'] .'"');
    header('Content-Transfer-Encoding: binary');
    header('Connection: Keep-Alive');
    header('Expires: 0');
    header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
    header('Pragma: public');
    header('Content-Length: ' . filesize($row['path']));

    // read file
    readfile($row['path']);
  }
  else {
    echo 'File not found!';
  }
}
else {
  echo 'Bad Request!';
}

?>