Differences

This shows you the differences between two versions of the page.

--- a_simple_php_sqlite_download_counter [2020/06/27 20:45] – waxphilosophic
+++ a_simple_php_sqlite_download_counter [2020/06/27 21:24] (current) – [Summary] waxphilosophic
@@ Line 3: / Line 3: @@
 ===== Summary =====
-If you host downloadable 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.
+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 =====
@@ Line 130: / Line 130: @@
 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.