how to download blob based file from MySQL database in PHP?

Question

How can i make a PHP script that will allow me to download a file from a database in MySQL. I have the following table named files where the uploaded file is saved in a BLOB based field.

+-------+----------+----------+------------+-------------------+--------+
|fileID | fileName | fileType | fileSize   |fileData           | userID |
+-------+----------+----------+------------+-------------------+--------+
| 1     | file1    | JPEG     | 211258     |[BLOB - 206.3 KiB] | 1      |
| 2     | file2    | PNG      | 211258     |[BLOB - 201.3 KiB] | 1      |
+-------+----------+----------+------------+-------------------+--------+

I cannot figure out a way to call the file to download and every time i try, i get the binary data of the file, such as random numbers and symbols.

I have tried this query where parameters are passed through (fileID, fileName, fileType) to a download.php page:

$id = mysqli_real_escape_string($link, $_GET['fileID']);
$name = mysqli_real_escape_string($link, $_GET['fileName']);
$type = mysqli_real_escape_string($link, $_GET['fileType']);

$SELECT = "SELECT * FROM files WHERE fileID = $id AND fileName = $name ";
$result = mysqli_query($SELECT, $link);
$result = mysqli_fetch_assoc($result);

header("Content-type: $type");  
echo $result['fileData'];

But this leads to a blank page with no output.

How can I download, for example, file1 as a JPEG file to my hard drive?

Solution

This is the most common problem faced while dealing with the blob file. From your example, I can see that you are saving "fileType" as the extensions of the files (i.e 'jpg' for images, 'pdf' for pdf files etc.), you are uploading. But instead of that you can can save file type as the MIME content type.

Suppose if you upload a jpeg image - the MIME type will be stored in the "fileType" as the "image/jpeg". Similarly for pdf it will be stored as "application/pdf". I designed code like this to download the blob file from the database. I am going to assume that the files are already uploaded into the database table you created.

Database table "uploads"

| fileID | fileName | fileType | fileSize |fileData | userID |

download.php

<?php
$connection =  mysqli_connect("localhost","root"," ",your_database)
               or die('Database Connection Failed');
mysqli_set_charset($connection,'utf-8');

$id = 1;

// Use a prepared statement in production to avoid SQL injection;
// we can get away with this here because we're the only ones who
// are going to use this script.
$query = "SELECT * " ."FROM uploads WHERE userID = '$id'";
$result = mysqli_query($connection,$query) 
       or die('Error, query failed');
list($id, $file, $type, $size,$content) = mysqli_fetch_array($result);
header("Content-length: $size");
header("Content-type: $type");
header("Content-Disposition: attachment; filename=$file");
ob_clean();
flush();
echo $content;
mysqli_close($connection);
exit;

?>

You can find the complete code of blob-upload here.

Comments

[Sanket Deshpande]

it has nothing to do with session variables but with mime type

[tmzafar]

this worked fine, when i directed it from HTML to PHP and it forced a download, but with jQuery & PHP it does not work.The content gets echoed in the console, but no file is downloaded!

[ADyson]

@tmzafar yep, that's how AJAX works - the response goes back to the JavaScript. Don't expect to download files via Ajax.