Become Zend Certified

Prepare for the ZCE exam using our quizzes (web or iPad/iPhone). More info...

When you're ready get 7.5% off your exam voucher using voucher CJQNOV23 at the Zend Store

Storing Images In MySQL

Note: This article has been replaced by Storing Images in MySQL Revisited (by Quentin Zervaas, 2010). We've left this article here for historical purposes, but we strongly recommend you use the newer article instead.

Creating An Image Table

Of course we need to create a table to store our images in. Here is a simple table:

Listing 2 listing-2.sql
create table testblob (
    image_id        tinyint(3)  not null default '0',
    image_type      varchar(25) not null default '',
    image           blob        not null,
    image_size      varchar(25) not null default '',
    image_ctgy      varchar(25) not null default '',
    image_name      varchar(50) not null default ''

Ok, nothing dramatic here, but let’s break it down.

First we have an id field, this should be an auto incremented field so we avoid confusion when selecting.

Next we have the image type (mime type). This is handy as we can use PHP to determine this and use it for sending the correct headers when we need to display the image.

Next we have the image_blob field. This is the field that will contain the actual image data.

Then we have the image size as determined by getimagesize().

Then we have a category for our images, you may wish to have have a second table of categories to populate your form dropdown for this. But for our purposes we will only be using three.

Finally, we have the image name. You can add more items to this table such as exif info and descriptions, but for our purposes we will keep it simple.

In This Article

Article History

Apr 20, 9804
Initial article version
Jan 18, 2008
Updated listing 5 to not include non-existent conf.php