Jump to content
wei123

Download file from mySQL database (blob)

Recommended Posts

I have 3 files:

config.php

<?php

$db = new PDO("mysql:host=localhost;dbname=datarepo", "root", "");

?>

data.php (the data repository which displays the table of files from my database)

<?php

<table class ="table">
<thead>

<tr>
<th>#</th>
<th>File</th>
<th>Action</th>
</tr>

</thead>

<tbody>
<?php
include('config.php');

$stmt = $db -> prepare("SELECT * FROM files");
$stmt->execute();


while($row = $stmt->fetch()){
?>

<tr>
 <td> <?php echo $row["id"]?></td>
 <td> <?php echo $row["name"]?></td>
 <td><a href="download.php?id=<?php echo $row["id"]?>" class="btn btn-primary">Download</a></td>
</tr>

<?php
}
?>

</tbody>

</table>

?>

download.php

<?php

include "config.php";
if(isset($_GET['id'])){
    
    $id = $_GET['id'];
    $stmt = $db -> prepare("select * from files where id=?");
    $stmt->bindParam(1,$id);
    $stmt->execute();
    $data = $stmt -> fetch();
    
    $file = 'dpo'.$data['name'];

    if(file_exists($file)){
        header('Content-Disposition: name="'.basename($file).'"');
        header('Content-Type:'.$data['mime']);
        header ('Content-Length:'.filesize($file));
        readfile($file);
        exit;
        
    }
      
}
?>

When I try to download a file from the data repository (data.php):

datarepo.PNG.579aea8fc0d3c4c445986943a1b1605f.PNG

I will be brought to any empty page but no file will be downloaded, with the url: http://localhost/dpo/download.php?id=1

Here is a sample of the 'files' table from the 'datarepo' database in mySQL:

table.PNG.f669d520644a35fb46e0a15d90fdd4cc.PNG

So my question is, how do I get the file to download?

Share this post


Link to post
Share on other sites

try content-disposition : attachment

header('Content-Disposition: attachment; filename="'.basename($file).'"');

 

Share this post


Link to post
Share on other sites
7 minutes ago, Barand said:

try content-disposition : attachment


header('Content-Disposition: attachment; filename="'.basename($file).'"');

  

no luck, download.php still gives a white page without downloading the file.

For reference, here's my upload file (please ignore the file sanitation for now) :

<?php

include('config.php');

if(isset($_POST["submitfile"])) {
  
	$fileName = $_FILES['file']['name'];
	$fileSize = $_FILES['file']['size'];
	$fileError = $_FILES['file']['error'];
	$fileType = $_FILES['file']['type'];
	$data = file_get_contents($_FILES['file']['tmp_name']);

	$fileExt = explode('.', $fileName);
	$fileActualExt = strtolower(end($fileExt));
	
	$allowed = array('csv','doc','docx','jpg','pdf','png');
	
	
	if (in_array($fileActualExt, $allowed)){
		if ($fileError == 0){
			if ($fileSize < 5000000){
				$stmt = $db ->prepare("insert into files values('',?,?,?,?)");
				$stmt ->bindParam(1,$fileName);
				$stmt ->bindParam(2,$fileType);
				$stmt ->bindParam(3,$fileSize);
				$stmt ->bindParam(4,$data);
				$stmt ->execute();

				echo "File uploaded successfully.";
			}
			
			else {
				echo "File size is too big.";
			}
		} else {
			echo "There were was an error uploading the file.";
		}
		
	} else {
		echo "File format not supported.";
		
	}
	
}
?>

 

Edited by wei123

Share this post


Link to post
Share on other sites

a) it is generally not a good idea to store files in a database (getting large data into and out of the database is a problem. and sql dumps are twice the size since they are done in hexadecimal.) databases are for storing data. file systems are for storing files.

b) if you do store files in a database, you must store all of the file. the blob type you have used only holds 64K bytes. the file size your code allows is 5M bytes (and would take special handling to store since the default data packet size is 1M bytes.) the actual size of your test file is 102k bytes, which was then cut off at 64K when it was stored.

c) the php code in download.php is trying to check the existence of and read the file as though it is stored in the file system, not in the database.

Edited by mac_gyver
  • Like 1

Share this post


Link to post
Share on other sites
32 minutes ago, mac_gyver said:

a) it is generally not a good idea to store files in a database (getting large data into and out of the database is a problem. and sql dumps are twice the size since they are done in hexadecimal.) databases are for storing data. file systems are for storing files.

b) if you do store files in a database, you must store all of the file. the blob type you have used only holds 64K bytes. the file size your code allows is 5M bytes (and would take special handling to store since the default data packet size is 1M bytes.) the actual size of your test file is 102k bytes, which was then cut off at 64K when it was stored.

c) the php code in download.php is trying to check the existence of and read the file as though it is stored in the file system, not in the database.

a) Yeah I'm well aware of that but my project requires me to store the files into the database so I don't have a choice here.

b) Is this alright? I've changed the data type from 'blob' to 'longblob', so now the blob size matches the file size

1790735789_newtable.PNG.025891b1d9b95899cd30d9bf9f675e9c.PNG

c) I've remove the if else block so download.php now looks like this:

<?php
include "config.php";
if(isset($_GET['id'])){
	
	$id = $_GET['id'];
	$stmt = $db -> prepare("select * from files where id=?");
	$stmt->bindParam(1,$id);
	$stmt->execute();
	$data = $stmt -> fetch();
	$file = $data['name'];

		header('Content-Disposition: attachment; filename="'.basename($file).'"');
		header('Content-Type:'.$data['mime']);
		header ('Content-Length:'.filesize($file));
		readfile($file);
		exit;
	
}


?>

But now whenever I try to download something, I will always end up with a 322B file.

Edited by wei123

Share this post


Link to post
Share on other sites

"your project requires" that you store files in a db?  Who is making this a requirement?  Not a teacher I hope!

  • Like 1

Share this post


Link to post
Share on other sites

The functions filesize() and readfile() both require the file to be stored in the file system.

If you are storing the file in the database, that is where your filesize and filedata need to be downloaded from. (And as mac_gyver pointed out, a BLOB column only holds 64K. Change it to MEDIUMBLOB (16MB) )

Better still, store the uploaded files on the server and store the metadata only in the database.

Share this post


Link to post
Share on other sites

A couple of pointers regarding the first piece of code in your original post (to list the  file names)

Your query is

$stmt = $db -> prepare("SELECT * FROM files");
  1. There is no point in preparing a query of that nature as there are no user-provided items of data.
  2. Don't use SELECT STAR. Especially as you are storing large blob items. You are now retrieving every column of every row (including the unnecessary blobs). This just wastes time and resources. Always specify the columns you need. In this case
$stmt = $db->query("SELECT id, name FROM files");

 

Share this post


Link to post
Share on other sites

Updated download.php:

<?php
include "config.php";
if(isset($_GET['id'])){
	
	$id = $_GET['id'];
	$stmt = $db->query("SELECT id, name,mime,filesize FROM files");
	$stmt->bindParam(1,$id);
	$stmt->execute();
	$data = $stmt -> fetch();
	$file = $data['name'];
	$type = $data['mime'];
	$size = $data['filesize'];
	
	// echo $data['name']."<br>";
	// echo $data['mime']."<br>";
	// echo $data['filesize']."<br>";
	
	
	header("Content-Disposition: attachment; filename=$file");
	header("Content-Type: $type");
	header("Content-Length:$size");
	
	exit;
	
	
}

?>

 

Right now, the files I download are only 264 bytes in size.

 

Updated table:

table3.PNG.d75643eef53e653dba13c827bd5f3e10.PNG

 

Echo messages give me:

datarepo.PNG
image/png
28282

 

datarepo.PNG

Edited by wei123
code edit

Share this post


Link to post
Share on other sites

You should also think about outputting the contents of the file too. All you do is send headers.

The query I gave you was the one where you list the files for selection. You cannot use the same query every time you access the database. This time you need to select the required record (id = ?) and get all the fields (including the blob column).

I recommend you turn on php's error reporting - you should have got errors telling you that indexes mime and filesize do not exist (as you didn't select those columns)

Share this post


Link to post
Share on other sites
1 hour ago, wei123 said:

my project requires me to store the files into the database so I don't have a choice here

Something smells amiss here.

Share this post


Link to post
Share on other sites

As you are too lazy to specify column names in your SELECT and INSERT statements, some of this is guesswork. But it should look something like this

$stmt = $db->prepare("SELECT name
                           , size
                           , mime
                           , data
                      FROM files
                      WHERE id = ?     
                      ");
$stmt->execute([ $_GET['id'] ]);
$file = $stmt->fetch();
   
        header('Content-Disposition: attachment; filename="'.basename($file['name']).'"');
        header('Content-Type: '.$file['mime']);
        header ('Content-Length: '.$file['size']);
        echo $file['data'];

 

  • Like 1

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.