- 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
Retrieving the Data from the Database
With the binary data stored in the database, the next and final step is to retrieve it from the database and send it to the browser. Retrieving it is simple: Just run a SELECT query:
$q = "SELECT name, file, file_type, file_size FROM files WHERE id=$id";
$r = mysqli_query($dbc, $q);
if (mysqli_num_rows($r) == 1) {
$file = mysqli_fetch_array($r, MYSQLI_ASSOC);
Presumably the value of the $id variable would be passed in the URL as in http://www.example.com/retrieve.php?id=1. That value would also be validated prior to running the query.
Sending the file to the web browser is not that difficult, but you change how you use PHP. Traditionally, a PHP script generates HTML that is rendered by the browser. In this scenario, the PHP script is “generating” binary data instead. It is not possible for the same PHP script to send to the browser both HTML and binary data. Thus, it’s very important that this script does not output anything to the browser except for that data: no HTML, no plain text, not even an empty space. Towards this end, if you look at the downloadable script, you’ll see that it begins with the opening PHP tag, not with any HTML.
To communicate to the browser that a file is being sent, the PHP script should invoke the header() function three times. First, that function is used to indicate the type of content to follow:
header("Content-Type: {$file['file_type']}\n");
As you can see, this is where the stored MIME type comes into play. This value would be application/pdf for a PDF file and image/png for a PNG file.
Next, the script indicates how the file should be handled and what its name is:
header("Content-Disposition: attachment; filename=\"{$file['name']}\"\n");
The Content-Disposition value should be either inline or attachment. This is an instruction to the browser: inline recommends that the browser display the file within the browser; attachment is a recommendation to download the file. You might use the former for an image but the latter for a Word doc or ZIP file. Note that these are only recommendations; what the browser actually does with the file will depend upon many things, including the file type, the operating system, and the specific browser in use. Similarly, usage of the filename value can also vary. Ideally, if the file is downloaded to the user’s machine, it will be given that name.
The last recommended header is an indication of the size of the data about to be sent:
header("Content-Length: {$file['file_size']}\n");
Finally, with the browser informed as to what is about to happen, the file itself—the actual data—can be sent:
echo $file['file'];
And that’s all there is to it! You can experiment with storing and retrieving different file types to see the end result. If you want to see the browser display the file instead of downloading it (when the type is appropriate), change the Content-Disposition to inline (see Figure 2).
)
Figure 2 The display of a PDF within the browser window.