a_simple_php_sqlite_download_counter []

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== A Simple PHP/SQLite Download Counter ======

===== Summary =====

If you host downloadable web content on SDF, you may want to get an idea of how many people have downloaded things and which are the most popular. In this tutorial, I will focus on an example of tracking PDF formatted ebooks. After completing this tutorial, you should be able to modify things to suit your needs, even if your downloads are not ebooks. You will need to have PHP access to do this, so check your membership level before you get started.

===== The Download Script =====

For the impatient and the PHP gurus in the audience, I will show the entire script first. After that, I'll pick it apart and explain each section.

<code>
<?php

  // Set the location holding the download content.
  $content_dir = basename(__FILE__) . "/download";

  // The query string passed indicates the filename.
  if (isset($_SERVER['QUERY_STRING'])) {
    $file = rawurldecode($_SERVER['QUERY_STRING']);
    $path = $content_dir . DIRECTORY_SEPARATOR . $file;

    // Deliver the file if it exists, otherwise error.
    if (file_exists($path)) {
      header("Content-type: application/pdf");
      header("Cache-Control: no-store, no-cache");
      header("Content-Disposition: inline; filename=\"$file\"");
      echo file_get_contents($path);

      // Record the download in the hit count database.
      $pdo = new PDO("sqlite:download.sl3");
      if ($pdo) {
        $create = "CREATE TABLE IF NOT EXISTS tally (datetime VARCHAR(32), filename VARCHAR(256))";
        $pdo->exec($create);
        $insert = "INSERT INTO tally (datetime, filename) VALUES (datetime('now'), :filename)";
        $prepared_sql = $pdo->prepare($insert);
        $prepared_sql->bindValue("filename", $file);
        $prepared_sql->execute();
      }
    }
    else {
      http_response_code(404);
      header("Content-type: text/plain");
      header("Cache-Control: no-store, no-cache");
      echo "404: Not Found";
    }
  }

?>
</code>

===== Explanation of Parts =====

For a better understanding of how this works, I'll dissect the parts of the script one by one and explain each bit.

==== Where to Find the Files ====

First, there is the ''$content_dir'' variable.

<code>
  // Set the location holding the download content.
  $content_dir = basename(__FILE__) . "/download";
</code>

This needs to be set to where the downloadable files are kept. The way things are set up by default assumes you have a directory hierarchy that looks like this:

<file>
html/
|-- download.php
|-- download/
    |-- ebook.pdf
</file>

In other words, your downloadable content is all kept in a directory called download and it is a subdirectory residing next to the download.php script we're putting together.

<code>$content_dir = basename(__FILE__) . "/download";</code>

__FILE__ in PHP means the filesystem path to the PHP script being executed. This is not the URL path, but the filesystem path. So it will not be ''http://login.sdf.org/download''. It will look more like ''/sdf/arpa/tz/l/login/html'' and using the dot concatenation operator will tack ''/download'' to the end of it.

So now you know where to store you content and what to change if it's located somewhere else.

==== Knowing What Was Requested ====

Next, there is the section that decodes the query string.

<code>
  // The query string passed indicates the filename.
  if (isset($_SERVER['QUERY_STRING'])) {
    $file = rawurldecode($_SERVER['QUERY_STRING']);
    $path = $content_dir . DIRECTORY_SEPARATOR . $file;
</code>

Inside your HTML, you might have a hyperlink that looks like this: ''href="download/ebook.pdf''. This is a simple example of fetching the file ''ebook.pdf'' from the ''download'' sub directory.

To use the download counter, the link needs to look like this: ''href="download.php?ebook.pdf''. It's a minor change and easy to pull off en masse with a little RegEx.

With the new format, each hyperlink will call the download.php script and pass the name of the desired file as a query string. The PHP variable ''$_SERVER['QUERY_STRING']'' is where we find this file name. It's just the name, not the full path, so we need to concatenate it with the ''$content_dir'' for it to be useful.

You may have also noticed the ''rawurldecode()'' function around the ''$_SERVER['QUERY_STRING']''. If you have experience with HTML and query strings, you might know there are certain rules that must be followed when passing information as part of a URL. One of those rules is no spaces allowed.

If you have an ebook called ''My Great American Novel.pdf'', it's not going to work, because there are spaces. You'll need to make the file name in your hyperlink look like this: ''My%20Great%20American%20Novel.pdf''

The %20 is a URL safe encoding of the space character. Guess what ''rawurldecode()'' does. If you said, 'change the %20s back to spaces' you are a winner.

==== Delivering the file ====

Now that we know what file the user wants, it's time to send it to their browser. That's what the next section of code is for.

<code>
    // Deliver the file if it exists, otherwise error.
    if (file_exists($path)) {
      header("Content-type: application/pdf");
      header("Cache-Control: no-store, no-cache");
      header("Content-Disposition: inline; filename=\"$file\"");
      echo file_get_contents($path);
</code>

The first thing it will do is to check to make sure the file actually exists. If there's something wrong with the hyperlink, or the user types it in manually and makes a mistake, we need a way to tell them. For now though, let's pretend everything is happening as it should.

The script delivers three HTTP headers to let the user's browser know what's coming.

The first is the content type (aka MIME type.) I mentioned that I use this script for PDF ebooks, so my content type is hard coded to ''application/pdf''. Obviously, you'll want to change it if you are delivering other types of files.

If you are delivering more than one type of file, you could use an associative array to choose the content-type based on file extension. This is beyond the scope of the tutorial.

The next HTTP header tells the browser and any proxy servers in between, not to cache the content.

Finally, we have some specifics about the actual file.

One being that it should be displayed inline. (The alternative is having the browser prompt to save.) I use inline because most browsers have a PDF reader built in and it's less jarring to the user to have everything appear in the same window.

The other is the filename. This is a courtesy to the user and will pre-fill any save dialog with the filename if they choose to save it.

==== Recording the Download ====

The next bit of code is where we get down to the business of making a tally in the database about what was downloaded and when.

<code>
      // Record the download in the hit count database.
      $pdo = new PDO("sqlite:download.sl3");
      if ($pdo) {
        $create = "CREATE TABLE IF NOT EXISTS tally (datetime VARCHAR(32), filename VARCHAR(256))";
        $pdo->exec($create);
        $insert = "INSERT INTO tally (datetime, filename) VALUES (datetime('now'), :filename)";
        $prepared_sql = $pdo->prepare($insert);
        $prepared_sql->bindValue("filename", $file);
        $prepared_sql->execute();
      }
</code>

The "hit counter" as they used to be called is a simple SQLite database that we manipulate by way of PHP's PDO class.

The first step is to create a PDO object that we can use. A nice feature of this is that the backing file ''download.sl3'' will be automatically created. There's no need to do any set up.

This is further enhanced by the first SQL statement. ''CREATE TABLE IF NOT EXISTS tally ...'' will create the table if it's not already present. This is for ease of use. I'm also making the assumption that it's not a high traffic site, so a little bit of extra overhead for the sake of convenience is a good thing in my mind.

Finally, there is a SQL insert using a prepared statement. It will insert a new row with a date-time stamp and the name of the file that was downloaded. This lets you see what is downloaded and when it was downloaded.

==== Handling Errors ====

There's not a lot of error checking in the script, but it does handle what would probably be the most common mistake, that being a case of the wrong file name.

<code>
    else {
      http_response_code(404);
      header("Content-type: text/plain");
      header("Cache-Control: no-store, no-cache");
      echo "404: Not Found";
    }
</code>

This last little bit is associated with the earlier ''if files_exists ...'' bit. It uses two of the HTTP headers we've seen before, but it also sends an HTTP response code, the dreaded 404. The last line sends the error in text to the user. This can, of course, be customized.

===== Counting Your Downloads =====

There's not much reason to track downloads from your site unless you're planning to look at that information every so often. This can be done in a couple different ways.

==== SQLite3 on Command-Line ====

If you want to query the database directly, simply use the command ''sqlite3 download.sl3'' and enter the query, ''SELECT filename, datetime FROM tally ORDER BY datetime DESC;''

This will give you everything downloaded in date order.

==== A PHP/SQLite Script ====

If you want to get a little fancier, the following PHP script will do the query for you and show not only downloads by date, but also give an indication of the most popular files based on total number downloaded. I won't go into detail on how it works. If you understand the download.php script, this one will be easy.

<code>
<?php
  $dbfile = "download.sl3";

  header("Content-Type: text/plain; charset=utf-8");
  if (file_exists($dbfile)) {
    $pdo = new PDO("sqlite:$dbfile");
    if ($pdo) {
      echo "Downloads per File\n";
      echo "------------------------------------------------------------------\n";
      $sql = "SELECT filename, COUNT(filename) AS count FROM tally GROUP BY filename ORDER BY count DESC, filename";
      foreach ($pdo->query($sql) as $row) {
        printf("%5s %s\n", $row['count'], $row['filename']);
      }
      echo " ----\n";
      $sql = "SELECT COUNT(*) FROM tally";
      printf("%5s Total\n\n", $pdo->query($sql)->fetchColumn());

      echo "Recent Downloads By Date (UTC)\n";
      echo "------------------------------------------------------------------\n";
      $sql = "SELECT filename, datetime FROM tally ORDER BY datetime DESC LIMIT 50";
      foreach ($pdo->query($sql) as $row) {
        printf("%s  %s\n", $row['datetime'], $row['filename']);
      }
    }
    else {
      echo "Database connection failed.\n";
    }
  }
  else {
    echo "Nothing yet.\n";
  }
?>
</code>

===== Database Management =====

The more downloads there are from your site, the more the download.sl3 file will grow. My assumptions have always been for a low volume of traffic and we're not storing a ton of information. My experience with tracking my own downloads is that I only need to prune one or two times a year. Most of that is removing ebook titles that I have removed.

There is no fancy PHP page for database pruning. It can be done with the ''sqlite3'' command-line tool and a little research on SQL where clauses.

SQLite also has the advantage that it will assign each row an index number. This is normally hidden from queries, but can be seen with the statement, "SELECT rowid,* FROM tally;"

Even if you're not a SQL expert, you can prune by simply picking a rowid and delete everything before it. "DELETE FROM tally WHERE rowid<100" will purge everything from 1 to 99. You get the idea.

===== Go Forth and Conquer =====

Now that you know what people are downloading from your site, you'll have better insight into what's popular and what's not.

It's your call, but I like to let people know I'm tracking downloads. Even though I'm not gathering any personal information with it, I like to be transparent about it. I put it in a privacy statement on my site and even offer a link to the PHP query that shows what's been downloaded.