Up until now, you’ve been working with one table: users. That’s because you’ve been working with a single entity: 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 user. Put another way, everything in the users table describes a user.
But, when you store an entire image within the database, you’re no longer dealing with something that describes a user. In fact, although an image is related to a user-it’s the image that a user wants to display when his profile is viewed-it’s an object in its own right. Just like a user, it’s a unique entity that might have other information describing it. And also like a user, an image should go into its own table.
You’re going to create a new table called images that’s going to store not only a user’s image, but several key details about that image:
• An image ID This will uniquely identify the image, similar to a user _id in the users table. It will also let you associate an image to the users table a bit later.
• The image name Even though you’re storing the image’s data, you still need a name by which you can refer to that image.
• The image’s MIME type This information is important for instructing a web server whether it needs to display a JPG, GIF, PNG, or something else entirely.
• The file size This is more information that you supply to the browser for displaying the image.
• The image data itself The raw bits and bytes that are turned into pixels and colors. Translate this into SQL and you get a new CREATE statement:
Translate this into SQL and you get a new CREATE statement:
You’ve seen all of this. before, with the exception of a new column type: mediumblob. As you might expect, this implies there are a few other blob types, which are:
• tinyblob This type stores objects up to 256 bytes.
• blob You can store objects up to 65 KB (kilobytes) in a blob column.
• mediumblob This has a capacity for up to 16 MB of data.
• longblob This is the big one. You can store 4 GB of data in a longblob column.
The term blob stands for binary large object. It’s a column designed for the very type of information that makes up an image; in other words, information that’s neither a number nor a string, but is instead binary data. (For more detail on which type of blob to use and when, see the box that follows.)
Planning for Growth and Describing Your Data
In the PHP world, there’s a fair bit of disagreement about which blob type you should use for a given column. Some argue that you should always use long blob, whereas others argue that you should know exactly what size file you’re dealing with, and use t.he blob that covers that size, and nothing more.
With those who argue for always using long blob, the thinking is that you’re planning ahead. Because your database uses space as your actual data needs-and not the column’s maximum size-a longblob holding a 2 MB image takes up just as much space, or more accurately, no more space than a mediumblob holding a 2 MB image. Then, why not use longblob all the time, and never have to change your column type as your storage needs change?
On the other hand, if you’re allowing only images that are 2MB or smaller, mediumblob best describes your data. You’re doing more than just choosing an arbitrary type; you’re providing information about what goes in the column.
For example, 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 (see the box on page 111). You lose a chance to say something about your data with that approach. The same is true for using a long blob I, (and this is an important if) you’ve clearly decided that you’re only accepting images up to a size that would fit in a mediumblob.
Go ahead and create this table. Ensure that it’s in the same database as users. You should now be able to see both of these tables in your database: