Retrieving Images or Other Binary Data
Problem
You can store images or other binary data values in your database, using the techniques discussed in Storing Images or Other Binary Data. But how do you get them back out?
Solution
You need nothing more than a
SELECT
statement. Of
course, what you do with the information after
you retrieve it might be a little more involved.
Discussion
As described in Storing Images or Other Binary Data, it’s difficult to issue
a statement manually that stores a literal image value into a
database, so normally you use
LOAD_FILE()
or
write a script that encodes the image data for insertion. However,
there is no problem at all entering a statement that retrieves an
image:
SELECT * FROM image WHERE id = 1;
But binary information tends not to show up well on text-display devices, so you probably don’t want to do this interactively from the mysql program unless you want your terminal window to turn into a horrible mess of gibberish, or possibly even to lock up. It’s more common to use the information for display in a web page. Or you might send it to the client for downloading, although that is more common for nonimage binary data such as PDF files. (Serving Query Results for Download discusses downloading.)
To display an image in a web page, include an <img>
tag in the page that tells the client’s web browser where to get the image. If you’ve stored images as files in a directory that the web server has access to, you can refer to an image directly. For example, ...
Get MySQL Cookbook, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.