Storing Images In MySQL
Creating An Image Table
Of course we need to create a table to store our images in. Here is a simple table:
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.