ONLamp.com
oreilly.comSafari Books Online.Conferences.

advertisement


Web Database Applications with PHP & MySQL

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.

Pages: 1, 2

Next Pagearrow





Sponsored by: