How to Store and Retrieve Binary Data Using PHP and MySQL
- The Benefits of Storing Binary Data in a Database
- Creating the Database Table
- Storing the Data in the Database
- Retrieving the Data from the Database
- Conclusion
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
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.