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,
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.
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:
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 -pEnter password:
If the server is running on another host, use this:
mysql -u root -h hostname -pEnter 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. Don't believe me? Try it yourself!
Next page: 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!
View contents of open_db.inc.
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
View contents of add.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.
Next page: 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.
View contents of main.php.
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.
View contents of download.php.
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.
Have fun and keep writing!
Joao Prado Maia is a web developer living in Houston with more than four years of experience developing web-based applications and loves learning new technologies and programming languages.
Discuss this article in the O'Reilly Network Forum.
Return to the O'Reilly Network Hub.
Copyright © 2009 O'Reilly Media, Inc.