How to create a file download that will be remove from the database once downloaded

Question

How can I remove the file from the user if the file was successfully downloaded? I have here a download function but I don't know how to do a one time download.

HTML codes with PHP:

<?php 
                    $result=mysql_query("SELECT * FROM school_management");
                    while($row=mysql_fetch_array($result))
                    {
                ?>
                    <div class="col-xs-6 col-md-3" style="padding-bottom: 10px;">
                        <div class="thumbnail">
                          <img src="images/ebooks/<?php echo $row['PICTURE'];?>" alt="...">
                        </div>
                        <p><center><a href="files/file.php?file=<?php echo $row['FILE_NAME'];?>" class="btn btn-xs btn-inverse" role="button" style="background-color: #2980b9;">Download</a></center></p>
                    </div>
                <?php
                    }
                ?>

file.php

<?php

include('config.php');

$file=$_GET['file'];
echo $file;
header("Content-disposition: attachment; filename=$file");
header("Content-type: application/pdf");
header('Content-Description: File Transfer');
header('Pragma: public');
header('Expires: 0');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
readfile("$file");

?>

My file.php can only download the file with letters in title but if the file has symbols or numbers in the title, I can't download the file. So I changed all the pdf titles for me to be able to download it. Any suggestions to make my file.php download correctly and if the user downloads the file successfully, the file will be deleted from the user's database.

Database Name: caledte1_ebook

Table Name: school_management

Table Columns: ID_NUMBER, EBOOK_FILENAME, PICTURE

Working on something for you. But changing it completely to mysqli() as out of principle (and your safety!!!) I refuse to work with deprecated code. — icecub, Sep 24 '14 at 02:16
@icecub Okay I'll change that to mysqli(). How can I know if the file is downloaded successfully? When I get that function, I can work on that delete query. — Ejardy, Sep 24 '14 at 02:22
You can't. There are some tricks to try and detect a failed download. See: http://stackoverflow.com/questions/8771226/determining-successful-download-using-php-readfile But in the end it's unreliable. — icecub, Sep 24 '14 at 02:31

score 0 · Answer 1 · answered Sep 24 '14 at 01:49

0

Just make a database call in file.php. You probably also want to validate that the file exists before delivering it.

$filename = mysql_real_escape_string($_GET['file']);
$result = mysql_query("SELECT id_number FROM school_management WHERE ebook_filename = '$filename'");
if(mysql_num_rows($result) == 0) {
    // Perform error handling
}
mysql_query("DELETE FROM school_management WHERE ebook_filename = '$filename'");

answered Sep 24 '14 at 01:49

UTAlan

132
1
9

Yes the file exists from the database. How can I delete the filename in the database once it is downloaded? Where will I put that delete query? I'm wondering if the download process stopped or sometimes the PC suddenly disconnected from the internet, the file must be deleted or it must stay and wait to to be deleted when the download is complete? – Ejardy Sep 24 '14 at 02:01
I would retrieve the file, delete it from the database, then deliver it to the browser. This does have the downside that if the download is interrupted, the file is lost, but I don't know of any way to automatically detect a successful download prior to running another query. – UTAlan Oct 09 '14 at 14:45

icecub · Answer 2 · 2014-09-24T03:02:14.037

New PHP / HTML File:

<?php

$dbhost = ""; //Enter db host here
$dbuser = ""; //Enter db user here
$dbpass = ""; //Enter db pass here
$dbname = ""; //Enter db name here

$db = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

/* check connection */
if ($db->connect_errno) {
    printf("Connect failed: %s\n", $db->connect_error);
    exit();
}

$sql = "SELECT * FROM school_management";

if ($result = $db->query($sql)){
    while ($row = $result->fetch_assoc()){ ?>
        <div class="col-xs-6 col-md-3" style="padding-bottom: 10px;">
            <div class="thumbnail">
                <img src="images/ebooks/<?php echo $row['PICTURE'];?>" alt="...">
            </div>
            <p>
                <center>
                    <a href="files/file.php?file=<?php echo $row['FILE_NAME']; ?>" class="btn btn-xs btn-inverse" role="button" style="background-color: #2980b9;">Download</a>
                </center>
            </p>
        </div>
    <? }

    $result->free();

} else {
    printf("Error: %s\n", $db->error);
}

$db->close();

?>

File.php:

<?php

$dbhost = ""; //Enter db host here
$dbuser = ""; //Enter db user here
$dbpass = ""; //Enter db pass here
$dbname = ""; //Enter db name here

$db = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

/* check connection */
if ($db->connect_errno) {
    printf("Connect failed: %s\n", $db->connect_error);
    exit();
}

$file = $_GET['file'];

if (file_exists($file)) {
    header('Content-Description: File Transfer');
    header('Content-Type: application/pdf');
    header('Content-Disposition: attachment; filename='.basename($file));
    header('Expires: 0');
    header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
    header('Pragma: public');
    header('Content-Length: ' . filesize($file));
    readfile($file);

    $file = $db->real_escape_string($file);

    $sql = "DELETE FROM school_management WHERE ebook_filename = '". $file ."'";

    if ($succes = $db->query($sql)){
        //Succesfull deleted code handle here
    } else {
        printf("Error: %s\n", $db->error);
    }

    $db->close();
}

?>

Ofcourse you could take the mysqli() connection parts out of these and use a seperate file for that.

How to create a file download that will be remove from the database once downloaded

2 Answers2