Differences

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

--- a_simple_php_sqlite_download_counter [2020/06/27 21:00] – 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 172: / Line 172: @@
 ===== 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.