如何从 PHP 中的 MySQL 数据库下载基于 blob 的文件?

2022-01-02 00:00:00 download blob php mysql

如何制作允许我从 MySQL 数据库下载文件的 PHP 脚本.我有下表名为 files,其中上传的文件保存在基于 BLOB 的字段中.

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.

我已经尝试了这个查询,其中参数通过(文件 ID、文件名、文件类型)传递到一个 download.php 页面:

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.

例如,我如何将 file1 作为 JPEG 文件下载到我的硬盘上?

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

推荐答案

这是处理 blob 文件时最常见的问题.从您的示例中,我可以看到您将fileType"保存为文件的扩展名(即图像为jpg",pdf 文件为pdf"等),您正在上传.但是,您可以将文件类型保存为 MIME 内容类型.

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.

假设如果您上传 jpeg 图像 - MIME 类型将作为图像/jpeg"存储在fileType"中.同样,对于 pdf,它将存储为application/pdf".我设计了这样的代码来从数据库下载 blob 文件.我将假设文件已经上传到您创建的数据库表中.

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.

数据库表上传"

|文件ID |文件名 |文件类型 |文件大小 |文件数据 |用户 ID |

| 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;

?>

您可以在这里找到完整的 blob-upload 代码.

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

相关文章