- 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
Storing the Data in the Database
Once you’ve created the database table, you can begin storing binary data there using PHP. You may be surprised to see how simple that is. All that’s required by the PHP script is a standard INSERT query, using the binary data for the file column value. To grab the file data to be used in the query, you must call the PHP file_get_contents() function, which reads a file into a string:
$file_data = file_get_contents($file);
In a real-world script, the file itself would probably have been uploaded and validated, but for the sake of simplicity, I’m using a static value here (see the downloadable code).
Now the $file_data variable is a binary string of data.
The PHP script also needs to determine the file’s MIME type. This can easily be accomplished using the Fileinfo extension, enabled by default as of PHP 5.3. Using it is a two-step process. First, you have to identify what it is you want to discover about a file; in this case, its MIME type:
$file_info = finfo_open(FILEINFO_MIME_TYPE);
Then you retrieve that information:
$file_type = finfo_file($file_info, $file);
The SQL query also needs to know the size of the file:
$file_size = filesize($file);
All that’s left is to define and execute the query:
$q = "INSERT INTO files (id, name, file, file_type, file_size) VALUES (NULL, '$file', '" . mysqli_real_escape_string($dbc, $file_data) . "', '$file_type', $file_size)";
As you can see in that line, to prevent the binary data from breaking the query, you either need to apply the mysqli_real_escape_string() function or use prepared statements.
Note that if you were to look at the stored record using phpMyAdmin or another tool, the value of the file column would appear to be gobbledygook (see Figure 1). What you’re seeing is the interface attempting to represent binary data as plain text.
)
Figure 1 The file’s record in the database, including part of the binary data.