Storing Images in MySQL Revisited

By Quentin Zervaas, 13 March 2010 MySQL PHP Download PDF (1 comment)

Creating a Database Table

To begin with storing images in MySQL, let's create a database table. For the purposes of this article, I'll assume you already have a database set up with the following details:

• Server address: localhost
• Database name: phpriot_demo
• Database username: phpriot_demo
• Database password: phpriot123

The following listing shows how you can create this database on your server.

Listing 1 Creating a database and a database user (listing-1.txt)

mysql> create database phpriot_demo;
Query OK, 1 row affected (0.00 sec)

mysql> grant all on phpriot_demo.* to phpriot_demo@localhost identified by 'phpriot123';
Query OK, 0 rows affected (0.00 sec)

mysql> use phpriot_demo;
Database changed

Let's now create the database table. In addition to storing the file data, we are also going to the store following:

• A unique ID for the image. We'll use the serial type for this (this is a shortcut for bigint unsigned auto_increment).
• The original filename of the image. We'll use varchar(255) for this, meaning we can easily index the table by the filename if we wanted to (we could use text since there's no reason we have to limit the length to 255, but we cannot fully index a text field). Indexing means we can quickly find a file by its filename.
• The file mime type. We're allowing users to upload images, which might be in jpg, png or gif format. We use the mime type when sending the image back to users. We could determine the mime type when required, but this is never going to change for a file so we might as well save some future processing power by determining it when the database record is created.
• The size of the file. When we send the image back to the user we want to use this value to tell the user's browser how big the image is. Since this value won't change, we can simply store it when we insert the image into the database.

The statement used to create the table is shown in the following listing.

Listing 2 Creating a database table in which to store images (listing-2.sql)

create table images (
    image_id    serial,
    filename    varchar(255) not null,
    mime_type   varchar(255) not null,
    file_size   int          not null,
    file_data   longblob     not null,
 
    primary key (image_id),
    index (filename)
);

The following listing shows the table when you describe it.

Listing 3 The database table once created (listing-3.txt)

mysql> describe images;
+-----------+---------------------+------+-----+---------+----------------+
| Field     | Type                | Null | Key | Default | Extra          |
+-----------+---------------------+------+-----+---------+----------------+
| image_id  | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| filename  | varchar(255)        | NO   | MUL |         |                |
| mime_type | varchar(255)        | NO   |     |         |                |
| file_size | int(11)             | NO   |     |         |                |
| file_data | longblob            | NO   |     |         |                |
+-----------+---------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

• Next: Connecting to the Database
• Download: Includes 1 bonus listing