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?

Content-type should be image/jpeg that may or may not fix the issue you are having. Honestly you should have just stored paths of the images in the database instead of storing them as blobs — elitechief21
– elitechief21, Commented Jan 22, 2014 at 18:56
@elitechief21 the image example i used in the question above is just an example. i want to allow zip folders and word documents to be stored as well. — user1386999
– user1386999, Commented Jan 22, 2014 at 18:58

Your Common Sense · Accepted Answer · 2023-07-28 13:23:45Z

12

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"

download.php

<?php
$connection =  mysqli_connect("localhost","root"," ",your_database);

$id = 1;

$query = "SELECT * FROM uploads WHERE userID = ?";
$result = $connection->execute_query($query, [$id]); 
list($id, $file, $type, $size, $content) = mysqli_fetch_array($result);
header("Content-length: $size");
header("Content-type: $type");
header("Content-Disposition: attachment; filename=$file");
echo $content;

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

edited Jul 28, 2023 at 13:23

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

answered Jul 9, 2015 at 13:19

Sampath

4016 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

tmzafar Over a year ago

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 Over a year ago

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

meisamphp · Accepted Answer · 2016-03-31 09:26:19Z

-1

you can use session and post filename and file content to download page

session_start();
$_SESSION['filename'] = $filename
$_SESSION['file'] = $file
<a href="download.php">echo "Download File"</a>

and in download.php use this code

session_start();
$filename = $_SESSION['filename'];
$file = $_SESSION['file'];
header("Content-Disposition: attachment; filename=$filename");
ob_clean();
flush();
echo $file;

answered Mar 31, 2016 at 9:26

meisamphp

397 bronze badges

1 Comment

Sanket Deshpande Over a year ago

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

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related