ONLamp.com
oreilly.comSafari Books Online.Conferences.

advertisement


Managing Images With a Web Database Application
Pages: 1, 2

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.

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.





Sponsored by: