a_simple_php_sqlite_download_counter
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revisionNext revisionBoth sides next revision | ||
a_simple_php_sqlite_download_counter [2020/06/27 21:00] – waxphilosophic | a_simple_php_sqlite_download_counter [2020/06/27 21:21] – waxphilosophic | ||
---|---|---|---|
Line 172: | Line 172: | ||
===== Counting Your Downloads ===== | ===== Counting Your Downloads ===== | ||
+ | |||
+ | There' | ||
+ | |||
+ | ==== SQLite3 on Command-Line ==== | ||
+ | |||
+ | If you want to query the database directly, simply use the command '' | ||
+ | |||
+ | 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. | ||
+ | |||
+ | < | ||
+ | <?php | ||
+ | $dbfile = " | ||
+ | |||
+ | header(" | ||
+ | if (file_exists($dbfile)) { | ||
+ | $pdo = new PDO(" | ||
+ | if ($pdo) { | ||
+ | echo " | ||
+ | echo " | ||
+ | $sql = " | ||
+ | foreach ($pdo-> | ||
+ | printf(" | ||
+ | } | ||
+ | echo " ----\n"; | ||
+ | $sql = " | ||
+ | printf(" | ||
+ | |||
+ | echo " | ||
+ | echo " | ||
+ | $sql = " | ||
+ | foreach ($pdo-> | ||
+ | printf(" | ||
+ | } | ||
+ | } | ||
+ | else { | ||
+ | echo " | ||
+ | } | ||
+ | } | ||
+ | else { | ||
+ | echo " | ||
+ | } | ||
+ | ?> | ||
+ | </ | ||
+ | |||
+ | ===== 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 '' | ||
+ | |||
+ | 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, " | ||
+ | |||
+ | Even if you're not a SQL expert, you can prune by simply picking a rowid and delete everything before it. " | ||
+ | |||
+ | ====== 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. | ||
+ | |||
a_simple_php_sqlite_download_counter.txt · Last modified: 2020/06/27 21:24 by waxphilosophic