ONLamp.com: Managing Images With a Web Database Application

Published on ONLamp.com (http://www.onlamp.com/)
http://www.onlamp.com/pub/a/onlamp/2002/05/09/webdb2.html
See this if you're having trouble printing code examples

Managing Images With a Web Database Application

by Hugh E. Williams, coauthor of Web Database Applications with PHP & MySQL
05/09/2002

Web developers often need to store images, sounds, movies, and documents in a database and deliver these to users. In this article, I'll show you how to develop a simple Web database application that allows users to upload and retrieve images, but can easily be adapted to storing files of any type.

To develop the application, I'll use PHP and MySQL, the most popular platform for developing Web database applications. I'll assume you're already familiar with PHP and MySQL basics. If you're not, then David Lane's and my book, Web Database Applications with PHP & MySQL, will give you an introduction, as well as the theory and practice of developing Web database applications with PHP and MySQL. You might also find my previous article that introduces tips for developing Web database applications to be useful background reading.

Creating an Image Database

Let's start off by creating a MySQL database to store our uploaded images. I'll keep the database simple: it only needs one table that stores the image, a unique ID for the image, a short description, the MIME type of the image (I'll explain why later), and a description of the MIME type.

You can create the database by using the MySQL command line monitor and interpreter. This is usually started by running:

mysql -uusername -ppassword

at the shell or command prompt. You should replace username with your username and password with your password.

Once you've got it running, here's the MySQL and SQL statements to create the database you need:

create database files;
use files;

CREATE TABLE files (
  id int(11) NOT NULL auto_increment,
  shortName varchar(50) default NULL,
  mimeType varchar(30) default NULL,
  mimeName varchar(50) default NULL,
  fileContents blob,
  PRIMARY KEY  (id)
) TYPE=MyISAM;

Viewing Images in the Database

Before I cover how to upload and store images in a database, let's start with how to retrieve them and show them to the user. I've written a short script -- which I've saved in the file index.php -- that retrieves all the image IDs, short descriptions, and descriptions of MIME types from our files database table. Here's the script:

<!DOCTYPE HTML PUBLIC 
  "-//W3C//DTD HTML 4.0 Transitional//EN"
  "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
   <title>Browse Upload Files</title>
</head>
<body bgcolor="white">

<?php
  include 'db.inc';
  
  $query = "SELECT id, shortName, mimeName FROM files";

  if (!($connection = @ mysql_pconnect($hostName, 
                                    $username,
                                    $password)))
     showerror();

  if (!mysql_select_db("files", $connection))
     showerror();
        
  if (!($result = @ mysql_query ($query, $connection)))
     showerror();
?>
    <h1>Image database</h1> 

    <h3>Click <a href="insert.php">here</a> to 
upload an image.</h3>
<?php 
  require 'disclaimer';

  if ($row = @ mysql_fetch_array($result))
  {
?>

    <table>
    <col span="1" align="right">
    <tr>
       <th>Short description</th>
       <th>File type</th>
       <th>Image</th>

    </tr>
<?php
   do 
   {
?>
    <tr>
       <td><?php echo "{$row["shortName"]}";?></td>         
       <td><?php echo "{$row["mimeName"]}";?></td>
       <td><?php echo "<img src=\"view.php?file={$row["id"]}\">";?></td>
    </tr>
<?php
   } while ($row = @ mysql_fetch_array($result));
?>
    </table>
<?php
  } // if mysql_fetch_array()
  else
     echo "<h3>There are no images to display</h3>\n";
?>
</body>
</html>

The script displays short descriptions of each image, descriptions of the image types, and the image itself in an HTML table. In most other HTML pages, images are displayed by quoting the filename of the image -- for example, picture.gif -- as the src attribute of an <img> tag. However, in this case, the src attribute is another PHP script, view.php, that does the work of retrieving the image from the database.

Here's how the view.php script is called:

<?php echo "<img src=\"view.php?file={$row["id"]}\">";?>

If the image being viewed has an ID of 1, then the output of the fragment is as follows:

<img src="view.php?file=1">

I'll explain view.php in the next section.

You'll also notice that my script includes an include file, db.inc. This file is used to store a few common functions and the settings to access the MySQL DBMS. The function clean() preprocesses user input so that special characters can't be used to manipulate the behavior of the system. The function showerror() reports any MySQL errors that occur while using the application.

Here's the contents of the db.inc file:

<?php

// These are the DBMS credentials
$hostName = "localhost";
$username = "fred";
$password = "shhh";

// Show an error and stop the script
function showerror()
{
   if (mysql_error())
      die("Error " . mysql_errno() . " : " . mysql_error());
   else
      die("Could not connect to the DBMS");
}

// Secure the user data by escaping characters 
// and shortening the input string
function clean($input, $maxlength)
{
  $input = substr($input, 0, $maxlength);
  $input = EscapeShellCmd($input);
  return ($input);
}

?>

Delivering an Image From a Database

I've authored a script view.php that delivers an image file from the files database. As I explained in the last section, the script is listed as the src attribute of <img> tags throughout the application. This means that whenever an image in the application needs to be rendered by a user's browser, the script is requested to produce the image as output.

Here's the script:

<?php
  include 'db.inc';

  $file = clean($file, 4);

  if (empty($file))
     exit;

  if (!($connection = @ mysql_pconnect($hostName,
                                       $username,
                                       $password)))
     showerror();

  if (!mysql_select_db("files", $connection))
     showerror();

  $query = "SELECT mimeType, fileContents FROM files 
            WHERE id = $file";

  if (!($result = @ mysql_query ($query,$connection)))
     showerror();  

  $data = @ mysql_fetch_array($result);

  if (!empty($data["fileContents"]))
  {
    // Output the MIME header
     header("Content-Type: {$data["mimeType"]}");
    // Output the image
     echo $data["fileContents"];
   }
?>

The database functionality is straightforward: the script retrieves the image that has an ID that matches the file parameter passed to the script. So, for example, if the script is requested by the following HTML fragment:

<img src="view.php?file=5">

then the image with id=5 is retrieved from the files table.

The script outputs the image using two lines of code:

// Output the MIME header
header("Content-Type: {$data["mimeType"]}");
// Output the image
echo $data["fileContents"];

The first line outputs an HTTP header that indicates the MIME type of the image, and the second line outputs the image itself. The HTTP header is used by the browser to determine how it handles the image, and it is the reason we need to store the MIME type in the database. For most image MIME types -- such as GIFs or JPEGs -- a graphical browser will simply display the image that follows. I'll explain how we figure out the MIME type of the file in the next section.

Uploading and Storing Images in a Database

So far I've shown you how images are delivered from a MySQL database. In this section, I'll show you how to upload and store images in the database. The upload process uses two scripts: first, I've authored a script that shows the user an HTML form for uploading an image and also inserts the new image into the database; and, second, I've created a receipt page that confirms the upload has occurred.

The first script is stored in the file insert.php and contains the following code:

<?php
  include 'db.inc';

  if (empty($short) || empty($userfile))
  {
?>
    <!DOCTYPE HTML PUBLIC 
               "-//W3C//DTD HTML 4.0 Transitional//EN"
               "http://www.w3.org/TR/html4/loose.dtd">
    <html>
    <head>
      <title>Upload an Image File</title>
    </head>
    <body bgcolor="white">
    <form method="post" action="insert.php" enctype="multipart/form-data">
    <h1>Upload an Image File</h1> 
    <h3>Please fill in the details below to upload your file. 
    Fields shown in <font color="red">red</font> are mandatory.</h3>
    <table>
    <col span="1" align="right">

    <tr>
       <td><font color="red">Short description:</font></td>
       <td><input type="text" name="short" size=50></td>
    </tr>

    <tr>    
       <td><font color="red">File:</font></td>
       <td><input name="userfile" type="file"></td>
    </tr>

    <tr>
          <td><input type="submit" value="Submit"></td>
    </tr>
    </table>
    <input type="hidden" name="MAX_FILE_SIZE" value="30000">
    </form>
    <h3>Click <a href="index.php">here</a> to browse the images instead.</h3>
    </body>
    </html>
<?php    
  }
  else 
  {
     $short = clean($short, 50);
     $userfile = clean($userfile, 50);

     if (!($connection = @ mysql_pconnect($hostName, 
                                         $username, 
                                         $password)))
        showerror();

     if (!mysql_select_db("files", $connection))
        showerror();

     // Was a file uploaded?
     if (is_uploaded_file($userfile))
     {
       
       switch ($userfile_type)
       {
          case "image/gif";       
             $mimeName = "GIF Image";
             break;
          case "image/jpeg";          
             $mimeName = "JPEG Image";
             break;
          case "image/png";       
             $mimeName = "PNG Image";
             break;
          case "image/x-MS-bmp";       
             $mimeName = "Windows Bitmap";
             break;
          default: 
             $mimeName = "Unknown image type";
       }
   
       // Open the uploaded file
       $file = fopen($userfile, "r");
    
       // Read in the uploaded file
       $fileContents = fread($file, filesize($userfile)); 

       // Escape special characters in the file
       $fileContents = AddSlashes($fileContents);
     }  
     else
       $fileContents = NULL;

     $insertQuery = "INSERT INTO files VALUES (NULL, \"{$short}\",
         \"{$userfile_type}\", \"{$mimeName}\", \"{$fileContents}\")";

     if ((@ mysql_query ($insertQuery, $connection)) 
         && @ mysql_affected_rows() == 1)
       header("Location: receipt.php?status=T&file="
         . mysql_insert_id($connection));
     else
       header("Location: receipt.php?status=F&file=" 
         . mysql_insert_id($connection));  
  } // if else empty()
?>

The first section of insert.php shows the user a form used for entering a description of the image and selecting the file to be uploaded. Files are uploaded by using the multipart/form-data encoding type. The encoding type is controlled by the <form> tag:

    <form method="post" action="insert.php" enctype="multipart/form-data">

Multipart encoding allows the data entered by the user to be sent in a message that contains one part per <input> element. In my example, this means that the file description, the image file, and the hidden field that contains the maximum file upload size are sent separately.

The file name of the image file is entered into an <input> of type="file" which, when rendered by the browser, shows the user a button labeled "Browse..." that can be used to browse files using a file dialog box.

The second section of insert.php is executed after the user presses "Submit," having filled in a description and selected a file. The code itself is fairly straightforward, but it does contain a few subtleties. The most important features of the code are:

I've tested if $userfile -- the variable with the same name as the "file" <input> in the <form> -- is an uploaded file by using the PHP library function is_uploaded_file(). This is important: if you don't check, then you're open to an attack from a user who maliciously supplies a value for $userfile that's the name of a file that exists on the Web server.
The MIME type of the uploaded file is automatically stored in $userfile_type. Depending on the type, I've determined a description of the image type that I later store in the database. In a real application, you might extend the list of MIME types further so that all practical image types (or other file types) are included in the switch statement.
I've read in the file that was uploaded by using fopen() and then fread(). I've then escaped any characters that can't be stored directly in a MySQL table by using the PHP library function AddSlashes().

The code that inserts the image and its details in the database is just the same as any other INSERT statement. After the insert, an HTTP header is issued that redirects the user's browser to the receipt page that I'll discuss next. The rationale for this redirection is to solve the reload problem discussed in my previous article.

Web Database Applications with PHP, and MySQL

The Receipt Page

The last component of the application is the receipt page that I've stored in the file receipt.php. The receipt page is similar to index.php except that it displays only the details of the image just inserted, or an error message if there was a database problem. Here's the code:

    <!DOCTYPE HTML PUBLIC 
               "-//W3C//DTD HTML 4.0 Transitional//EN"
               "http://www.w3.org/TR/html4/loose.dtd">
    <html>
    <head>
      <title>File Insert Receipt</title>
    </head>
    <body bgcolor="white">

<body bgcolor="white">
<?php
  include 'db.inc';
  
  $status = clean($status, 1);
  $file = clean($file, 5);

  // did the insert operation succeed?
  switch ($status)
  {
  case "T":
     // Yes, insert operation succeeded. 
     // Show details of the new file.

     $query = "SELECT shortName, mimeName FROM files WHERE id = $file";

     if (!($connection = @ mysql_pconnect($hostName, 
                                       $username,
                                       $password)))
        showerror();

     if (!mysql_select_db("files", $connection))
        showerror();
        
     // Run the query on the DBMS
     if (!($result = @ mysql_query ($query, $connection)))
        showerror();

     if ($row = @ mysql_fetch_array($result))
     {
?>
    <h1>File Insert Receipt</h1> 
    <h3>The following file was successfully uploaded:
    <table>
    <col span="1" align="right">
    <tr>
       <td><font color="red">Short description:</font></td>
       <td><?php echo "{$row["shortName"]}";?></td>
    </tr>

    <tr>
       <td><font color="red">File type:</font></td>
       <td><?php echo "{$row["mimeName"]}";?></td>
    </tr>

    <tr>
       <td><font color="red">File:</font></td>
       <td><?php echo "<img src=\"view.php?file={$file}\">";?></td>
    </tr>
    </table>
<?php
     } // if mysql_fetch_array()

     break;

  case "F":
     // No, insert operation failed
     // Show an error message
     echo "The file insert operation failed.";
     echo "<br>Contact the system administrator.";

     break;

  default:
     // User did not provide a status parameter
     echo "You arrived unexpectedly at this page.";          
  } // end of switch
?>
<h3>Click <a href="insert.php">here</a> to upload another image.</h3>
<h3>Click <a href="index.php">here</a> to browse the uploaded images.</h3>
</body>
</html>

Tricks, Traps, and Hints

The techniques I've discussed work well for small files such as GIF images. If you're planning on uploading much larger files into a MySQL database -- such as applications, PDF documents, or movies -- then there are a few additional configuration steps required:

As in my insert.php, an additional hidden <form> field must be added to specify the maximum allowed upload file size, such as <input type="hidden" name="MAX_FILE_SIZE" value="100000">.
The memory limit of a PHP script should be greater than the maximum file size. This can be set by adjusting the memory_limit parameter in the php.ini file, usually found in /usr/local/lib/ in Unix installations.
The maximum file upload size should be set by modifying the upload_max_filesize parameter in the php.ini file.
The maximum POST size should be set to greater than the maximum file size by modifying the post_max_size parameter in the php.ini file.
The maximum execution time for a PHP script should be set to an appropriate value to allow the upload to complete. The default value is 30 seconds. The parameter can be changed by modifying the max_execution_time parameter in the php.ini file.

The Web server must be restarted after any changes, so that the php.ini configuration file is re-read. This can usually be done in a Unix installation by executing the command apachectl restart in the directory /usr/local/apache/bin/.

As I discussed earlier, the file uploaded in my example is associated with a <form> widget:

<td><input name="userfile" type="file"></td>

The result of associating the file with the name userfile is that several variables are available at the Web server:

$userfile -- the Web server file name of file that was uploaded.
$userfile_name -- the original file name of the file on the client browser machine.
$userfile_size -- the size of the file.
$userfile_type -- the MIME type of the file.

Finding Out More

I've presented a simple file management system. You can test the final application and download the source from Dave's and my book Web site. An obvious extension is to manipulate the images as they're stored, so that there are thumbnails available, or so that images have the same dimensions. For this task, you'll need PHP's image functions and the underlying GD Graphics Library.

There's a lot more that PHP and MySQL can do, and our new book, Web Database Applications with PHP & MySQL, covers much of the theory and practice. There's also plenty of information on the Web. Have a look at the PHP Web site as a starting point and, in particular, the Links section.

Hugh E. Williams is a software design engineer at Microsoft's Windows Live Search in Redmond, WA. Previously, he was the Associate Professor in Information Retrieval at RMIT University in Melbourne, Australia.

Return to the ONLamp.com.