如何使用php mysqli创建下载文件计数

Question

I have the filenames of my files in my database which are mp3 songs, I need help in generating a php mysql code which help me count a number of times a each file has been downloaded. Thank You.

if(isset($_GET['id'])){}
$songid = $_GET['id'];
$sql = 'SELECT filename, album_art, song, song_id, artist FROM homemp3 WHERE song_id ='.$songid;
$result = $conn->query($sql);
$row = mysqli_fetch_array($result);

            <img src="./images/<?= $mainImage; ?>" alt="PowerBeatz" width="300" height="300" />
            <div class="media-body">
                <h5 class="mt-0"><span class="title"><?php echo $row['song']; ?></span></h5>
                 <?php echo $row['artist']; ?>
                <div class="play">
                    <a href="./homemp3/<?= $row['filename']; ?>" class="btn btn-primary"><i class="fa fa-download"></i></a>
                </div>
                <div>
                <p>Total Downloads:</p>
                </div>

Answer 1

I would recommend putting a download counter on the table and every time a user clicks the link to download the mp3 then update the record with counter+=1. Then you can display the counter for each mp3 based on the value of that column.

I would create a download.php page and pass either the file name or better yet and id for the file. Then hen the user clicks the link to download the file, then update the record counter and then set headers to force download the file. For example.

In download file...

Find record by name or id, update counter and then force download...

$mp3 ='path_to/mp3_file.mp3';
if(file_exists($mp3)) {
  header('Content-Type: audio/mpeg');
  header('Content-Disposition: attachment; filename="mp3_file.mp3"');
  header('Content-length: '. filesize($mp3));
  header('Cache-Control: no-cache');
  header('Content-Transfer-Encoding: chunked'); 
  readfile($mp3);
  exit;
}