Database Reference
In-Depth Information
the filesystem, remember to delete both the database row
and
the image file the row
points to.
store_image.pl
stores each image both in the database and in the filesystem for illustra‐
tive purposes, but of course that makes it inefficient. Earlier, I mentioned that if you use
this script as a basis for your own applications, you should modify it to store images
only in one place—either in the database or in the filesystem—not in both places:
• To adapt the script to store images only in MySQL, there is no need to create an
image directory, and you can delete the code that checks for that directory's exis‐
tence and that writes image files there.
• To adapt the script for storage only in the filesystem, drop the
data
column from
the
image
table, and modify the
REPLACE
statement to omit that column.
These modifications also apply to the
display_image.pl
image processing script shown
in
Recipe 19.7
.
See Also
Recipe 19.7
shows how to retrieve images for display over the Web.
Recipe 20.8
discusses
how to upload images from a web page for storage into MySQL.
19.7. Serving Images or Other Binary Data
Problem
You can store images or other binary data values in your database, using the techniques
discussed in
Recipe 19.6
. 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
Recipe 19.6
, 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 prob‐
ably don't want to do this interactively from the
mysql
program unless you want your