Upload images to a MySQL database (PHP)

Dreamweaver's PHP server behaviors don't handle file uploads, so you need to do most of the coding yourself. However, to make the SQL query easier to handle, you can create a form with hidden fields representing the columns in your database. There's no need to assign any values to the hidden fields. They're used simply to build the Insert Record server behavior code.

The attached zip file (upload_to_db.zip) contains the code at different stages of development.

Getting ready

This recipe assumes that you have a database table called images with the following columns: image_id (primary key), filename, mimetype, caption, image, width, and height.

The filename and caption columns should be of the VARCHAR data type.

The mimetype column should be an ENUM data type with the following values: 'image/png','image/jpeg','image/gif'. You need to store this value so the browser knows how to display the image when it's retrieved from the database.

The image column should be a BLOB (binary large object) data type.

The width and height columns should both be an INT data type.

Create a form in Dreamweaver with a file field, a text input field for the caption, and hidden fields for filename, mimetype, width, and height (see upload_to_db1.php). Also add the following code inside the form in Code view anywhere before the file field:

<input type="hidden" name="MAX_FILE_SIZE"
value="<?php echo MAX_FILE_SIZE; ?>" />

Apply an Insert Record server behavior to the page. As long as you have created the hidden fields, Dreamweaver should automatically create the necessary code for you to adapt. See upload_to_db2.php.

Adapting the Dreamweaver code

The first task is to define the constant MAX_FILE_SIZE to limit the maximum size of files being uploaded. The value needs to be in bytes (1KB = 1024 bytes). To limit the maximum to 50KB, add the following code right at the top of the page (adjust the value to your own requirements):

<?php define ('MAX_FILE_SIZE', 1024 * 50); ?>

Now scroll down to the main part of the Insert Record server behavior code, which looks like this:

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] ==
"uploadImage")) {
  $insertSQL = sprintf("INSERT INTO images (filename, mimetype,
caption, image, width, height) VALUES (%s, %s, %s, %s, %s, %s)",
      GetSQLValueString($_POST['filename'], "text"),
      GetSQLValueString($_POST['mimetype'], "text"),
      GetSQLValueString($_POST['caption'], "text"),
      GetSQLValueString($_POST['image'], "text"),
      GetSQLValueString($_POST['width'], "int"),
      GetSQLValueString($_POST['height'], "int"));

  mysql_select_db($database_testConn, $testConn);
  $Result1 = mysql_query($insertSQL, $testConn) or
die(mysql_error());
}

This is the section that needs to be adapted. Create some blank lines before the line that begins with $insertSQL, and insert the following code:

// make sure it's a genuine file upload
if (is_uploaded_file($_FILES['image']['tmp_name'])) {
  // replace any spaces in original filename with underscores
  $filename = str_replace(' ', '_', $_FILES['image']['name']);
  // get the MIME type $mimetype = $_FILES['image']['type'];
  if ($mimetype == 'image/pjpeg') {
    $mimetype= 'image/jpeg';
  }
  // create an array of permitted MIME types
  $permitted = array('image/gif', 'image/jpeg', 'image/png');

 // upload if file is OK
 if (in_array($mimetype, $permitted)
     && $_FILES['image']['size'] > 0
     && $_FILES['image']['size'] <= MAX_FILE_SIZE) {
   switch ($_FILES['image']['error']) {
     case 0:
       // get the file contents
       $image = file_get_contents($_FILES['image']['tmp_name']);
       // get the width and height
       $size = getimagesize($_FILES['image']['tmp_name']);
       $width = $size[0];
       $height = $size[1]

This checks that the file is a genuine file upload, replaces any spaces in the file name with underscores, gets the MIME type, width and height, and stores the image data in $image. With the exception of the caption, all the values are now stored in ordinary variables, not the $_POST array. So, you need to modify the SQL query created by Dreamweaver like this:

$insertSQL = sprintf("INSERT INTO images (filename, mimetype,
caption, image, width, height) VALUES (%s, %s, %s, %s, %s, %s)",
      GetSQLValueString($filename, "text"),
      GetSQLValueString($mimetype, "text"),
      GetSQLValueString($_POST['caption'], "text"),
      GetSQLValueString($image, "text"),
      GetSQLValueString($width, "int"),
      GetSQLValueString($height, "int"));

The rest of the script goes after the line beginning with $Result1. It's important to make sure all the curly braces balance correctly, so the following code extract shows the closing PHP tag immediately above the DOCTYPE declaration:

          if ($Result1) {
            $result = "$filename uploaded successfully.";
          } else {
            $result = "Error uploading $filename. Please try
again.";
          }
          break;
        case 3:
        case 6:
        case 7:
        case 8:
          $result = "Error uploading $filename. Please try again.";
          break;
        case 4:
          $result = "You didn't select a file to be uploaded.";
      }
    } else {
      $result = "$filename is either too big or not an image.";
    }
  }
}
?>

Finally, add the following code block to the body of the page to display the result of the upload operation:

<?php
// if the form has been submitted, display result
if (isset($result)) {
  echo "<p><strong>$result</strong></p>";
}
?>

You can also remove the hidden fields for filename, mimetype, width, and height. The final code is in upload_to_db3.php.

For details of how to display your images when they are stored like this, see Display an image stored in a database (PHP).