2010-06-29 13:57:18
Podcast Tracking: Improved
Last week I showed you a quick and dirty way to make a podcast downloader script that will also track the number of downloads. This week I have a new, improved version of that script thanks to some comments I received on the site and via Twitter. For those of you who think that you're rock star programmers and can get things right the first time, you're not and you can't. The more eyes you have on your code, the better. That's why I'm not ashamed to say that my first version of this script was lacking in a few areas. Here are the few improvements that I've made:- Database connection is called only if a valid file is requested
- All MySQL errors are suppressed
- Script still delivers the podcast if the database is down
- One SQL statement replaces three and multiple lines of PHP
<?php
function clean($s)
{
return preg_replace("/[^A-Za-z0-9\-]/", "", $s);
}
$file = clean($_GET['f']);
if (file_exists("files/" . $file . ".mp3"))
{
$CONN = @mysql_connect("server","username","password");
if (!($CONN==FALSE))
{
@mysql_select_db("db");
$SQL = "insert into podcasts (name,downloads)
values ('" . $file . "',1)
on duplicate key update downloads=downloads+1";
$RESULT = @mysql_query($SQL);
mysql_close($CONN);
}
header("Cache-Control: no-cache, must-revalidate"); // HTTP/1.1
header("Expires: Sat, 26 Jul 1997 05:00:00 GMT"); // Date in the past
header('Content-type: audio/mpeg');
header('Content-Disposition: attachment; filename="' . $file . '.mp3"');
readfile("files/" . $file . ".mp3");
}
else
{
header("Location: index.php");
}
?>
Let's look at these four items one at a time.
1. In version 1, the database connection was one of the first things called. That meant that even if the client requested a file that didn't actually exist, and no database update would be made, the database was still opened and then immediately closed. That wastes time and resources. Now, the database connection is contained inside of the file_exists IF block so that it is only called if the client requests a real file that we will need to track.
2. Another problem was in the realm of MySQL errors. Should any MySQL functions fail (mysql_connect, mysql_select_db, or mysql_query), an error would be printed to the screen and the following header() calls would break thus rendering the file download null. That's not cool. Now, by putting the @ symbol before each MySQL call, errors are suppressed.
3. Keeping in the MySQL error realm, let's assume that instead of a simple error we have a catastrophic failure, such as the entire database being unreachable. Previously, the script would have died (this problem was mostly taken care of by #2). Now, however, if the database is unreachable all further MySQL calls aren't even processed. The script just moves on to deliver the file to the client. Of course, we lose our tracking in this case, however the most important thing is that the client gets the file no matter what.
4. Thanks to Jonathan Angliss for this idea, MySQL has a type of command that does all of the legwork that I was doing with three commands previously. By using "ON DUPLICATE KEY" in the INSERT statement, MySQL can do one thing if a row doesn't exist and something else if it does. Built-in conditional statements... awesome! The only change that I needed to make was to modify my current podcasts table and add the "UNIQUE" bit to the "name" column. Now when the script tries to insert a row with the name of a podcast that is already there, it will just increase the counter. If the podcast isn't in the table yet, it makes the row like normal. This saves PHP processing time and MySQL processing time.
Now, in approx. 856 bytes, we have a script that is faster and handles errors better than the first version.
Back