This article is aimed at answering one of the most asked questions on the PHP mailing list and discussion forums alike: How to store binary files in a MySQL database.

Uploading, Saving and Downloading Binary Data in a MySQL Database

This article is aimed at answering one of the most asked questions on the PHP mailing list and discussion forums alike: How to store binary files in a MySQL database.

I ran into this same question when asked by a possible employer testing my programming skills to create a set of scripts to upload files to a MySQL database, download files from it, and also show an image, if the file was indeed an image. Anyway, I couldn't find any articles on how to do that, so I searched a lot in the PHP mailing lists to find my answer. This article is my way of "giving back" to the community. :)

I split the article into three pages,

• Setting up the database
• Creating the 'upload' scripts
• Creating the 'download' script

so everything can be explained at the correct time, and also because I added some extra features, like a download script to get the binary files back from the database. This is extremely useful for downloading files of different types from a company database, or even displaying images stored in BLOB fields.

Setting up the database

I give instructions below for MySQL databases, since it was the database I used when developing my web application.

Before starting out, I need to explain what a BLOB field is. Like a teacher would say: "A BLOB is a binary large object that can hold a variable amount of data." This essentially means that BLOB is a datatype that can hold binary content, and we can use it to store files.

In order to set up our database, we should optimize the fields on our tables to not waste any resources. This means that you shouldn't use a LONGBLOB field when you only need to upload 1.5 Kb files. Quoting from the MySQL Online Documentation:

• TINYBLOB - A BLOB column with a maximum length of 255 (2⁸ - 1) characters.
• BLOB - A BLOB column with a maximum length of 65,535 (2¹⁶ - 1) characters.
• MEDIUMBLOB - A BLOB column with a maximum length of 16,777,215 (2²⁴ - 1) characters.
• LONGBLOB - A BLOB column with a maximum length of 4,294,967,295 (2³² - 1) characters.

For most applications, a MEDIUMBLOB field is more than enough, since it can hold up to 15 megs of binary data. Anyway, let's create the database and tables for our web application. We need to connect to the MySQL server:

mysql -u root -p
Enter password: ******

If the server is running on another host, use this:

mysql -u root -h hostname -p
Enter password: ******

Now to create the actual database:

mysql> CREATE DATABASE binary_files;
Query OK, 1 row affected (0.00 sec)

Ok, we now have a database to play with. We can create the tables now.

mysql> CREATE TABLE tbl_Files (
> id_files tinyint(3) unsigned NOT NULL auto_increment,
> bin_data longblob NOT NULL,
> description tinytext NOT NULL,
> filename varchar(50) NOT NULL,
> filesize varchar(50) NOT NULL,
> filetype varchar(50) NOT NULL,
> PRIMARY KEY (id_files)
> );

Now we need to create a custom user for this database/application for maximum security:

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER
> ON binary_files.*
> TO binary_user@localhost
> IDENTIFIED BY 'binary_password';

That's all. Now we can connect to the server from Apache/PHP using the user binary_user and password binary_password.

Creating the upload scripts

To make thing easier, I created an open_db.inc include file, so I don't have to keep using the same code to connect to a server and select the database. I'm a lazy programmer, and you should be one too!

<?php
$db = mysql_connect("localhost", 

"binary_user", "binary_password");
mysql_select_db("binary_files", $db)

or die(mysql_errno() . ": " . mysql_error() . "<br>");
?>

This next script is used to upload a new file into the database. We always have to check the variable ($binFile) for contents. Whenever someone just hits "Upload" without selecting a file, the file input field variable will hold "none". We also have to make an extra security check on the binary content itself, since it can contain some weird characters that need a slash. This can break our SQL statement, so we addslashes() it!

<?php
if ($action == "upload") {
// ok, let's get the uploaded data and insert it into the db now
include "open_db.inc";
if (isset($binFile) && $binFile != "none") {
$data = addslashes(fread(fopen($binFile, "r"), filesize($binFile)));
$strDescription = addslashes(nl2br($txtDescription));
$sql = "INSERT INTO tbl_Files ";
$sql .= "(description, bin_data, filename, filesize, filetype) ";
$sql .= "VALUES ('$strDescription', '$data', ";
$sql .= "'$binFile_name', '$binFile_size', '$binFile_type')";
$result = mysql_query($sql, $db);
mysql_free_result($result); // it's always nice to clean up!
echo "Thank you. The new file was successfully added to our database.<br><br>";
echo "<a href='main.php'>Continue</a>";
}
mysql_close();
} else {
?>
<HTML>
<BODY>
<FORM METHOD="post" ACTION="add.php" ENCTYPE="multipart/form-data">
<INPUT TYPE="hidden" NAME="MAX_FILE_SIZE" VALUE="1000000">
<INPUT TYPE="hidden" NAME="action" VALUE="upload">
<TABLE BORDER="1">
<TR>
<TD>Description: </TD>
<TD><TEXTAREA NAME="txtDescription" ROWS="10" COLS="50"></TEXTAREA></TD>
</TR>
<TR>
<TD>File: </TD>
<TD><INPUT TYPE="file" NAME="binFile"></TD>
</TR>
<TR>
<TD COLSPAN="2"><INPUT TYPE="submit" VALUE="Upload"></TD>
</TR>
</TABLE>
</FORM>
</BODY>
</HTML>
<?php
}
?>

We are using the same script to handle both actions: uploading a new
file and showing the form to add the information related to this new
file. The hidden input field called MAX_FILE_SIZE on the form is needed so the client side can limit the filesize of new files. Also, don't forget to add the extra attribute ENCTYPE="multipart/form-data" to your form, or else your script is not going to work at all.

Okay, that seemed almost too easy, right? Now what we need to do is create the download script, as well as the script that shows the list of files currently in the database.

Creating the download scripts

First let's create the script to show the list of files currently in our table, and then we can start working on the script to download the files.

<?php
include "open_db.inc";
$sql = "SELECT * FROM tbl_Files ";
$sql .= "ORDER BY filename ASC";
$result = mysql_query($sql, $db);
$rows = mysql_num_rows($result);
echo "<table>\n";
echo " <tr>\n";
echo "  <td>Filename</td>\n";
echo "  <td>Type</td>\n";
echo "  <td>Size</td>\n";
echo "  <td>Description</td>\n";
echo "  <td> </td>\n";
echo " </tr>\n";
for ($i = 0; $i < $rows; $i++) {
$data = mysql_fetch_object($result);
// since our script is very small, i'm not going to escape out to html mode here
echo " <tr>\n";
echo "  <td>$data->filename</td>\n";
echo "  <td>$data->filetype</td>\n";
echo "  <td>$data->filesize</td>\n";
echo "  <td>" . stripslashes($data->description) . "</td>\n";
echo "  <td>( <a href='download.php?id=$data->id_files'>Download</a> )</td>\n";
echo " </tr>\n";
}
mysql_free_result($result);
mysql_close($db);
?>

This should create a clean and beautiful table with all the files on the table. For a real world application, you would probably want to limit the number of files displayed here, and also do some formatting on the filesize and filetype fields. I'll leave that as an exercise for the reader.

Let's go to the download script now. It's really simple, and it is going to work with any type of file inside the database. Whenever users click on the "Download" links, a download dialog should open with the filename coming from the database.

<?php
if ($id_files) {
include "open_db.inc";
$sql = "SELECT bin_data, filetype, filename, filesize FROM tbl_Files WHERE id_files=$id_files";
$result = @mysql_query($sql, $db);
$data = @mysql_result($result, 0, "bin_data");
$name = @mysql_result($result, 0, "filename");
$size = @mysql_result($result, 0, "filesize");
$type = @mysql_result($result, 0, "filetype");
header("Content-type: $type");
header("Content-length: $size");
header("Content-Disposition: attachment; filename=$name");
header("Content-Description: PHP Generated Data");
echo $data;
}
?>

Sometimes in other scripts, text files (.txt) or even images are shown in the browser window, instead of opening the download dialog. That's because of the HTTP header "Content-Disposition:," which needs to have something recognizable as the first argument. If you put a recognizable MIME type in there, such as an image MIME type, the browser will try to open it in the window. The same goes for text files or any other filetype that the browser can recognize.

A lot of times people ask how they could track the number of downloads for each file. This script can be easily changed to track the "hits," and you could even create an extra script to show statistics of each file.