PHP Mysql and jQuery implement file download count statistics

CREATE TABLE IF NOT EXISTS `downloads` (
 `id` int(6) unsigned NOT NULL AUTO_INCREMENT,
 `filename` varchar(50) NOT NULL,
 `savename` varchar(50) NOT NULL,
 `downloads` int(10) unsigned NOT NULL DEFAULT '1',
 PRIMARY KEY (`id`),
 UNIQUE KEY `filename` (`filename`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

You can also directly download the Demo, import the SQL file, and the data is all there.

Download address: Source code for PHP file download count statistics.

Second, HTML part

Add the following HTML structure to the index.html page body, where ul.filelist is used to display the file list. Now it has no content, and jQuery will be used to read the file list asynchronously. You also need to load the jQuery library file in html.

Copy code

Three, CSS part In order to allow the demo to better display the page effect, CSS is used to modify the page. The following code mainly sets the file list display effect. Of course, in the actual project, the corresponding style can be set as needed.

#demo{width:728px;margin:50px auto;padding:10px;border:1px solid #ddd;background-color:#eee;}
ul.filelist li{background:url("img/ bg_gradient.gif") repeat-x center bottom #F5F5F5;
border:1px solid #ddd;border-top-color:#fff;list-style:none;position:relative;}
ul.filelist li.load{background :url("img/ajax_load.gif") no-repeat; padding-left:20px;
border:none; position:relative; left:150px; top:30px; width:200px}
ul.filelist li a{display :block;padding:8px;}
ul.filelist li a:hover .download{display:block;}
span.download{background-color:#64b126;border:1px solid #4e9416;color:white;
display: none;font-size:12px;padding:2px 4px;position:absolute;right:8px;
text-decoration:none;text-shadow:0 0 1px #315d0d;top:6px;
-moz-border-radius: 3px;-webkit-border-radius:3px;border-radius:3px;}
span.downcount{color:#999;padding:5px;position:absolute; margin-left:10px;text-decoration:none;}

Four, PHP part

For better understanding, it is divided into two PHP files. One is filelist.php, which is used to read the data in the mysql data table and output the data in JSON format for calling the front-end index.html page. The other is download.php, used to respond to download actions , update the number of downloads of the corresponding file, and complete the download through the browser. In fact, there is also a database connection file conn.php, which has been packaged in the download compressed package. Click here to download. filelist.php reads the downloads table and outputs the data in JSON format through json_encode(), which is prepared for the following Ajax asynchronous operation.

require 'conn.php'; //Connect to the database
$result = mysql_query("SELECT * FROM downloads");
if(mysql_num_rows($result)){
 while($ row=mysql_fetch_assoc($result)){
 $data[] = array(
 'id' => $row['id'],
 'file' => $row['filename'],
 'downloads '=> $row['downloads']
 );
 }
 echo json_encode($data);
}

download.php passes parameters according to the url, queries to get the corresponding data, detects whether the file to be downloaded exists, and if it exists, updates the number of downloads of the corresponding data by +1, and uses header() to implement the download function. It is worth mentioning that using the header() function , force the file to be downloaded, and you can set the file name to be saved locally after downloading.

Under normal circumstances, the uploaded file will be renamed and saved to the server through the background upload program. Commonly used files are named after date and time. One of the benefits of this is that it avoids duplication of file names and garbled Chinese names. For files downloaded locally, you can use header("Content-Disposition: attachment; filename=" .$filename) to set the file name to an easily identifiable file name.

require('conn.php'); //Connect to the database
$id = (int)$_GET['id'];

if(!isset($id) | | $id==0) die('Wrong parameter!');
$query = mysql_query("select * from downloads where id='$id'");
$row = mysql_fetch_array($query);
if( !$row) exit;
$filename = iconv('UTF-8','GBK',$row['filename']);//Please pay attention to the conversion encoding of the Chinese name
$savename = $row['savename']; //Actual save name on the server
$myfile = 'file/'.$savename;
if(file_exists($myfile)){//If the file exists
 //Update download times
 mysql_query("update downloads set downloads =downloads+1 where id='$id'");
 //Download file
 $file = @ fopen($myfile, "r");
 header("Content-type: application/octet-stream");
 header("Content-Disposition: attachment; filename=" .$filename );
 while (!feof($file)) {
 echo fread($file, 50000);
 }
 fclose($file);
 exit ;
}else{
 echo 'The file does not exist! ';
}

5, jQuery part The jQuery on the front-end page mainly completes two tasks. One is to read the file list asynchronously through Ajax and display it. The other is to respond to the user's click event and increase the number of downloads of the corresponding file by 1.

Code:

$(function(){
 $.ajax({ //Asynchronous request
 type: 'GET',
 url: 'filelist.php',
 dataType: 'json',
 cache: false,
 beforeSend: function(){
 $(".filelist").html("
Loading...
 } ,
 success: function(json){
 if(json){
 var li = '';
 $.each(json,function(index,array){
 li = li + '
'+array['file']+
Click to download
 });
 $( ".filelist").html(li);
 }
 }
 });
 $('ul.filelist a').live('click',function(){
 var count = $('.downcount' ,this);
 count.text( parseInt(count.text())+1); //Number of downloads+1
 });
});

First, the page is loaded Finally, send an Ajax request in the form of GET to the background filelist.php through $.ajax(). When filelist.php responds successfully, it receives the returned json data, traverses the json data object through $.each(), and constructs an html string. , And add the final string to ul.filelist to form the file list in the demo.

Then, when the file is clicked to download, the click event of the dynamically added list element is responded to through live(), and the number of downloads is accumulated.

Finally, in fact, after reading this article, this is a commonly used Ajax case. Of course, there is also the knowledge of PHP combined with mysql to achieve downloading. I hope it will be helpful to everyone.