Uploading, Saving and Downloading Binary Data in a MySQL Database

by Joao Prado Maia
09/15/2000

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. Don't believe me? Try it yourself!

Next page: Creating the upload scripts

Pages: 1, 2, 3

Next Page