PHP and MySQL: The Missing Manual (2011)
Part 3. From Web Pages to Web Applications
Chapter 9. Binary Objects and Image Loading
Right now, you’ve got images on a file system, and paths to those images in a database. Then, in your web application, you convert that file system path to a web path, and display the image. This works, and actually works pretty well. In fact, you could run with that solution and likely never have any real issues…and then again, you might have a huge issue that crops up next week.
The real downside of this approach is that you don’t have a self-contained solution. Images are on the file system, paths are in the database, and then you need some PHP to convert from the location of the image on your server to a path that users’ browsers can interpret correctly. So you’ve created a real connection—sometimes called a tight coupling—between your file system, your PHP, and your database.
So how do you make things more self-contained? You’d have to take these pieces of information and put them all in one place. Obviously, you’re committed to a database, so that becomes the logical place to consolidate your information. Instead of some of your information going into your database, it all goes into your database.
In this approach, you’d actually take the uploaded image and put it in your database, rather than just storing a reference to your image. But to do that, there’s a lot more work to be done: you need not just a new column in your users table, but an entirely new table; you need a new data type; and you need more than just the SELECT and INSERT queries you’ve been using so far. But you may very well need just the type of solution, so time to get busy.
Storing Different Objects in Different Tables
Up until now, you’ve been working with one table: users. That’s because you’ve been working with a single thing: a representation of one of your users. Everything in that table—the first and last name, the email address, and the Facebook URL and Twitter handle—are parts of that that user. Another way to think about it is that everything in the users table describes a user.
So now you come to the idea of storing an image—and information about that image, something you’ll see is important when you go to storing an entire image within the database. This content no longer describes a user. In fact, while an image is related to a user—it’s the image that a user wants to display when their profile is viewed—it’s an object in its own right. It’s its own “thing.”
And once you come to that point—when you realize that you’re dealing with a new object—then you need to create a new table. So you’re going to create a new table, called images, that’s going to not only store a user’s image, but several key details about that image.
NOTE
If you think about it, this table is exactly the same as the users table. That table stores not just the user’s name (equivalent to the image itself) but information about that user, such as their email address and Twitter handle. In the same way, you’re going to store information about the image that helps define that image for when you need to use it.
§ Image ID. Identifies this image, much like you have a user_id in the users table. It will also let you relate an image to the users table a bit later.
§ Image name. Even though you’re storing the data of the image, you still need a name by which you can refer to that image.
§ MIME type. This information is important for telling a web server whether it needs to display a JPG, or a GIF, or PNG, or something else entirely.
§ File size. Used by the browser for displaying the image.
§ Image data. The raw bits and bytes that are turned into pixels and colors.
Translate this into SQL, and you get a new CREATE statement:
CREATE TABLE images (
image_id int AUTO_INCREMENT PRIMARY KEY,
filename varchar(200) NOT NULL,
mime_type varchar(50) NOT NULL,
file_size int NOT NULL,
image_data mediumblob NOT NULL
);
These items are all pretty straightforward, with the exception of a new column type: mediumblob. Hence, there are a few other blob types, and there are:
§ tinyblob: Stores objects up to 256 bytes.
§ blob: You can store objects up to 65 KB (kilobytes) in a blob column.
§ mediumblob: Allows for up to 16MB of data.
§ longblob: Here’s the big one. You can store 4GB of data in a longblob column.
Blob stands for binary large object. It’s a column built just for the type of information that makes up an image: information that’s not a number or a string, but binary information.
DESIGN TIME: PLANNING FOR GROWTH AND DESCRIBING YOUR DATA
There’s a fair bit of disagreement about which blob type you should use for a given column. Some argue that you should virtually always use longblob, while others argue that you should figure out the data you’re aware of, and use the blob size that covers that size, and nothing more.
For those who argue for always using longblob, the thinking is that you’re planning ahead. Because your database uses space as its needed by your actual information—and not the maximum size of a column—a longblob holding a 2MB image takes up just as much, and only as much, space as a mediumblob holding a 2MB image. If you use longblob all the time, you never have to change your column type as your storage needs change.
On the other hand, if you’re only allowing images that are 2MB or smaller, then you best describe your data by using medium-blob. You’re doing more than just choosing an arbitrary type; you’re providing information about what goes in a column.
It’s not a good idea to make everything a varchar(255) if you are only storing a first name because there’s no first name that long; you lose a chance to say something about your data with that approach. The same is true for using a longblob if (and this is an important if) you’ve decided that you’re only accepting images up to a size that would fit in a mediumblob.
Go ahead and create this table. Make sure it’s in the same database as users. You should now be able to see both these tables in your database:
mysql> USE bmclaugh;
Database changed
mysql> SHOW tables;
+------------------------------------+
| Tables_in_bmclaugh |
+------------------------------------+
| images |
| users |
+------------------------------------+
2 rows in set (0.00 sec)
Inserting a Raw Image into a Table
It’s time to revisit create_user.php. You’re going to use a lot of your code, but there are also some changes to be made. Specifically, all the checks to ensure your user uploaded a valid image, that no errors were generated by the server or PHP, and that the file is an image via getimagesize are just fine.
Where things change is in the section of code that you used to move the temporary image into a final location. In this approach, the final location is the images table, so that code has to be replaced.
Here’s the create_user.php script with the code you no longer need removed.
NOTE
You should make a backup of create_user.php before you start making changes. Consider copying it to create_user.php.bak or something similar, so if you want to go back to storing just an image’s path, you can easily do that.
<?php
require_once '../scripts/app_config.php';
require_once '../scripts/database_connection.php';
// The errors array and variables related to images stay the same
$upload_dir = SITE_ROOT . "uploads/profile_pics/";
$image_fieldname = "user_pic";
// Potential PHP upload errors
$php_errors = array(1 => 'Maximum file size in php.ini exceeded',
2 => 'Maximum file size in HTML form exceeded',
3 => 'Only part of the file was uploaded',
4 => 'No file was selected to upload.');
$first_name = trim($_REQUEST['first_name']);
$last_name = trim($_REQUEST['last_name']);
$email = trim($_REQUEST['email']);
$bio = trim($_REQUEST['bio']);
$facebook_url = str_replace("facebook.org", "facebook.com", trim($_
REQUEST['facebook_url']));
$position = strpos($facebook_url, "facebook.com");
if ($position === false) {
$facebook_url = "http://www.facebook.com/" . $facebook_url;
}
$twitter_handle = trim($_REQUEST['twitter_handle']);
$twitter_url = "http://www.twitter.com/";
$position = strpos($twitter_handle, "@");
if ($position === false) {
$twitter_url = $twitter_url . $twitter_handle;
} else {
$twitter_url = $twitter_url .
substr($twitter_handle, $position + 1);
}
// Make sure we didn't have an error uploading the image
($_FILES[$image_fieldname]['error'] == 0)
or handle_error("the server couldn't upload the image you selected.",
$php_errors[$_FILES[$image_fieldname]['error']]);
// Is this file the result of a valid upload?
@is_uploaded_file($_FILES[$image_fieldname]['tmp_name'])
or handle_error("you were trying to do something naughty. " .
"Shame on you!",
"Uploaded request: file named " .
"'{$_FILES[$image_fieldname]['tmp_name']}'");
// Is this actually an image?
@getimagesize($_FILES[$image_fieldname]['tmp_name'])
or handle_error("you selected a file for your picture that " .
"isn't an image.",
"{$_FILES[$image_fieldname]['tmp_name']} " .
"isn't a valid image file.");
// Name the file uniquely
$now = time();
while (file_exists($upload_filename = $upload_dir . $now .
'-' .
$_FILES[$image_fieldname]['name'])) {
$now++;
}
// Remove the code that used move_uploaded_file to move the temporary image
// Remove the column name and value for user pics.
$insert_sql = "INSERT INTO users (first_name, last_name, email, " .
"bio, facebook_url, twitter_handle) " .
"VALUES ('{$first_name}', '{$last_name}', '{$email}', " .
"'{$bio}', '{$facebook_url}', '{$twitter_handle}');";
// Insert the user into the database
mysql_query($insert_sql);
// Redirect the user to the page that displays user information
header("Location: show_user.php?user_id=" . mysql_insert_id());
exit();
?>
So this code is substantially the same. The big change is that now you need a new INSERT statement, and this statement doesn’t insert into users, but into images.
Here’s the beauty of this solution, though: you can get every bit of the information you need to put into images from the $_FILES array (which is actually an array of arrays). From here, writing the code is a piece of cake:
$insert_sql = "INSERT INTO users (first_name, last_name, email, " .
"bio, facebook_url, twitter_handle) " .
"VALUES ('{$first_name}', '{$last_name}', '{$email}', " .
"'{$bio}', '{$facebook_url}', '{$twitter_handle}');";
// Insert the user into the database
mysql_query($insert_sql);
// Insert the image into the images table
$image = $_FILES[$image_fieldname];
$image_filename = $image['name'];
$image_info = getimagesize($image['tmp_name']);
$image_mime_type = $image_info['mime'];
$image_size = $image['size'];
$image_data = file_get_contents($image['tmp_name']);
$insert_image_sql = "INSERT INTO images " .
"(filename, mime_type, file_size, image_data) " .
"VALUES ('{$image_filename}', '{$image_mime_type}', " .
"'{$image_size}', '{$image_data}');";
mysql_query($insert_image_sql);
// Redirect the user to the page that displays user information
header("Location: show_user.php?user_id=" . mysql_insert_id());
?>
There’s a lot going on here, and some of it’s flat-out confusing, so take this piece by piece.
First, this code creates a new $image variable that’s just for convenience:
$image = $_FILES[$image_fieldname];
This line makes it easier to deal with all the properties of an image. You don’t have to keep typing $_FILES[$image_fieldname] over and over. This step isn’t necessary, but it does make things much easier.
Next, you can get the name of the image from this array:
$image_filename = $image['name'];
getimagesize Doesn’t Return a File Size
Here’s where things start to get a little weird. getimagesize actually doesn’t return a numeric file size of the uploaded image. Rather, it returns an array of information about the image, such as its MIME type (which you need), and the height and width of the image that you might use to display the image in an HTML page (which you currently don’t need).
So you might think you should do something like this:
$image_size = getimagesize($image['tmp_name']);
But that’s a problem on two counts: getimagesize returns an array, not a size; and the sizes that getimagesize returns in that array are height and width, not file size.
What you do need from the returned array, though, is the MIME type:
$image_info = getimagesize($image['tmp_name']);
$image_mime_type = $image_info['mime'];
Now, you still need the actual file size of the uploaded image. You get that from a property on the original image-related array:
$image_size = $image['size'];
file_get_contents Does What You Think It Does
Sometimes a function’s name is a bit misleading, like getimagesize. Other times, a function is perfectly named; that’s the case with file_get_contents. This function gets you the data in binary form from an object, which is just what you want for the image_data column in your images table:
$image_data = file_get_contents($image['tmp_name']);
INSERT the Image
Last but not least, you just need to build the INSERT query and run it:
$insert_image_sql = "INSERT INTO images " .
"(filename, mime_type, file_size, image_data) " .
"VALUES ('{$image_filename}', '{$image_mime_type}', " .
"'{$image_size}', '{$image_data}');";
mysql_query($insert_image_sql);
WARNING
Hold off on running this code! Or, if you do, get ready for some weird errors. There are problems here, lurking in the dark corners of how MySQL handles data. So get your code to this point, but keep reading before you think you’ve done something wrong.
Your Binary Data Isn’t Safe to Insert…Yet
So things look pretty good…but if you run the code from the previous section, you’ll have some issues. First and foremost, that binary data has all sorts of weird characters on which PHP and MySQL are going to choke. There’s actually always the possibility of running into characters that are a problem, but that’s especially true—and almost always the case—when you’re dealing with binary data.
Once again, though, there’s a utility function for that.
NOTE
You’ve probably noticed that at almost every turn, there’s a PHP utility function. That’s one of the advantages of a language that’s fairly mature. Well into version 4 and 5, PHP has settled and developed a pretty robust library of handy functions like getimagesize and the one you’re about to use, mysql_real_escape_string.
mysql_real_escape_string escapes any special characters in the string you hand it. So you can pass in your $image_data, and then pass the result of mysql_real_escape_string to mysql_query through your INSERT statement. In fact, it’s not a bad idea to use this function on any string data you pass in to MySQL:
$insert_sql = "INSERT INTO users (first_name, last_name, email, " .
"bio, facebook_url, twitter_handle) " .
"VALUES ('{mysql_real_escape_string($first_name)}', " .
"'{mysql_real_escape_string($last_name)}', " .
"'{mysql_real_escape_string($email)}', " .
"'{mysql_real_escape_string($bio)}', " .
"'{mysql_real_escape_string($facebook_url)}', " .
"'{mysql_real_escape_string($twitter_handle)}');";
// Insert the user into the database
mysql_query($insert_sql);
// Insert the image into the images table
$image = $_FILES[$image_fieldname];
$image_filename = $image['name'];
$image_info = getimagesize($image['tmp_name']);
$image_mime_type = $image_info['mime'];
$image_size = $image['size'];
$image_data = file_get_contents($image['tmp_name']);
$insert_image_sql = "INSERT INTO images " .
"(filename, mime_type, file_size, image_data) " .
"VALUES ('{mysql_real_escape_string($image_filename)}', ".
"'{mysql_real_escape_string($image_mime_type)}', " .
"'{ mysql_real_escape_string($image_size)}', " .
"'{mysql_real_escape_string($image_data)}');";
mysql_query($insert_image_sql);
NOTE
You don’t need mysql_real_escape_string for the $image_size, since that’s a numeric value. However, if you’re constantly trying to remember whether or not input data is a string or a number, you’re eventually going to make a mistake and not escape something you should.
To be safe, just escape everything. It’s more consistent, and it’s another layer of protection. The time it takes PHP to escape that one bit of data is trivial compared to the problems if malicious data goes unescaped.
Printing a String to a Variable
As natural as this code looks, it’s got a pretty serious problem. While the curly braces surrounding a variable will allow that variable to be printed inside a string (so “{$variable}” prints the value of $variable), PHP draws the line at doing actual work inside the curly braces. So it won’t interpret the call to mysql_real_escape_string.
There are two ways to get around this dilemma. The first is the easiest: you could just move the calls to mysql_real_escape_string up into the variable assignments, sort of like this:
// Insert the image into the images table
$image = $_FILES[$image_fieldname];
$image_filename = mysql_real_escape_string($image['name']);
$image_info = getimagesize($image['tmp_name']);
$image_mime_type = mysql_real_escape_string($image_info['mime']);
// and so on...
This method also looks okay, but it’s not a good idea. Think about the function you’re calling: it’s specifically for getting values set up to work with MySQL. But what if you want to use $image_filename somewhere else in your script? You’ve turned this variable into a MySQL-specific version of the filename.
It seems like the original approach—converting the variable using mysql_real_escape_string as it’s going into the actual SQL INSERT statement—is the right one. It allows the variable to just be the image filename, or the image MIME type, and then you convert that into a MySQL-friendly value when that’s required.
So that seems to indicate there’s a need for a way to perform calculations or run functions on values when you’re constructing your SQL string…and there is. You typically do so using sprintf, which is a PHP function that prints to a string. In other words, you construct a string, using any calculations you need, and pass all the required information to sprintf. sprintf puts everything together and returns a string, which you can then assign to your variable, and boom, you’re then ready to pass that variable into mysql_query.
This technique is a little different than anything you’ve done so far. Instead of just building the string up with string concatenation, you indicate the entire string you want to create, but every time you come to a spot in the string where you want to include the value of a variable, you put in a special type specifier. For example, you use %s for a string type:
$hello = sprintf("Hello there, %s %s", $first_name, $last_name);
echo $hello;
Suppose $first_name is “John” and $last_name is “Wayne”. Running a script with these two lines would give you:
Hello there, John Wayne
sprintf replaces the first %s with the first value after the string, which is $first_name. Then it replaces the second %s with the second value after the string, $last_name. Finally, the whole thing—the string with the values inserted—is assigned to $hello.
What’s great about sprintf is that you can perform calculations on variables before you pass them to sprintf. So even though this example is a bit silly, the following code is perfectly legal:
$hello = sprintf("Hello there, %s", $first_name . ' ' . $last_name);
echo $hello;
Of course, there are much better ways to use sprintf… like creating a query string and using mysql_real_escape_string in the process:
// This replaces the older assignment to $insert_sql
$insert_sql = sprintf("INSERT INTO users " .
"(first_name, last_name, email, " .
"bio, facebook_url, twitter_handle) " .
"VALUES ('%s', '%s', '%s', '%s', '%s', '%s');",
mysql_real_escape_string($first_name),
mysql_real_escape_string($last_name),
mysql_real_escape_string($email),
mysql_real_escape_string($bio),
mysql_real_escape_string($facebook_url),
mysql_real_escape_string($twitter_handle));
// Insert the user into the database
mysql_query($insert_sql);
This code doesn’t do anything noticeably different than your older version, because the data being inserted into users was probably not a problem in the first place. But now you can take this same approach and apply it to your insertion into images.
$insert_image_sql = sprintf("INSERT INTO images " .
"(filename, mime_type, " .
"file_size, image_data) " .
"VALUES ('%s', '%s', %d, '%s');",
mysql_real_escape_string($image_filename),
mysql_real_escape_string($image_mime_type),
mysql_real_escape_string($image_size),
mysql_real_escape_string($image_data));
mysql_query($insert_image_sql);
You can guess what %d means to sprintf: replace that type specifier with a decimal number, like 1024 or 92048. So this code builds up an INSERT, and executes it, and escapes your values in the process.
POWER USERS’ CLINIC: SPRINTF IS YOUR NEW BEST FRIEND
Most PHP programmers use sprintf initially because it lets them do things like use mysql_real_escape_string on variables before they’re inserted into a query string. But those same programmers find something else out, just like you will: using sprintf lets you write a lot more robust and flexible code.
Now you can do calculations on your data, escape values, and do pretty much anything else you want to your data as you’re inserting into or selecting from your database. You no longer have to calculate things, then assign the results of those calculations to a variable (or, even worse, a new variable, based upon some old variable), and then—and only then—use those variables as part of a SQL construction.
sprintf lets you do all that in a single step. In general, you should use sprintf as your default means of creating SQL strings that are executed as queries against your database.
Now try this script out. Head over to create_user.php once again, find a new friend to fill out the form, let them choose an image, and submit the form. Your new version of create_user.php should run, and you’ll get to show_user.php.
Now, this time you won’t see the user’s profile, because that’s not code you’ve written. But you should be able to dig into your new images table and see an entry for the uploaded image:
mysql> SELECT image_id, filename FROM images;
+----------+----------------------------+
| image_id | filename |
+----------+----------------------------+
| 4 | 220px-William_Shatner.jpeg |
+----------+----------------------------+
1 row in set (0.00 sec)
Now, you most definitely do not want to do a SELECT * here because you’ll get MySQL’s attempt to load your actual image data, which might be a few hundred KB, or a few thousand KB! But at least you can see that an image is indeed in your table.
You can also access your table use PhpMyAdmin if you’ve got that running, and get a little extra information about your entries in images. Figure 9-1 shows you what to expect.
Getting the Correct ID Before Redirecting
Unfortunately, there still is a problem. You may have noticed something like Figure 9-2 when you got your image insertion working.
Figure 9-1. PhpMyAdmin reports BLOB columns—regardless of what type of BLOB you used—as BLOB and a size. In this case, you can see that the file size, at 11729 bytes, matches up with the size of the data in the BLOB column, which is 11.5KiB. This is a good way to make sure things are working: your script is correctly getting the size of the image it’s inserting into your database table.
Figure 9-2. This screen is hardly what you want to see after all that work on getting images into your database. So what gives?
The missing image isn’t as much of a mystery as it first seems. Here’s the last bit of your code from create_user.php:
// This replaces the older assignment to $insert_sql
$insert_sql = sprintf("INSERT INTO users " .
"(first_name, last_name, email, " .
"bio, facebook_url, twitter_handle) " .
"VALUES ('%s', '%s', '%s', '%s', '%s', '%s');",
mysql_real_escape_string($first_name),
mysql_real_escape_string($last_name),
mysql_real_escape_string($email),
mysql_real_escape_string($bio),
mysql_real_escape_string($facebook_url),
mysql_real_escape_string($twitter_handle));
// Insert the user into the database
mysql_query($insert_sql);
$insert_image_sql = sprintf("INSERT INTO images " .
"(filename, mime_type, " .
"file_size, image_data) " .
"VALUES ('%s', '%s', %d, '%s');",
mysql_real_escape_string($image_filename),
mysql_real_escape_string($image_mime_type),
mysql_real_escape_string($image_size),
mysql_real_escape_string($image_data));
mysql_query($insert_image_sql);
// Redirect the user to the page that displays user information
header("Location: show_user.php?user_id=" . mysql_insert_id());
What’s the problem? It’s in that last line. Remember, mysql_insert_id returns the ID of the last INSERT query… which is no longer the INSERT for your users table, but your new INSERT for images. So the redirect to show_user.php is working, but it’s sending the ID of the image inserted, rather than the user. You can fix that easily:
// This replaces the older assignment to $insert_sql
$insert_sql = sprintf("INSERT INTO users " .
"(first_name, last_name, email, " .
"bio, facebook_url, twitter_handle) " .
"VALUES ('%s', '%s', '%s', '%s', '%s', '%s');",
mysql_real_escape_string($first_name),
mysql_real_escape_string($last_name),
mysql_real_escape_string($email),
mysql_real_escape_string($bio),
mysql_real_escape_string($facebook_url),
mysql_real_escape_string($twitter_handle));
// Insert the user into the database
mysql_query($insert_sql);
$user_id = mysql_insert_id();
$insert_image_sql = sprintf("INSERT INTO images " .
"(filename, mime_type, " .
"file_size, image_data) " .
"VALUES ('%s', '%s', %d, '%s');",
mysql_real_escape_string($image_filename),
mysql_real_escape_string($image_mime_type),
mysql_real_escape_string($image_size),
mysql_real_escape_string($image_data));
mysql_query($insert_image_sql);
// Redirect the user to the page that displays user information
header("Location: show_user.php?user_id=" . $user_id);
exit();
Try this script out again, and you should be back to what you expect: still a slightly broken version of show_user.php, but broken in the way you expect (see Figure 9-3).
Figure 9-3. As odd as it seems, you sometimes want things to be broken. In this case, you want to see a missing image, because you haven’t written any code to display the image just INSERTed. What you don’t want to see—and what you just fixed—is the missing user information other than the image.
Connecting Users and Images
And it’s here that things become sticky. You have two tables—users and images—but no connection between those tables. So when you go to load a user and display it by using show_user.php, how do you figure out which image in the images table you should display for a given user in theusers table?
Clearly, there has to be some connection between these two tables. You’ve already got a unique ID for each entry in users (user_id) and in images (image_id), so that’s a good starting place. But does a user reference an image, or does an image reference a user?
Here’s the fundamental question you’ll ask over and over when you’re connecting two tables in a database: how are the two tables related? Better still, how are the two objects that your tables represent related?
Does a user have an image? Does a user have lots of images? In this case, a single user has a single profile image. So you have what is called a one-to-one (or 1-1) relationship. One user is related to one image. So you can create a new column in your users table, and in that column, you can store the image_id of that user’s profile image. You can make that change to your database like this:
mysql> ALTER TABLE users
-> ADD profile_pic_id int;
Query OK, 6 rows affected (0.11 sec)
Records: 6 Duplicates: 0 Warnings: 0
WARNING
You’ve already made changes to your scripts to accommodate storing images in your database, rather than on your file system. With this ALTER, you’re now making the same sort of changes to your database. These changes reflect a change in how your application works. So you want to back things up at this point in your database.
But backing up a script is a lot easier than backing up a database. You may want to give your hosting company a call and see if and how you can back up your database. Or, you can just figure out how to undo these changes if you go back to storing images on your file system.
Either way, you’re going to get some PHP and MySQL practice switching between the two approaches. That’s a good thing no matter what solution you end up using.
DESIGN TIME: FOREIGN KEYS AND COLUMN NAMES
The profile_pic_id column is setting up what’s called a foreign key relationship. This column is a foreign key because it relates to the key in a “foreign” table: images.
In most databases, you not only define a column in your table that relates to the referenced table’s primary key, you define a FOREIGN KEY at the database level. So your database actually knows that profile_pic_id is storing IDs that are in the images table’s image_id column. But until recently, MySQL hasn’t provided this feature.
Now, you can use foreign keys in MySQL, but you’ve got to use the MySQL InnoDB table engine. Doing so requires some extra setup, and not all hosting providers support InnoDB. Besides, programmers have been using MySQL without foreign key support for years, so if you code properly, you can work around this limitation. If you want to use InnoDB and foreign key support at the database level, start with this command on your tables:
ALTER TABLE [table-name]
ENGINE = InnoDB;
Then Google “MySQL foreign keys” and you’ll have a wealth of information at your fingertips.
Regardless of whether or not you use foreign keys through your programming, or you add support at the database level by using InnoDB, naming your foreign key columns is a big deal. The typical practice here is to name the foreign key [singular-table-name]_id. So for a foreign key connecting users to images, you’d typically take the singular name of the table you’re connecting to—“image” from images—and append “_id”. So you’d get image_id for your foreign key column name.
So then why use profile_pic_id in users? Because you could very well store more than just profile pictures in images. You might store several images for a user, only one of which is a profile picture. You might keep up with user’s candid photos, or icons for logging in, or images for companies to which your users connect.
In all these cases, then, image_id in users doesn’t provide enough specificity. So in these cases—where you’re not just setting up a foreign key, but actually setting up a foreign key and indicating a particular type of usage—using a different name makes sense. For instance, you could end up with a profile_pic_id column in users, and then perhaps a company_logo_id in a potential companiestable, and who knows what other images you’ll use? By using profile_pic_id now, you’re indicating that you’re relating to an image, and the specific purpose for which that image is being used.
Inserting an Image, then Inserting a User
Think carefully about what you have here. Once an image is in images, you need to get that image’s ID, and insert that into a user’s profile_pic_id column. But right now, your script inserts into users before inserting into images:
// This replaces the older assignment to $insert_sql
$insert_sql = sprintf("INSERT INTO users " .
"(first_name, last_name, email, " .
"bio, facebook_url, twitter_handle) " .
"VALUES ('%s', '%s', '%s', '%s', '%s', '%s');",
mysql_real_escape_string($first_name),
mysql_real_escape_string($last_name),
mysql_real_escape_string($email),
mysql_real_escape_string($bio),
mysql_real_escape_string($facebook_url),
mysql_real_escape_string($twitter_handle));
// Insert the user into the database
mysql_query($insert_sql);
$user_id = mysql_insert_id();
$insert_image_sql = sprintf("INSERT INTO images " .
"(filename, mime_type, " .
"file_size, image_data) " .
"VALUES ('%s', '%s', %d, '%s');",
mysql_real_escape_string($image_filename),
mysql_real_escape_string($image_mime_type),
mysql_real_escape_string($image_size),
mysql_real_escape_string($image_data));
mysql_query($insert_image_sql);
// Redirect the user to the page that displays user information
header("Location: show_user.php?user_id=" . $user_id);
exit();
Now, you could look up the ID of the user you inserted using mysql_insert_id, and store that in a variable. Then you could get the image ID using mysql_insert_id again. Finally, you could update the profile_pic_id column of the new user’s row in users. That would work, and you’d end up with three different database interactions:
1. An INSERT to put the user’s information into users.
2. An INSERT to put the image information into images.
3. An UPDATE to drop the new image’s ID into users.
Now, these steps may not seem like much, but every interaction with your database takes time and resources. As a general principle, you want to interact with your database as few times as you need. That’s not to say you don’t work with a database; you just don’t make three or four calls if you can pull off the same task with one or two.
In this case, you can actually reduce the number of MySQL interactions from three to two:
1. INSERT the image into the images table (and get the ID of that image in the process).
2. INSERT the new user into users, and use the image ID you just grabbed as part of the data you put into that INSERT.
Is this a big deal? Going from three MySQL interactions to two probably sounds pretty minor. Then again, you just cut your database interactions by a third! If you can make fewer calls, do it.
Go ahead and switch up your INSERT statements:
// Get image data
$insert_image_sql = sprintf("INSERT INTO images " .
"(filename, mime_type, " .
"file_size, image_data) " .
"VALUES ('%s', '%s', %d, '%s');",
mysql_real_escape_string($image_filename),
mysql_real_escape_string($image_mime_type),
mysql_real_escape_string($image_size),
mysql_real_escape_string($image_data));
mysql_query($insert_image_sql);
// This replaces the older assignment to $insert_sql
$insert_sql = sprintf("INSERT INTO users " .
"(first_name, last_name, email, " .
"bio, facebook_url, twitter_handle) " .
"VALUES ('%s', '%s', '%s', '%s', '%s', '%s');",
mysql_real_escape_string($first_name),
mysql_real_escape_string($last_name),
mysql_real_escape_string($email),
mysql_real_escape_string($bio),
mysql_real_escape_string($facebook_url),
mysql_real_escape_string($twitter_handle));
// Insert the user into the database
mysql_query($insert_sql);
// Redirect the user to the page that displays user information
header("Location: show_user.php?user_id=" . $user_id);
exit();
NOTE
There’s no additional code here. It’s just a wholesale move of the insertion creation and mysql_query call related to a user from before the image-related code to after that code.
But, you can remove some code. Now that you’ve got the insertion into users second, you can go back to using mysql_insert_id in your redirection.
Now it’s just a matter of getting the ID from your images INSERT and using it in the users INSERT. But you know how to do that: you can use mysql_insert_id to grab the ID of the row inserted into images, and then add that to your INSERT for users.
// Get image data
$insert_image_sql = sprintf("INSERT INTO images " .
"(filename, mime_type, " .
"file_size, image_data) " .
"VALUES ('%s', '%s', %d, '%s');",
mysql_real_escape_string($image_filename),
mysql_real_escape_string($image_mime_type),
mysql_real_escape_string($image_size),
mysql_real_escape_string($image_data));
mysql_query($insert_image_sql);
// This replaces the older assignment to $insert_sql
$insert_sql = sprintf("INSERT INTO users " .
"(first_name, last_name, email, " .
"bio, facebook_url, twitter_handle, " .
"profile_pic_id) " .
"VALUES ('%s', '%s', '%s', '%s', '%s', '%s', %d);",
mysql_real_escape_string($first_name),
mysql_real_escape_string($last_name),
mysql_real_escape_string($email),
mysql_real_escape_string($bio),
mysql_real_escape_string($facebook_url),
mysql_real_escape_string($twitter_handle),
mysql_insert_id());
// Insert the user into the database
mysql_query($insert_sql);
// Redirect the user to the page that displays user information
header("Location: show_user.php?user_id=" . mysql_insert_id());
exit();
NOTE
Remember that because the ID of the image you’re inserting into profile_pic_id is an int, not a string, you need to use %d as your type specifier for sprintf, and you don’t need to include that value in single quotes.
Put everything together, and your updated version of create_user.php should look like this:
<?php
require_once '../scripts/app_config.php';
require_once '../scripts/database_connection.php';
$upload_dir = SITE_ROOT . "uploads/profile_pics/";
$image_fieldname = "user_pic";
// Potential PHP upload errors
$php_errors = array(1 => 'Maximum file size in php.ini exceeded',
2 => 'Maximum file size in HTML form exceeded',
3 => 'Only part of the file was uploaded',
4 => 'No file was selected to upload.');
$first_name = trim($_REQUEST['first_name']);
$last_name = trim($_REQUEST['last_name']);
$email = trim($_REQUEST['email']);
$bio = trim($_REQUEST['bio']);
$facebook_url = str_replace("facebook.org", "facebook.com", trim($_
REQUEST['facebook_url']));
$position = strpos($facebook_url, "facebook.com");
if ($position === false) {
$facebook_url = "http://www.facebook.com/" . $facebook_url;
}
$twitter_handle = trim($_REQUEST['twitter_handle']);
$twitter_url = "http://www.twitter.com/";
$position = strpos($twitter_handle, "@");
if ($position === false) {
$twitter_url = $twitter_url . $twitter_handle;
} else {
$twitter_url = $twitter_url . substr($twitter_handle, $position + 1);
}
// Make sure we didn't have an error uploading the image
($_FILES[$image_fieldname]['error'] == 0)
or handle_error("the server couldn't upload the image you selected.",
$php_errors[$_FILES[$image_fieldname]['error']]);
// Is this file the result of a valid upload?
@is_uploaded_file($_FILES[$image_fieldname]['tmp_name'])
or handle_error("you were trying to do something naughty. Shame on you!",
"Uploaded request: file named '{$_FILES[$image_fieldname]
['tmp_name']}'");
// Is this actually an image?
@getimagesize($_FILES[$image_fieldname]['tmp_name'])
or handle_error("you selected a file for your picture that isn't an image.",
"{$_FILES[$image_fieldname]['tmp_name']} isn't a valid image
file.");
// Name the file uniquely
$now = time();
while (file_exists($upload_filename = $upload_dir . $now .
'-' .
$_FILES[$image_fieldname]['name'])) {
$now++;
}
// Insert the image into the images table
$image = $_FILES[$image_fieldname];
$image_filename = $image['name'];
$image_info = getimagesize($image['tmp_name']);
$image_mime_type = $image_info['mime'];
$image_size = $image['size'];
$image_data = file_get_contents($image['tmp_name']);
$insert_image_sql = sprintf("INSERT INTO images " .
"(filename, mime_type, file_size, image_data) " .
"VALUES ('%s', '%s', %d, '%s');",
mysql_real_escape_string($image_filename),
mysql_real_escape_string($image_mime_type),
mysql_real_escape_string($image_size),
mysql_real_escape_string($image_data));
mysql_query($insert_image_sql);
$insert_sql = sprintf("INSERT INTO users " .
"(first_name, last_name, email, " .
"bio, facebook_url, twitter_handle, " .
"profile_pic_id) " .
"VALUES ('%s', '%s', '%s', '%s', '%s', '%s', %d);",
mysql_real_escape_string($first_name),
mysql_real_escape_string($last_name),
mysql_real_escape_string($email),
mysql_real_escape_string($bio),
mysql_real_escape_string($facebook_url),
mysql_real_escape_string($twitter_handle),
mysql_insert_id());
// Insert the user into the database
mysql_query($insert_sql);
// Redirect the user to the page that displays user information
header("Location: show_user.php?user_id=" . mysql_insert_id());
exit();
?>
Try this code out by creating another user. Then, see what the last and highest inserted image ID is from your images table:
mysql> select image_id from images;
+----------+
| image_id |
+----------+
| 4 |
| 5 |
| 6 |
+----------+
3 rows in set (0.00 sec)
This should be the same ID that was inserted into your last inserted user in users:
mysql> select user_id, first_name, last_name, profile_pic_id from users;
+---------+------------+-----------+----------------+
| user_id | first_name | last_name | profile_pic_id |
+---------+------------+-----------+----------------+
| 1 | C. J. | Wilson | NULL |
| 5 | Peter | Gabriel | NULL |
| 7 | Bob | Jones | NULL |
| 22 | James | Roday | NULL |
| 30 | William | Shatner | 6 |
+---------+------------+-----------+----------------+
7 rows in set (0.01 sec)
Perfect! So now you can see that when an image gets inserted, the ID of that image gets dropped into users, and you’ve got a connection between a user and an image.
Joining Tables with WHERE
So how do you actually get an image when you have a user? Everything begins with a user ID. With that, you can select the user you want:
$select_query = sprintf("SELECT * FROM users WHERE user_id = %d",
$user_id);
This is just a sprintf version of code from show_user.php. Go ahead and make this change in your own version of show_user.php.
But you get more than just user information, now. You also get the profile_pic_id for that user. So you can use this ID to get the image for that user:
if ($result) {
$row = mysql_fetch_array($result);
$first_name = $row['first_name'];
$last_name = $row['last_name'];
$bio = preg_replace("/[\r\n]+/", "</p><p>", $row['bio']);
$email = $row['email'];
$facebook_url = $row['facebook_url'];
$twitter_handle = $row['twitter_handle'];
$profile_pic_id = $row['profile_pic_id'];
$image_query = sprintf("SELECT * FROM images WHERE image_id = %d",
$profile_pic_id);
$image_result = mysql_query($image_query);
// Turn $twitter_handle into a URL
$twitter_url = "http://www.twitter.com/" .
substr($twitter_handle, $position + 1);
}
NOTE
You can remove any code in show_user.php that involves the profile image’s file path, since you’re no longer using that approach for dealing with images.
This code works, but it’s actually turning what is potentially one step into two. What you’re doing here is joining two tables: you’ve got a piece of information—profile_pic_id in users and image_id in images—that connects the two tables.
Connect Your Tables Through Common Columns
You’ve also got a way to get only certain rows from a table: the WHERE clause. So putting this together, you can get a user from users and an image from images where the user’s profile_pic_id matches the image’s image_id:
SELECT first_name, last_name, filename
FROM users, images
WHERE profile_pic_id = image_id;
Run this in MySQL, and you should see a result like the following:
mysql> SELECT first_name, last_name, filename
-> FROM users, images
-> WHERE profile_pic_id = image_id;
+------------+-----------+----------------------------+
| first_name | last_name | filename |
+------------+-----------+----------------------------+
| William | Shatner | 220px-William_Shatner.jpeg |
+------------+-----------+----------------------------+
1 row in set (0.02 sec)
WARNING
Don’t expect exactly the same actual data from your query result, unless you too have inserted Bill Shatner into your database, since you love him in brilliant shows like Boston Legal.
This is pretty spectacular stuff! You’re connecting your tables together. In a single query, you’ve joined information in one table to corresponding information in another table. That’s big stuff.
Alias Your Tables (and Columns)
But as cool as this query is, it’s a bit confusing. Take a look again:
SELECT first_name, last_name, filename
FROM users, images
WHERE profile_pic_id = image_id;
It’s pretty obvious that first_name and last_name are columns from users. But unless you know your database structure, it’s not immediately clear where filename comes from. (Of course, you are intimately familiar with your database, so you do know that filename is a column in images.)
The same is true with profile_pic_id and image_id. Both are column names, but which column belongs to which table?
You can make this clear, though, through using table prefixes on your columns. So you can convert this query to something a bit more obvious:
SELECT users.first_name, users.last_name, images.filename
FROM users, images
WHERE users.profile_pic_id = images.image_id;
You’ll get the same result, but the query itself is a lot less ambiguous. Now, there’s another important fact to keep in mind here: programmers are lazy. Yup, it’s true; most programmers would rather type a single character—or at most two—if they can avoid typing five or ten. And SQL is happy to accommodate. You can alias a table by providing a letter or two after the table name, and then using that letter as your prefix in the rest of the query:
SELECT u.first_name, u.last_name, i.filename
FROM users u, images i
WHERE u.profile_pic_id = i.image_id;
Once again, there’s nothing functionally different about this query, but it’s now both clear and succinct: a programmer’s best-case situation.
Show Me the Image
At this point, you’ve got all your data, and you can even get the image for a particular user. So all that’s left is to actually show the image… right?
That’s true. But you’ve got an entirely different situation than when you already had the image on a file system, and just needed to point at that file. In this case, you’ve got to load the actual raw image data from your database, and then somehow let the browser know, “Hey, this is an image, not just text. Display it like an image.” That’s not particularly difficult, but it is very much different from what you’ve been doing.
Displaying an Image
First, you need a script that can do the loading and displaying of an image. Then, once that’s done, it’s pretty simple to reference that display script in your show_user.php. So the script is the important piece, with all the new code.
Go ahead and create a new script, and call it show_image.php. You can start out with the basic script shell that all your scripts now have:
<?php
require '../scripts/app_config.php';
require '../scripts/database_connection.php';
?>
Game Plan Your Script
Now, what exactly needs to happen in this script? You can map out the basic steps:
1. Get an image ID from the request.
2. Build a SELECT query from the images table using that image ID.
3. Run the SELECT query and get the results.
4. Grab what should be the only row from those results.
5. Tell the browser that it’s about to receive an image.
6. Tell the browser what kind of image it’s about to receive.
7. Give the browser the image data.
With the exception of these last few steps, you’re probably already whirring away, figuring out exactly what sort of code you need to write. But there’s a lot of error handling that has to happen along the way, too:
1. Make sure an image ID was actually sent to the script.
2. Make sure the ID maps to an image in the images table.
3. Deal with general problems that occur loading or displaying the image data.
Again, though, none of this is particularly hard. So time to get to work.
Get the Image ID
First up, you need to get an ID to use for loading the image from the database. You can also do some initial error handling now: if there’s no ID coming in as part of the request, something’s gone wrong.
<?php
require '../scripts/app_config.php';
require '../scripts/database_connection.php';
if (!isset($_REQUEST['image_id'])) {
handle_error("no image to load was specified.");
}
$image_id = $_REQUEST['image_id'];
?>
Simple enough, and a lot like code you’ve written before, in show_user.php. And once again, handle_error makes dealing with problems if they do occur a piece of cake.
Build and Run a Select Query
Next up, you can use your new friend sprintf to construct a SQL query, and an older friend, mysql_query, to get a result set.
<?php
// require statements
// Get the image ID
// Build the SELECT statement
$select_query = sprintf("SELECT * FROM images WHERE image_id = %d",
$image_id);
// Run the query
$result = mysql_query($select_query);
?>
Nothing new here, either.
Get the Results, Get the Image, and Deal with Potential Errors
Now you can grab the actual data from $result. In the past, you’ve done that in a few ways. Early on, you actually looped over all the rows returned from a query:
if ($return_rows) {
// We have rows to show from the query
echo "<p>Results from your query:</p>";
echo "<ul>";
while ($row = mysql_fetch_row($result)) {
echo "<li>{$row[0]}</li>";
}
echo "</ul>";
} else {
// No rows. Just report if the query ran or not
echo "<p>The following query was processed successfully:</p>";
echo "<p>{$query_text}</p>";
}
NOTE
This code is from way back in Chapter 5. How to believe how much more advanced your PHP scripts have become in a few short chapters, isn’t it?
You’ve also used an if statement if you expected only a single result:
if ($result) {
$row = mysql_fetch_array($result);
// Deal with the single result
} else {
handle_error("there was a problem finding your information in our system.",
"Error locating user with ID {$user_id}");
}
This statement assumes that as long as $result is valid, you’ve got a row. Further, it just ignores any rows other than the first, knowing that the SQL query that generated these results can only return a single row.
In show_image.php, you want something similar to this latter approach. But, it’s possible to check and make sure you have a result without encasing everything in an if:
<?php
// require statements
// Get the image ID
// Build and run the query
if (mysql_num_rows($result) == 0) {
handle_error("we couldn't find the requested image.",
"No image found with an ID of " . $image_id . ".");
}
$image = mysql_fetch_array($result);
?>
This approach is cleaner because it keeps your code moving along once the error’s been dealt with. (For more on why this sequence is more natural, check out the box below.)
DESIGN TIME: SEQUENTIAL CODE IS USUALLY CLEARER CODE
There’s almost always more than one way to accomplish any task in programming. In fact, there are usually multiple good ways to get a job done. But there’s usually a clearest way and that’s what you want to work toward. You want good, working code that’s also clear and easy to understand.
Now, writing clear code gets harder as your code gets more complex. You often have multiple decision points (with if statements), error handling, loops, and all sorts of other constructs that take your code all over the place. Because of all this complexity, you want to make as much of your code as you can sequential. In other words, you want to be able to read that code more or less beginning to end, and be able to follow the flow.
With that in mind, take a look again at the earlier code from show_user.php:
if ($result) {
$row = mysql_fetch_array($result);
// Deal with the single result
} else {
handle_error(
"there was a problem finding your " .
"information in our system.",
"Error locating user with ID
{$user_id}");
}
This code works, and it’s even pretty solid. But is it sequential? Well… sort of. If there’s a result, get that result, and work with it. If there’s not, deal with errors.
But how do you really think about this? What’s the real sequence?
First, you want to see if there’s a result, and if not, handle the error. Then—and really, only after you’re sure it’s safe to carry on—you want to work with the results, and continue the program. So in this line of thinking, the else at the end handling the error is out of sequence. It’s something you conceptually want to deal with before going on to work with the row.
That’s why the newer sequence in show_image.php, where errors are handled and then results are used, is a better solution for your code’s readability. Same functionality, but easier to understand and maintain.
Tell the Browser What’s Coming
Now you’ve got the information you want from images, but you can’t just toss that to the browser. Well, you can, but the browser is going to be confused. It’s used to dealing with HTML, but raw binary data is something else altogether.
There are a couple of things you have to let the browser know here:
§ What kind of content is coming? This information is passed to the browser through a MIME type, and usually is something like text/html or text/xml, or in the case of images, image/jpeg or image/gif or image/png.
§ If that type is binary—like images are—what size of information is coming? The browser needs to know so it can figure out when it’s done receiving information.
Now, even though you may be wondering how to talk to the browser, you’ve already got the tools you need to do just that. Remember this line?
header("Location: " . SITE_ROOT . "scripts/show_error.php?" .
error_message={$user_error_message}&" .
system_error_message={$system_error_message}");
This code talks directly to the browser. It’s sending a header, called Location, to the browser. The value of that header is a location, a URL, and the browser knows that when it gets a Location header, to go to the URL that’s the value of that header.
So the header function in PHP is the mechanism by which you can speak directly to the browser. As for the two pieces of information you need to send—the content type and the size of that content—browsers have specific headers for both:
§ Content-type lets you tell a browser what the MIME type is of the content you’re about to send.
§ Content-length lets you give a size—actually the “length” of a file—of the information you’re about to send.
Now, you’ve got both of these pieces of information in your images table, in the mime_type column and the file_size column.
Put all this together, and you’ve got two lines of code to add to show_image.php:
<?php
// require statements
// Get the image ID
// Build and run the query
// Get the result and handle errors from getting no result
header('Content-type: ' . $image['mime_type']);
header('Content-length: ' . $image['file_size']);
?>
That’s it. Now the browser expects a certain type of information (in your case, image/ jpeg or, in most cases, image/gif), it knows the size of the information and now it just needs the actual information itself.
Send the Image Data
What’s left? The easiest step of all:
<?php
// require statements
// Get the image ID
// Build and run the query
// Get the result and handle errors from getting no result
// Tell the browser what's coming with headers
echo $image['image_data'];
?>
That’s it. Now, this data is not a string of text; it’s the raw binary information pulled from a BLOB column in your images table, spit out bit by bit. But the magic isn’t in this line. The magic is you telling the browser that this is a certain kind of information, and a certain size. Those let the browser know, “This is an image coming. Treat it like one.”
Catching and Handling Errors
At this point, you’ve knocked out your list of things to do to show an image:
1. Get an image ID from the request.
2. Build a SELECT query from the images table using that image ID.
3. Run the SELECT query and get the results.
4. Grab what should be the only row from those results.
5. Tell the browser that it’s about to receive an image.
6. Tell the browser what kind of image it’s about to receive.
7. Give the browser the image data.
All done; excellent. And the script is short, too; clean and easy to follow. That’s a win by every account.
You’ve also taken care of most of your error handling:
1. Make sure an image ID was sent to the script.
2. Make sure the ID maps to an image in the images table.
3. Deal with general problems that occur loading or displaying the image data.
Hmmm. The first two are done, but what about those so-called general problems? What happens if, for example, there’s an error sending the Content-type header? Or perhaps sending the Content-length header? And what about echoing out the image data? Doesn’t that seem like something can go bad? What if the image data is corrupt, or something happens in pulling data from the result set, or if the browser can’t handle a particular type of image that your script tries to send?
In all these cases, you get an error that’s unaccounted for. And when you have these general sort of errors—these errors that are beyond black-and-white, “I can check ahead of time and make sure there’s no problem”—you have to deal with them.
The real problem here is that you can’t pin these things down. You need a way to say, “While this entire chunk of code is running, if something general happens, do this…” And you’ve got a “do this” in handle_error. PHP provides a way to do just this with something called a try/catch block.
The try part of a try/catch block is a block that you put all your error-prone code into. So you’re saying, “Try this code.” The catch path of the try/catch block is run only if an error occurs. So if at any time within the try block, something goes wrong, the catch part of the block runs.
Not only that, but in the catch, an object gets handed off: an Exception. This Exception has information about what went wrong, so you can report on that…say to a custom function like handle_error.
To put this into place in show_image.php, first surround all your error-prone code with a try and curly braces, like so:
<?php
require '../scripts/app_config.php';
require '../scripts/database_connection.php';
try {
if (!isset($_REQUEST['image_id'])) {
handle_error("no image to load was specified.");
}
$image_id = $_REQUEST['image_id'];
// Build the SELECT statement
$select_query = sprintf("SELECT * FROM images WHERE image_id = %d",
$image_id);
// Run the query
$result = mysql_query($select_query);
if (mysql_num_rows($result) == 0) {
handle_error("we couldn't find the requested image.",
"No image found with an ID of " . $image_id . ".");
}
$image = mysql_fetch_array($result);
header('Content-type: ' . $image['mime_type']);
header('Content-length: ' . $image['file_size']);
echo $image['image_data'];
}
?>
So now all this code is covered. Anything that goes wrong, the PHP interpreter will spit out an Exception object reporting the problem, and go to the catch block:
<?php
require '../scripts/app_config.php';
require '../scripts/database_connection.php';
try {
// code that may cause an error
} catch (Exception exc) {
}
?>
You can see that this code almost looks like a function: the catch code gets control, and it gets an Exception object passed to it. exc is the variable name of the exception, so you can reference that exception if you need to.
NOTE
The exc variable doesn’t begin with a $ because it’s typical for objects in PHP to not have names prefixed with the $ character. You’ll learn more about PHP objects, but for now, realize that exc is a variable, just a different type of variable than you’ve worked with before.
Finally, you should do something useful in this catch block:
<?php
require '../scripts/app_config.php';
require '../scripts/database_connection.php';
try {
// code that may cause an error
} catch (Exception exc) {
handle_error("something went wrong loading your image.",
"Error loading image: " . $exc->getMessage());
}
?>
This code should carry no surprises, except perhaps for that weird ->. Anytime there’s an error, handle_error comes to the rescue. As usual, you pass handle_error a friendly string, and then some extra information for the programmers who might be looking on. In this case, that message comes from exc, and the getMessage method. An object in PHP doesn’t have functions; it has methods. And you reference a method with ->.
NOTE
Don’t worry if that was totally confusing. You’ll soon be an object pro, so just get the code down now, and trust that this, too, will all be old hat before much longer.
So when this code runs, it reports any error that might have occurred, and stops PHP from trying to continue on in the try block.
Here’s what you should have for show_image.php:
<?php
require '../scripts/app_config.php';
require '../scripts/database_connection.php';
try {
if (!isset($_REQUEST['image_id'])) {
handle_error("no image to load was specified.");
}
$image_id = $_REQUEST['image_id'];
// Build the SELECT statement
$select_query = sprintf("SELECT * FROM images WHERE image_id = %d",
$image_id);
// Run the query
$result = mysql_query($select_query);
if (mysql_num_rows($result) == 0) {
handle_error("we couldn't find the requested image.",
"No image found with an ID of " . $image_id . ".");
}
$image = mysql_fetch_array($result);
header('Content-type: ' . $image['mime_type']);
header('Content-length: ' . $image['file_size']);
echo $image['image_data'];
} catch (Exception $exc) {
handle_error("something went wrong loading your image.",
"Error loading image: " . $exc->getMessage());
}
?>
So what’s left? Just some testing to make sure things work.
Test, Test, Always Test
First, open up MySQL, and find an image that’s been inserted. Make a note of that image’s ID.
mysql> select image_id, filename from images;
+----------+----------------------------+
| image_id | filename |
+----------+----------------------------+
| 6 | 220px-William_Shatner.jpeg |
+----------+----------------------------+
1 row in set (0.03 sec)
Now, open up your browser, and type in the URL for show_image.php. But don’t hit Enter! Well, you can, but you should get the error shown in Figure 9-4, because you didn’t supply an ID.
Figure 9-4. It’s not completely necessary, but it’s probably a good idea to even test your errors out. In this case, by not specifying an image ID, you’re verifying that errors are handled properly, and in particular that the case where no image ID is provided is handled.
Now, add the image ID to the URL like this: show_image.php?image_id=6. Put that (along with the rest of your domain name and path) and you should get something like Figure 9-5.
Figure 9-5. Here’s what all this work is about: getting a browser to show an image. In fact, this is a lot like right-clicking an image on another web page, and selecting View Image. It shows you just the image, without any other text.
So now you’ve got William Shatner staring out at you, and that’s actually a good thing.
Embedding an Image Is Just Viewing an Image
Finally, it’s back to show_user.php. Remember, show_image.php was really a bit of a diversion. It’s a necessary one, but the point isn’t a script that displays an image. Instead, it’s a script that displays a user, and that just happens to mean you have to show that user’s image. But, you’ve got all the work done now to make this work, so show_user.php is back into the fold, ready to piece it all together.
All You Need is an Image ID
Your first thought might be to rewrite that SQL query that grabs an entry from images based on a user from users:
SELECT u.first_name, u.last_name, i.filename
FROM users u, images i
WHERE u.profile_pic_id = i.image_id;
But do you need to do this? No, because all that show_image.php requires is an image ID, and you have that in the users table, in profile_pic_id. You don’t need to do a join on users and images.
So when you’re getting the results from your SQL query, you just need to grab the profile image ID:
<?php
require '../scripts/app_config.php';
require '../scripts/database_connection.php';
// Get the user ID of the user to show
// Build the SELECT statement
// Run the query
if ($result) {
$row = mysql_fetch_array($result);
$first_name = $row['first_name'];
$last_name = $row['last_name'];
$bio = preg_replace("/[\r\n]+/", "</p><p>", $row['bio']);
$email = $row['email'];
$facebook_url = $row['facebook_url'];
$twitter_handle = $row['twitter_handle'];
$image_id = $row['profile_pic_id'];
// Turn $twitter_handle into a URL
} else {
handle_error("there was a problem finding your information in our system.",
"Error locating user with ID {$user_id}");
}
?>
<!-- HTML -->
NOTE
This line of new code replaces the older line where you grabbed the URL to the image, in the version where you stored just a path to the image in your users table.
A Script Can Be an Image src
With this ID, you’re ready to deal with the missing image. But what’s about to happen is going to seem a bit odd, so some explanation is in order.
Think about your standard, vanilla HTML img element:
<img src="/images/roday.jpg" />
The img part tells the browser to expect an image. And the src tag tells the browser the location of that image. But that location is just going to trigger another browser request—in this case, to /images/roday.jpg. And what does the browser get from that location? A bunch of bits that makes up the image roday.jpg.
But there’s nothing magical about roday.jpg, or that URL. It’s just a location, and as long as that location returns an image to the browser, the image is displayed. So it’s perfectly okay to supply anything to the src, as long as that anything returns an image. You might supply it, let’s say, a script that displays an image. You might just hand it something like this:
<img src="show_image.php?image_id=6" />
And since show_image.php with a valid ID returns an image, the browser happily displays that image in place of the img tag in your web page.
As a result, you can change your HTML to do just this:
<?php
// Lots of PHP goodness
?>
<html>
<head>
<link href="../css/phpMM.css" rel="stylesheet" type="text/css" />
</head>
<body>
<div id="header"><h1>PHP & MySQL: The Missing Manual</h1></div>
<div id="example">User Profile</div>
<div id="content">
<div class="user_profile">
<h1><?php echo "{$first_name} {$last_name}"; ?></h1>
<p><img src="show_image.php?image_id=<?php echo $image_id; ?>"
class="user_pic" />
<?php echo $bio; ?></p>
<p class="contact_info">
Get in touch with <?php echo $first_name; ?>:
</p>
<ul>
<!-- Connect links -->
</ul>
</div>
</div>
<div id="footer"></div>
</body>
</html>
That’s all there is to it! The src tag of your img is now a link to your script, with the right ID. So when you take all of show_user.php together, you should have something like this:
<?php
require '../scripts/app_config.php';
require '../scripts/database_connection.php';
// Get the user ID of the user to show
$user_id = $_REQUEST['user_id'];
// Build the SELECT statement
$select_query = sprintf("SELECT * FROM users WHERE user_id = %d",
$user_id);
// Run the query
$result = mysql_query($select_query);
if ($result) {
$row = mysql_fetch_array($result);
$first_name = $row['first_name'];
$last_name = $row['last_name'];
$bio = preg_replace("/[\r\n]+/", "</p><p>", $row['bio']);
$email = $row['email'];
$facebook_url = $row['facebook_url'];
$twitter_handle = $row['twitter_handle'];
$image_id = $row['profile_pic_id'];
// Turn $twitter_handle into a URL
$twitter_url = "http://www.twitter.com/" .
substr($twitter_handle, $position + 1);
} else {
handle_error("there was a problem finding your information in our system.",
"Error locating user with ID {$user_id}");
}
?>
<html>
<head>
<link href="../css/phpMM.css" rel="stylesheet" type="text/css" />
</head>
<body>
<div id="header"><h1>PHP & MySQL: The Missing Manual</h1></div>
<div id="example">User Profile</div>
<div id="content">
<div class="user_profile">
<h1><?php echo "{$first_name} {$last_name}"; ?></h1>
<p><img src="show_image.php?image_id=<?php echo $image_id; ?>"
class="user_pic" />
<?php echo $bio; ?></p>
<p class="contact_info">
Get in touch with <?php echo $first_name; ?>:
</p>
<ul>
<li>...by emailing them at
<a href="<?php echo $email; ?>"><?php echo $email; ?></a></li>
<li>...by
<a href="<?php echo $facebook_url; ?>">checking them out
on Facebook</a></li>
<li>...by <a href="<?php echo $twitter_url; ?>">following
them on Twitter</a></li>
</ul>
</div>
</div>
<div id="footer"></div>
</body>
</html>
You can see the result in Figure 9-6.
Nice work. Who ever thought before you closed this chapter you’d be manually loading bits and bytes from a database and displaying them as an image on demand?
FREQUENTLY ASKED QUESTION: AREN’T THERE ONLINE TUTORIALS FOR WORKING WITH IMAGES?
If you’ve spent much time on the Internet, you probably know what a force Google is. Spend five minutes on their search engine and you’ll find at least 20 or 30 tutorials on image uploads in PHP, both for storing paths to the image in your database and for storing the images themselves in your database. Heck, there are even frameworks that take care of all this programming for you!
So why the heck is it worth you plowing through some of the trickiest PHP material you’ve run across yet, just to do this yourself? There are two very important reasons why this sort of code—and in fact this exact code—is important not only for you to type into your editor, but also to actually understand.
First, you can do lots of things using frameworks floating around on the Internet. And, truth be told, many of the frameworks, especially when you get them from reputable sources, do what your code would do, better, faster, and with greater efficiency. But that doesn’t mean it’s not important to understand what’s going on. In fact, once you do understand how this code works, you’re muchbetter prepared to make good choices about which frameworks to use, and why those frameworks might be better than writing your own…after you’ve written your own, and are ready to move to a more advanced usage.
And second, as you write more and more web applications, you’ll often find your needs are more and more specific. So you need image uploading, but you need it with some particular wrinkle or tweak specific to your application. Maybe you only want to accept JPGs, and not GIFs; or you want to impose a server-side restriction on size, rather than relying on the HTML input field that sets a maximum size.
But if you have no idea how this sort of code works, then how can you possibly make adjustments like this? Whether it’s your code or someone else’s, you need to be able to make those sort of adjustments that personalize and specialize a piece of code. That requires knowledge, and knowledge comes from trying things out for yourself.
Figure 9-6. Wow, has this been a long time in coming. Just a few hundred lines of code ago, you were referencing an image on a file system. Cool, yes; but loading an image from a database? That was something else altogether. Now you’ve got a new script, a new approach, and yet another way to show a user’s smiling face (or perhaps, her cat’s face) in full color.
So Which Approach is Best?
So here you are, with two totally different approaches to getting users’ images into your database (or at least the paths to those images). In fact, you’ve probably spent as much time working through this code as any other code you’ve run across in your PHP journey. Now, one question begs to be answered: which approach is best?
The best answer to that is, “It depends.” Or maybe, “It’s up to you.” Those are frustrating answers, and probably completely unsatisfying. That’s because the sort of questions you’re getting into—storing images or handling errors or interacting with other frameworks and other people’s code—you’re not always going to have clear “right” answers.
Do you have a particularly small file system with which to work? Are you charged based on the space your web server’s files take up? Is that charge greater or lesser than the charges you’re assessed for the size of your database? Is your database locally accessible and blistering fast? Or is it a slow connection to another machine? These all have an effect; these all bear on your answer.
And still, at the end of the day, you sometimes have to say, “I’m not sure…I just like this approach better…or that approach better.” That’s okay. You may just need to pick something, try it out, and get moving. There are plenty of cases where the only real wrong solution is to wait around analyzing the options for hours (days! weeks!) instead of moving forward.
OK, If You Insist on an Answer…
If you’re not sure, store your images on a file server, and store just the path to that image in your database. The reality is that while you can write good code that both stores an image in a database, and displays that image, it’s a lot tougher to do things right. Every single time a SELECT runs against your images table and grabs the contents of the image_data column, you’re selecting the entire size of that image’s data. Say you have 100 rows each with an image of an average size of 1MB, then you’ve got 100MB of image data clogging up your network and database traffic.
So when in doubt, stick with a simple path in your database. So why go through an entire chapter on an alternative approach? Because now you have a handle on just what goes on with images, whether they’re stored in the database or not.
FREQUENTLY ASKED QUESTION: SO HOW DO I GET MY DATABASE BACK IN ORDER?
Going with images stored on the file system is the better solution, all things being equal. (To be clear, though, all things are never equal!) Since that’s a good default option, the examples moving forward will assume that’s your setup. So how do you get back to that solution?
First, you should have backed up your scripts. If you didn’t, you may want to re-download the sample files from the Missing CD page, and use the versions that don’t store images in the database.
Second, you need to remove the profile_pic_id column in your users table. Here’s the SQL to make that change:
ALTER TABLE users
DROP COLUMN profile_pic_id;
You can then delete the images table easily enough:
DROP TABLE images;
That’s it. You’re back in action.