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?
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.
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, if the image file iceland.jpg is located in the /usr/local/lib/mcb/images directory, you
can reference it like this:
<img src="/usr/local/lib/mcb/images/iceland.jpg" />
If you use this approach, make sure that each image filename has
an extension (such as .gif or
.png) that enables the web server
to determine what kind of Content-Type:
header to generate when it
sends the file to the client.
If the images are stored in a database table instead, or in a
directory that is not accessible to the web server, the <img>
tag can refer to a script that
knows how to fetch images and send them to clients. To do this, the
script should respond by sending a Content-Type:
header that indicates the
image format, a Content-Length:
header that indicates the number of bytes of image data, a blank line,
and finally the image itself as the body of the response.
The following script, display_image.pl, demonstrates how to serve
images over the Web. It requires a name
parameter that indicates which image to
display, and allows an optional location
parameter that specifies whether to
retrieve the image from the image
table or from the filesystem. The default is to retrieve image data
from the image
table. For example,
the following URLs display an image from the database and from the
filesystem, respectively:
http://localhost/cgi-bin/display_image.pl?name=iceland.jpg http://localhost/cgi-bin/display_image.pl?name=iceland.jpg;location=fs
The script looks like this:
#!/usr/bin/perl # display_image.pl - display image over the Web use strict; use warnings; use CGI qw(:standard escapeHTML); use FileHandle; use Cookbook; sub error { my $msg = escapeHTML ($_[0]); print header (), start_html ("Error"), p ($msg), end_html (); exit (0); } # ---------------------------------------------------------------------- # Default image storage directory and pathname separator # (CHANGE THESE AS NECESSARY) my $image_dir = "/usr/local/lib/mcb/images"; # The location should NOT be within the web server document tree my $path_sep = "/"; # Reset directory and pathname separator for Windows/DOS if ($^O =~ /^MSWin/i || $^O =~ /^dos/) { $image_dir = "C:\mcb\images"; $path_sep = "\"; } my $name = param ("name"); my $location = param ("location"); # make sure image name was specified defined ($name) or error ("image name is missing"); # use default of "db" if the location is not specified or is # not "db" or "fs" (defined ($location) && $location eq "fs") or $location = "db"; my $dbh = Cookbook::connect (); my ($type, $data); # If location is "db", get image data and MIME type from image table. # If location is "fs", get MIME type from image table and read the image # data from the filesystem. if ($location eq "db") { ($type, $data) = $dbh->selectrow_array ( "SELECT type, data FROM image WHERE name = ?", undef, $name) or error ("Cannot find image with name $name"); } else { $type = $dbh->selectrow_array ( "SELECT type FROM image WHERE name = ?", undef, $name) or error ("Cannot find image with name $name"); my $fh = new FileHandle; my $image_path = $image_dir . $path_sep . $name; open ($fh, $image_path) or error ("Cannot read $image_path: $!"); binmode ($fh); # helpful for binary data my $size = (stat ($fh))[7]; read ($fh, $data, $size) == $size or error ("Failed to read entire file $image_path: $!"); $fh->close (); } $dbh->disconnect (); # Send image to client, preceded by Content-Type: and Content-Length: # headers. print header (-type => $type, -Content_Length => length ($data)); print $data;
18.117.101.178