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.

PHP

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.

CREATE TABLE IF NOT EXISTS `vs_fancyapps` (
   `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',
   `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   PRIMARY KEY (`id`),
   KEY `idx_page_id` (`page_id`),
   KEY `idx_position` (`position`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=22 ;

--
-- 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 (
   id MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
   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))
TYPE=MyISAM;

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.

March 2013 - Twitter Bootstrap Form Generator version includes tinyMCE

Check out the PHP Form Generator for MySQL™ MyISAM type tables which has been purchased by more than 300 web developers located in 30 different countries.

The above referenced PHP script generator uses the phpMyEdit class file in order to instantly create forms allowing users to manipulate records in a MySQL™ database table.

phpMyEdit based forms are highly configurable, ideal for administrative interfaces, and often suitable for public data display.

phpMyEdit is multi-language friendly with respect to the labels which are applied to various INPUT buttons used to navigate through the data set.


Picasa Web Albums

Picasa Web Albums will solve the needs of many people who need to put large quantities of photographs online.


Best Web Host

Hostgator.com offers Fantastico which will automatically install Gallery on your hostgator.com web hosting account.

web hostingBest web host I've ever used, regular updates to cPanel® , PHP, MySQL.

November 2011 - Google launched a campaign to provide 500,000 small businesses in India with free domain names which will be hosted by Hostgator.com.

Copyright © 1999-2014 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