RSS Feed (xml)MySQL Download Tracking
One application of this would be to track information about file downloads. Here we're going to track the number of downloads and when the last download occurred. We're going to store the information in a MySQL database. Below is the structure of the table.CREATE TABLE filestats (
fileid INT NOT NULL auto_increment,
filename TEXT,
downloads INT NOT NULL,
lastdownload DATETIME,
primary key (fileid)
);
The fileid is just an auto incrementing number used to keep track of the database. Filename will be the field we search with. Downloads and lastdownload are the fields used for keeping statistics.
Insert the following code after the if (isset($_REQUEST["file"])) { statement. This code will connect to the MySQL database and update the file stats. PHP has a strong built in function library for MySQL.
$db=mysql_connect($mysql_host,$mysql_login,$mysql_passwd) or die(mysql_error());
mysql_select_db($mysql_database);
$query=mysql_query("select * from filestats where filename='".basename($file)."' LIMIT 0,1") or die (mysql_error());
$fileexist=@mysql_num_rows($query));
$now=date("Y-m-d G:i:s");
if ($fileexist>0) {
$updatequery=mysql_query("update filestats set downloads=downloads+1,
lastdownload='$now' where filename='".basename($file)."'") or die (mysql_error());
} else {
$addquery=mysql_query("insert into filestats (filename,downloads,lastdownload)
values ('".basename($file)."','1','$now')") or die (mysql_error());
}
?> The date function is used to set the current date and time. Here it will be displayed in the YYYY-MM-DD HH:MM:SS which is the format of the MySQL datetime format. In this segment we connected to the MySQL database and updated the download statistics. First mysql_connect connected to the database passing the connection parameters in the order of host, login, and password. Then we selected which MySQL database we wanted to use with the mysql_select_db statement. Once we have the connection completed we check to see if the table holds a record for the file. The first mysql_query searches the database for a record with the filename of the file being downloaded. Mysql_num_rows determines the number of results returned from the query. Here we preceded the mysql_num_rows with a @ to prevent it from returning errors if the query results were empty.
If there was a result from the query $fileexist will be greater than 0 so we update the database. To do this we use a MySQL update query. If there wasn't a result returned an insert query will be used instead of an update.
