Uploading images to a MySQL database table

Uploading images into a MySQL database table is a bad idea

MySQL™ IMHO, uploading images into a MySQL™ BLOB field is such a bad idea that examples of how to accomplish this have been removed from this site.


Certain web hosts use their Terms Of Service (which no one reads) to specifically disallow uploading images into MySQL™ and will cancel the hosting account if images are stored in the MySQL™ database. Web hosts feel that image uploads place a burden on the the server that effects all users, especially if the uploaded images are large in size or they were not web-optimized before being uploaded. Check the terms of your hosting agreement before uploading images or other types of binary data.

If you ever had a MySQL™ table containing a lot of binary image data, and had to export the data using phpMyAdmin and then insert the images in a new MySQL™ table, you probably concluded it's a major headache. I wouldn't recommend a MySQL™ image database to anyone. A possible exception would be if comparatively few images are involved, and all of the images are small in size (thumbnails), and all of the images were web-optimized (compressed) before uploading them. Uploading images into MySQL™ is clever, but IMHO, it is generally a bad idea. Recording information about a file that is physically uploaded is a better idea (discussed below).

Store the HTML IMG SRC tag (or the file details) instead of storing an image as a BLOB

Here is a link to a great image upload script, capable of manipulating an image in dozens of different ways: class.upload.php.

See phpclasses.org for examples of image upload scripts. Secure, properly executed upload scripts can be rather difficult and/or time consuming to develop.

Image management may best be achieved by physically uploading images to the server and then recording file location and image details in a database. Subsequently, a Search Form could be configured to permit the user to do a text search, part number search, or other queries. A PHP script could be written to produce a valid HTML image tag based on data found in the table. The following is the MySQL™ schema example, for an image management table.

   `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
   `mime_type` char(255) NOT NULL,
   `dir` char(255) NOT NULL DEFAULT '',
   `filename` char(255) NOT NULL DEFAULT '',
   `wh` char(255) NOT NULL DEFAULT '',
   `text_1` char(255) NOT NULL DEFAULT '',
   `page_id` smallint(5) unsigned NOT NULL DEFAULT '0',
   `user_id` smallint(5) unsigned NOT NULL DEFAULT '0',
   `position` smallint(5) unsigned NOT NULL DEFAULT '20',
   `hidden` enum('0','1') NOT NULL DEFAULT '0',
   `deleted` enum('0','1') NOT NULL DEFAULT '0',
   `upld_date` date NOT NULL DEFAULT '0000-00-00',
   `upld_time` time NOT NULL DEFAULT '00:00:00',
   PRIMARY KEY (`id`),
   KEY `idx_page_id` (`page_id`),
   KEY `idx_position` (`position`)

-- Dumping data for table `vs_fancyapps`

INSERT INTO `vs_fancyapps` (`id`, `mime_type`, `dir`, `filename`, `wh`, `text_1`, `page_id`, `user_id`, `position`, `hidden`, `deleted`, `upld_date`, `upld_time`, `updated`) VALUES(1, 'image/jpeg', 'imgs/048/tn', 'hi001.jpg', 'width="200" height="150"', 'Only a test', 48, 1, 20, '0', '0', '2012-03-05', '21:55:58', '2012-09-15 12:38:03');

CREATE TABLE image_location (
   image_path CHAR(255) DEFAULT '',
   image_name CHAR(255) DEFAULT '',
   image_width SMALLINT(4) DEFAULT '',
   image_height SMALLINT(4) DEFAULT '',
   alt_tag_text CHAR(255) DEFAULT '',
   category CHAR(20) DEFAULT '',
   description CHAR(255) DEFAULT '',
   part_number CHAR(20) DEFAULT '',
   upc_code CHAR(20) DEFAULT '',
   upld_by_person CHAR(20) DEFAULT '',
   upld_by_dept CHAR(20) DEFAULT '',
   upld_on TIMESTAMP(14) NULL,
   PRIMARY KEY (id), KEY ID(id),
   FULLTEXT KEY search_index(image_name,description))

Problems with storing images in MySQL

Restoring exported image data is generally problematic if the images are much larger than thumbnails. If the images are large, you can end up having to copy/paste one SQL INSERT statement at a time (into phpMyAdmin). If the images are large and the SQL INSERT statement is broken into two lines by your text editor, you'll never be able to restore the image. In a basic eCommerce table with 77 images each 167-pixels square, plus a modest product description, the table size is about 1.5-MB. Having developed this on my PC, it took about an hour to hand-feed / upload data to the web server's phpMyAdmin interface, despite having a fast broadband connection.

You can insert images in MySQL™ all day long ... but if you have to move the binary data to another server or a new MySQL™ table, or restore a backup because a table crashed, you will likely encounter problems of one sort or another. In short, you will likely regret your decision to insert image files into a MySQL™ database table.

If you absolutely can't resist creating a MySQL™ image database, use the smallest image size that suits your objective, and make sure you've optimized the original images before inserting them in the database. In most cases, JPG/JPEG compression of 75% yields the smallest image size. If image quality is decent at 50%-60% JPG compression, use it.

This forum thread also talks about problems with uploading images into MySQL™.

If you found the above information helpful, please consider buying a copy of my PHP Form Generator.

Image Upload using PHP

Here is my favorite image upload script.

Various image upload scripts can be found on phpclasses.org.

Copyright © 1999-2015 by Doug Hockinson - All rights reserved - Site Map - Contact
Find us on Google+ - Find us on Google+
phpvs.com - php-html-form.com - hockinson.biz - php-form-generator.com - hockinson.us

web hosting