A File-Uploading Example | Core Web Application Development with PHP and MySQL

This section presents a complete example demonstrating file uploads and database interaction. We begin with a form where a user can submit information to create a new account, along with an image file. The data from this form will be submitted to a page that will then create this account and submit the image file to the database. We then create a page to show the new user account details along with the image, which will be fetched from the database (see Figure 25-2).

Figure 25-2. From submitting to viewing a new user account with an avatar image.

Setting Up

To make this sample work, we create a new table in one of our databases (MySQL syntax shown) that contains a minimal set of information about the user and details about the image and its data:

 CREATE TABLE AvatarSample (   user_id INTEGER AUTO_INCREMENT PRIMARY KEY,   user_name VARCHAR(200) NOT NULL UNIQUE,   full_name VARCHAR(200),   password VARCHAR(100),   email VARCHAR(200) NOT NULL,   avatar_image BLOB,   file_type VARCHAR(100),   file_size INTEGER );

We now describe and show the various scripts we need to make this sample work.

The New Account Form

This form is very similar to ones we have seen before. It lets the user enter some information for an account and upload a file:

 <html> <head>   <title>Create New User</title> </head> <body>   <form name="newUser" enctype="multipart/form-data"         action="submit_account.php" method="POST">     User Name:     <input type="text" name="userName"/><br/>     Full Name:     <input type="text" name="fullName"/><br/>     Password:     <input type="password" name="password1"/><br/>     Re-Enter Password:     <input type="password" name="password2"/><br/>     <br/>     Email Address:     <input type="text" name="email"/><br/>     <br/>     User Image:     <input type='file' name='avatarfile'/>     <br/>     <input type="submit" value="Submit"/>   </form> </body> </html>

Note that we are using the new form enctype we first saw in the section "The Client Form." If you find that file uploads do not seem to be working for some reason, remember that it is very frequently a result of having forgotten to add this to the form element.

Creating the New Account

When users click Submit, they are taken to submit_account.php. This script processes the information the user gives us and then submits the data to the database. Of particular interest is the extra information it saves about the uploaded image file. For any image we want to store in our database, we will in fact store the following information:

The image data (binary bits), escaped using the addslashes function so that it is safe for insertion in a SQL query
The type of the image file we received, such as "image/jpeg" or "image/gif"
The size of the image file the user uploaded

We will need all of this information when we try to retrieve the image data later on.

The addslashes function takes whatever data you pass to it and returns that same data with any instances of single quotes ('), double quotes ("), backslashes (\), or NULL bytes escaped with a backslash. Even though we are inserting binary data into our database, we will pass it to our database server using a text SQL query, so we need to be sure that it does not misinterpret characters in the byte stream. Note that we do not need to unescape the data when fetching it.

Our code to insert the user account into the database performs four basic tasks:

It processes the parameters and user data sent, performing any necessary validation.
It looks at the image file sent by the user and gathers the additional information needed, as mentioned previously.
It gets a connection to the database.

It creates a query and inserts the user data into the database.

 <?php require_once('dbinfo.inc'); require_once('errors.inc'); function super_escape_string($in_string, $in_conn) {   $str = $in_conn->real_escape_string($in_string);   return ereg_replace('([%;])', '\\\1', $in_string); } // 1. // we will go light on data validation here a little bit in the // interest of not distracting much from our example. // // look at the normal data. $uname = isset($_POST['userName']) ? $_POST['userName'] : ''; $fname = isset($_POST['fullName']) ? $_POST['fullName'] : ''; $pw1 = isset($_POST['password1']) ? $_POST['password2'] : ''; $pw2 = isset($_POST['password2']) ? $_POST['password2'] : ''; $email = isset($_POST['email']) ? $_POST['email'] : ''; if ($pw1 != $pw2)   throw new PasswordsDontMatchException(); // // did the upload succeed or fail? // if ($_FILES['avatarfile']['error'] == UPLOAD_ERR_OK) {   $ext = strtolower(pathinfo($_FILES['avatarfile']['name'],                              PATHINFO_EXTENSION));   switch ($ext)   {     // 2.     // we will need to know the file type later on to send the     // image to the client browser.     case 'jpg': case 'jpeg':       $fileType = 'image/jpeg';       break;     case 'gif':       $fileType = 'image/gif';       break;     case 'png':       $fileType = 'image/png';       break;     case 'bmp':       $fileType = 'image/bmp';       break;     default:       throw new InvalidFileTypeException($ext);   }   //   // to add binary data to a MySQL database, we need   // to escape it using the addslashes function, as follows:   //   $f = @fopen($_FILES['avatarfile']['tmp_name'], 'r');   if ($f === NULL)      throw new FileAccessException();   $fsize = @filesize($_FILES['avatarfile']['tmp_name']);   $fileData = addslashes(fread($f, $fsize));   fclose($f);   if (intval($_FILES['avatarfile']['size']) > 50000)     throw new FileTooBigException(); } else   throw new FileUploadExeption($_FILES['avatarfile']['error']); // 3. // now create a connection to the db and then make // the parameters safe for inserting. // $conn = @new mysqli(DB_HOST, DB_USR, DB_PW, DB_DB); if (mysqli_connect_errno() !== 0)   throw new DatabaseErrorException(mysqli_connect_error()); $uname = super_escape_string($uname, $conn); $fname = super_escape_string($fname, $conn); $pw = md5($pw1); $email = super_escape_string($email, $conn); // 4. // now construct and execute the query: // $query = <<<EOQUERY INSERT INTO AvatarSample     (user_name, full_name, password, email,      avatar_image, file_type, file_size)     VALUES('$uname', '$fname', '$pw', '$email',            '$fileData', '$fileType', $fileSize) EOQUERY; $results = @$conn->query($query); if ($results === FALSE) {   $msg = $conn->error;   $conn->close();   throw new DatabaseErrorException($msg); } $userid = $conn->insert_id; $conn->close(); // // finally, give the user a way to browse the data. // echo <<<EOM <a href='view_user.php?uid=$userid'>View User Info</a> EOM; ?>

Notice that in this sample, to save on space and keep the sample code reasonably focused, we do not perform full validation on all input data. We do make sure that the input file is of an image type we recognize and is less than 50,000 bytes, but for the rest of the input, we just make sure that it is SQL safe.

The addslashes function needs to know the size of the data it is being given. We determine this by using the filesize function, as follows:

 $f = @fopen($_FILES['avatarfile']['tmp_name'], 'r'); if ($f === NULL)    throw new FileAccessException(); $fsize = @filesize($_FILES['avatarfile']['tmp_name']); $fileData = addslashes(fread($f, $fsize)); fclose($f);

When this section of code is complete, $fileData contains the data that is safe to insert into the SQL query we send to the database server.

Viewing the User Data

After the new account data is submitted, we let the user view it in the view_user.php script. This file just loads the information for the user and displays it. The big surprise in this script is that we do not load the image data just yet. Instead, we present a URL to the image data in an XHTML img tag, as follows:

 <img border='0' src='/books/3/445/1/html/2/get_img.php?uid=XX'/>

We show the implementation of get_img.php in the section "Fetching the Image from the Database."

The view_user.php script is as follows:

 <?php require_once('dbinfo.inc'); require_once('errors.inc'); function emitUserInfo($in_dbrow) {   echo <<<EOINFO   <b>User Name:</b> {$in_dbrow['user_name']}<br/>   <b>Full Name:</b> {$in_dbrow['full_name']}<br/>   <b>Email Address:</b> {$in_dbrow['email']}<br/>   <b>Avatar:</b>  <img border='0' src='/books/3/445/1/html/2/get_img.php?uid={$in_dbrow['user_id']}'/>   <br/> EOINFO; } // // make sure we got a user ID from somewhere. // if (!isset($_GET) and !isset($_GET['uid']))   die('need a user id'); $uid = intval($_GET['uid']); // // connect to the db. // $conn = @new mysqli(DB_HOST, DB_USR, DB_PW, DB_DB); if (mysqli_connect_errno() !== 0)   throw new DatabaseErrorException(mysqli_connect_error()); // // get the record. // $query = <<<EOQUERY SELECT user_id, user_name, full_name, email FROM AvatarSample   WHERE user_id = $uid EOQUERY; $results = @$conn->query($query); if ($results === FALSE) {   $msg = $conn->error;   $conn->close();   throw new DatabaseErrorException($msg); } while (($row = @$results->fetch_assoc()) !== NULL) {   if ($row['user_id'] == $uid)   {     emitUserInfo($row);     break;   } } $results->close(); $conn->close(); ?>

The output of the emitUserInfo function looks something like this:

 <b>User Name:</b> Jimmy<br/> <b>Full Name:</b> Jimmy the Enforcer<br/> <b>Email Address:</b> Jimmy@enforcersrus.com<br/> <b>Avatar:</b> <img border='0' src='/books/3/445/1/html/2/get_img.php?uid=3'/> <br/>

The client browser then duly calls get_img.php to fetch the image for the user.

Fetching the Image from the Database

The final piece of the puzzle is the script to fetch the image from the database. The data is sent directly to the client as the output stream of the script, while we simultaneously use HTTP headers (via the header function) to indicate what we are sending the client and how large the data is. Our get_img.php script looks like this:

 <?php require_once('dbinfo.inc'); require_once('errors.inc'); // find the user ID we're going to use. if (!isset($_GET) and !isset($_GET['uid']))   die('need a user id'); $uid = intval($_GET['uid']); // connect to the db. $conn = @new mysqli(DB_HOST, DB_USR, DB_PW, DB_DB); if (mysqli_connect_errno() !== 0)   throw new DatabaseErrorException(mysqli_connect_error()); // get the record. $query = <<<EOQUERY SELECT user_id, avatar_image, file_type, file_size    FROM AvatarSample   WHERE user_id = $uid EOQUERY; $results = @$conn->query($query); if ($results === FALSE) {   $msg = $conn->error;   $conn->close();   throw new DatabaseErrorException($msg); } while (($row = @$results->fetch_assoc()) !== NULL) {   if ($row['user_id'] == $uid)     break; } $results->close(); $conn->close(); if ($row !== NULL) {   // send the headers and the image data.   header("Content-type: {$row['file_type']}");   header("Content-length: {$row['file_size']}");   header("Content-Disposition: attachment; filename=$uid.jpg");   header("Content-Description: PHP Generated Data");   echo $row['avatar_image']; } ?>

Back when we submitted the data for the user, we stored, in addition to the user information and image file data, the size and type of this file. We use that information now as part of the headers we send back to the client:

 header("Content-type: {$row['file_type']}"); header("Content-length: {$row['file_size']}"); header("Content-Disposition: attachment; filename=$uid.jpg"); header("Content-Description: PHP Generated Data");

With these lines of code, we are indicating that we are sending something like an "image/jpeg" as output, with the indicated file size, and giving it a reasonable filename in case the user wants to save it in his browser. The last line of code we execute is to send the binary data for the file to the client:

 echo $row['avatar_image'];

With this, we have successfully stored binary data in our database and written scripts to retrieve it later on. Note that these scripts operate similarly to the way actual web servers implement file downloads.