MySQL Cookbook

Retrieving Images or Other Binary Data

Problem

Okay, you can store images or other binary data values in your database, using the techniques discussed in Recipe 17.7. 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 Recipe 17.7, it’s difficult to issue a statement manually that stores a literal image value. However, there is no problem at all entering a query that retrieves an image:

mysql> SELECT * FROM image WHERE id = 1;

But binary information tends not to display well on ASCII 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 (and 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, though that is more common for non-image binary data such as PDF files. Downloading is discussed in Recipe 17.10.

Displaying an image in a web page is done by including an <img> tag in that 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, if the image file iceland.jpg is located in the directory /mcb/images under the server’s document root, you can reference it like this:

<img src="/mcb/images/iceland.jpg" ...