It isn’t too long into your web development career before you get to a point where you can store and retrieve plain-text data in your sleep. Being strong in this area is important, as plain text is the most commonly stored data format. However, there are situations in which storing and retrieving binary data is useful, too. In this article, Larry Ullman explains exactly how you can do that using PHP and MySQL, and why you would want to.

From the author of

Databases are simply warehouses for information: a reliable place to put, and later fetch, data. Most of the time, the stored information will be in a plain-text format. Just the characters on your keyboard are sufficient to represent:

Numbers

Strings (i.e., characters and longer text)

Dates and times

But websites and other applications also use binary datafor example, images or PDFs. Most developers choose to store those kinds of binary data as files on the file system. In many situations, that’s an appropriate decision. There’s no reason, for example, why it would make sense to store your site’s navigation images in a database. Sometimes, though, developers choose to use the file system merely because they’re unaware of the alternative option.

So that you can make the right decision as to where to store your data, it’s important to learn how to, and why you would, use the database instead of the file system for binary data.

The Benefits of Storing Binary Data in a Database

Before learning how you store and retrieve binary data in a database, you need to understand why you would. Databases offer several benefits over the file system. The first is additional security: Only authenticated database users can access the stored information. If the data being stored is of a sensitive naturesay, a scanned image of an identification documentthen storing it in a database is prudent.

A second benefit of using a database for your binary data is that doing so keeps related information together. If you have a CMS that allows for the uploading of files, you might be inclined to store the document itself on the file system but place information about that document in the database. The result is two references to one item. If you were instead to store the document in the database along with the metadata about that document, all of the information about the uploaded file, including the file itself, would be in one place.

Third, and on a similar note, on very large and active sites, the workload will be spread across multiple servers. Content placed on the file system is only usable in such situations if that content is duplicated across every machine. Not only is this inefficient, but it’s tricky to maintain. Conversely, (most) database applications have good built-in replication for handling the mirroring of data across multiple servers.