Uploading images into a MySQL database table is a bad idea

MySQL Image Table

121communications.com
american-web-design.com
attaindata.com
audiocuts.com
b2ce.com
clawsonconsulting.com
clawsonline.com
colorado-developer.com
colorado-website-design.com
cymax2.com
cymaxdev.com
cymaxmedia.com
cymaxnow.com
greatglobalwarming.com
monument-colorado.com
peakwebsolutions.com
web-denver.com

Try Firefox

The short explanation

To get your uploaded image ($file) into $data that can be used in a MySQL™ INSERT statement, all you need is something like this:

if(!$data = addslashes(@fread(@fopen($dir.'/'.$file, "r"), @filesize($dir.'/'.$file)))){
die('Cannot create $data');
}
// followed by the appropriate MySQL™ INSERT statement shoving $data into a BLOB field
@fclose($dir.'/'.$file);

Problems with storing images in MySQL (store the HTML code instead)

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.

If you're still interested in uploading images into MySQL™, download and install a copy of phpMyEdit. After you have phpMyEdit working, obtain a copy of "image_edit.php" listed under Attached Files in the Bug Note page of the phpMyEdit Forum. This will give you all that you need to begin uploading images into MySQL™. Upload only web-optimized images (e.g. .JPG's at 75% compression, or small .GIF's).

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

Store related data in your MySQL image fields not the BLOB data itself. The following schema stores information to be displayed within my CMS. Upload ID, the ID of the logged-in user, the name of the logged-in user, gallery ID, CMS page ID, text optionally displayed below the photo, a URL optionally displayed below the photo, the upload time using the PHP time() value, the directory in which the file is stored, the filename, a priority number (999) which can be reduced to achieve sorting or moving a particular image to the first display position, extension of the filename, MIME type, category being either thumbnail or original image, the HTML tag for each image, and other image properties which are unused but happen to be available at the moment of insertion. Data for all images displayed using my CMS are stored in the same table, and storing the page ID of the intended display page is very useful.

CREATE TABLE IF NOT EXISTS `vs_lightbox` (
  `upld_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` varchar(20) NOT NULL DEFAULT '',
  `user_name` varchar(20) NOT NULL DEFAULT '',
  `gallery_id` enum('0','1','2','3','4','5','6','7','8','9','10') NOT NULL DEFAULT '0',
  `page_id` smallint(5) unsigned NOT NULL,
  `photo_text` varchar(255) NOT NULL,
  `external_url` varchar(100) NOT NULL,
  `upld_time` varchar(10) NOT NULL DEFAULT '',
  `dir` varchar(255) NOT NULL DEFAULT '',
  `filename` varchar(50) NOT NULL DEFAULT '',
  `image_priority` smallint(3) NOT NULL DEFAULT '999',
  `ext` varchar(5) NOT NULL DEFAULT '',
  `mime_type` varchar(50) NOT NULL DEFAULT '',
  `category` enum('image','thumb','other') NOT NULL DEFAULT 'image',
  `html_tag` text NOT NULL,
  `img_width` varchar(4) NOT NULL DEFAULT '',
  `img_height` varchar(4) NOT NULL DEFAULT '',
  `img_type_no` varchar(2) NOT NULL DEFAULT '',
  `img_type_txt` varchar(20) NOT NULL DEFAULT '',
  `img_src_attr` varchar(30) NOT NULL DEFAULT '',
  `hidden` enum('0','1') NOT NULL DEFAULT '0',
  `deleted` enum('0','1') NOT NULL DEFAULT '0',
  `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`upld_id`),
  KEY `idx_category` (`category`),
  KEY `idx_gallery_id` (`gallery_id`),
  KEY `idx_image_priority` (`image_priority`),
  KEY `idx_upld_time` (`upld_time`),
  KEY `idx_external_url` (`external_url`),
  KEY `idx_page_id` (`page_id`),
  KEY `idx_hidden` (`hidden`),
  KEY `idx_deleted` (`deleted`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

When you devise your upload script, make sure you compress .JPG images by 75% or so, to reduce file sizes. See phpclasses.org for examples of image upload scripts. Secure, properly executed upload scripts can be very difficult to develop.

MySQL™ / PHP Form Generator

PHP Form Generator will create one form for each table found in the currently configured MySQL™ database in just a few seconds. Uses phpMyEdit and requires MyISAM type tables. Most users find it pays for itself in a few minutes, currently priced at $39 US.

Example script for uploading images to MySQL

Update: 16 July 2006 - How To Insert Images Into MySQL Database

The above script should give you something like this:

mysql schema

13 March 2007 - PHP based image upload / file upload example, with option to insert image and/or thumbnail into MySQL

Other Options

Below are excerpts from an older version of a open source content management system named PHPMySite which includes scripts and forms required to insert images into a MySQL™ database table. Obtain a copy of PHPMySite if you're looking for a means of inserting images into a MySQL™ database table using PHP scripting. There aren't very many PHP scripts involved in this project, thus you should be able to easily determine which scripts you'll need: getimage.php, graphics.php, PHPMyEdit.inc, saveimage.php, and .htconfig which contains the database logon.

The following are excerpts from the phpMySite project ... obtain a copy of the project in order to get the Upload Form (saveimage.php).

# MySQL Schema
CREATE TABLE images (
   Seq_No smallint(5) NOT NULL auto_increment,
   Picture longblob NOT NULL,
   Alt varchar(20) default NULL,
   File_Type varchar(20) default NULL,
   PRIMARY KEY (Seq_No)
) TYPE=MyISAM;

<?php
// getimage.php - by Florian Dittmer <dittmer@gmx.net>
// Example php script to demonstrate the direct passing of binary data
// to the user. More infos at http://www.phpbuilder.com
// Syntax: getimage.php?id=$id

if (array_key_exists('id',$_GET)) {$id = $_GET['id'];} else {$id='';}
if($id)
{
   include('.htconfig.php');
   $query = "SELECT Picture, Alt, File_Type FROM images WHERE Seq_No=$id";
   $result = MYSQL_QUERY($query,$dbl);
   $data = MYSQL_RESULT($result,0,"Picture");
   $type = MYSQL_RESULT($result,0,"File_Type");
   Header( "Content-type: $type");
   echo $data;
};
?>

<?php
// .htconfig.php
$hn=''; /*host name for MySQL*/
$un=''; /*user name for MySQL*/
$pw=''; /*password for MySQL*/
$db=''; /*database name for MySQL*/
$dbl = @mysql_pconnect($hn,$un,$pw)
or die ('<html><body><h1>Database not available - please try later</h1></body></html>'."n");
mysql_select_db ( $db, $dbl)
or die ('<html><body><h1>Database not found - please try later</h1></body></html>'."n");
$stylesheet = 'bluebird.css';
?>

Inserting images in a MySQL™ database table works very well in terms of organizing large numbers of images in a multi-user environment ... but your pages won't download as quickly as they would with traditional image handling methods. As mentioned above, if you subsequently need to move the image table to another server, or restore a crashed table, you may have to spend a great deal of time trying to restore a huge amount of binary data.

At hotscripts.com you'll find hundreds of image gallery projects that use PHP and/or MySQL™.

The form generator phpMyEdit can be adapted to display a link to images in your MySQL™ image library. This requires that you also have file named getimage.php which is shown further down on this page. In the form generated by phpMyEdit, link the unique id (Seq_No in this example) to the getimage.php file and comment out the Picture field.

// Excerpt from the above-linked phpMyEdit Image Library form

$opts['fdd']['Seq_No'] = array(
  'name' => 'Seq No',
  'select'   => 'T',
  'options'   => 'ACPVDFL',
  'maxlen'   => 5,
  'default'   => '0',
  'required' => true,
  'sort'     => true
);
$opts['fdd']['Seq_No']['URL'] = 'getimage.php?id=$value';
$opts['fdd']['Seq_No']['URLtarget'] = '_blank';

/* Comment out the Picture column

$opts['fdd']['Picture'] = array(
  'name'     => 'Picture',
  'select'   => 'T',
  'maxlen'   => 16777215,
  'textarea' => array(
    'rows' => '5',
    'cols' => '110'),
  'required' => false,
  'sort'     => true
);

*/

Other considerations

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 an image management table.

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;

If storing HTML IMG SRC tags, it is probably advisable to store the complete URL in the IMG SRC tag.

Lightbox image viewer

Use the Lightbox image viewer to create nice web galleries.

Picasa Web Albums

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

Web Hosting

Hostgator.com offers Fantastico which will automatically install Gallery on your hostgator.com web hosting account. Best web host I've ever used, regular updates to cPanel® , PHP, MySQL.

Web Content Management Systems

The number of web pages that can be implemented using my CMS is almost unlimited. For example, http://www.etnews.org/ is approaching 600 pages of content. Literally thousands of pages could be published, grouped by category and/or sub-category (subject to the aesthetics of navigation links appearing at the side of the page). Side navigation links are generated on-the-fly based on active content found in the currently selected topic or category. New web pages are automatically added to navigation links. Each web page in the CMS database has numberous properties. Pages can be flagged as Hidden while content is under development. Pages can be optionally configured to draw their content from separate programming scripts which access data residing in various database tables, for example displaying reports or displaying a searchable list of products.

This CMS includes user registration and login features. New registrants must complete a registration form which generates an email message to the registrant. The registration message contains an assigned password and a link which the registrant must click in order to activate their registration. This is done in order to ensure a valid email address entered on the registration. After a new user activates their profile, they can log in using the assigned password. Users can change the assigned password after logging in for the first time. Users who forget their user name or password can use built-in forms to retrieve their information via email. Lost passwords can be reset, but not recovered.

Dozens of additional features are configurable within my CMS.

Web Developer Resume

MySQL Cheat Sheet

Web Development with PHP and MySQL