Storing and Retrieving Binary Data
One of the more common questions people ask regarding MySQL is how to store and retrieve binary data in a database. Binary data includes items such as an image, a PDF file, or a video clip. These items have to be handled differently than you would the things you normally file in a database: simple text strings, dates, and numbers.
For this example, I'll use PHP as the programming language. This is a logical choice, as Web content often includes binary data (specifically images). If you're using JSP or even CGI scripts written in Perl, you should still be able to follow along. I'll start by creating the database. Then I'll write one page that stores the binary data and lists all of the currently stored records. The third step will be to create a script that retrieves and displays the data.
|
In this chapter I teach how to store binary data, but there is some debate as to whether or not you should store binary data in your database. The alternative would be to store the file on the server in a convenient location, and then store the filename in the database. There are pros and cons to both methods.
On the one hand, storing binary data in a database allows you to back it up at the same time as you back up the rest of the data. It also makes those files accessible to anyone with access to the database. This means that the binary files can be accessible to multiple computers, as well.
On the other hand, you'll need to write extra SQL and code in order to store and retrieve this information. Your application may have decreased performance, too.
In the end, it's really up to the developer and the needs of the application as to which method you use, but it's great that MySQL offers different options. You should experiment with both approaches to see which you like the best.
|
Creating the database
To store binary data in your database, you should first make a column of type BLOB.
CREATE TABLE binary (
binary_item BLOB
)
MySQL supports different sizes of BLOBsTINYBLOB, MEDIUMBLOB, BLOB, and LONGBLOBeven though the SQL standard does not. There's also BINARY and VARBINARY, which store smaller amounts of binary data, too small to use in this example.
For this example I will store imagesJPEGs, GIFs, and PNGsin a database. Along with the actual image, I will store the image's original name, its MIME type, its size in pixel dimensions, and its size in bytes. Of this metadata, the type is the most important, as PHP needs to know that information in order to properly show the image in a Web browser.
To create the database:
1. | Log in to mysql as a user with permission to create a new database.
| 2. | Create the new database ( Figure 12.1).
CREATE DATABASE binary_data;
USE binary_data; | | | 3. | Create the necessary table ( Figure 12.2).
CREATE TABLE images (
image_id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
image BLOB,
image_name VARCHAR(60) NOT NULL,
image_type VARCHAR(12) NOT NULL,
image_width MEDIUMINT UNSIGNED NOT NULL,
image_height MEDIUMINT UNSIGNED NOT NULL,
image_size INT UNSIGNED NOT NULL,
uploaded_date TIMESTAMP,
PRIMARY KEY (image_id),
INDEX (uploaded_date)
);
For demonstration purposes, I'll be creating a new images table within the binary_data database. The image's size is stored in two separate fields, both of type MEDIUMINT. I'm also adding an uploaded_date TIMESTAMP column that will reflect when an image was added to the database.
|
 Tip
In reality, a field of type BLOB is exactly like a TEXT field, except that it is case-sensitive, whereas TEXT fields are case-insensitive.
Storing binary data
Once you've established a table field that can take binary data, you could use the LOAD_FILE() function to store data in it. Using the SET notation, an example INSERT would be:
INSERT INTO tablename SET image_col= LOAD_FILE('/path/to/file.ext'), SET other_col='value'...
The LOAD_FILE() function takes as its lone argument the full path and name of the file on the server (e.g., C:/data/myfile.xls). There are two requirements for using this method:
This last requirement may be an issue, as, for security purposes, only administrators should have FILE permission.
An alternative to using LOAD_FILE() is to have your programming language read the file into a variable and then use that variable in a query, just as you would any other piece of data. Using this method, the first of the two preceding conditions still applies but the second does not.
To demonstrate this, I'll create a PHP script that uploads an image from a form and stores it in the database. I will use the Improved MySQL extension functions, discussed in Chapter 7, "MySQL and PHP." This assumes that you are using at least version 5 of PHP, with support for this extension (the version of MySQL will not matter in this example).
To store binary data:
| | 1. | Begin a new PHP script in your text editor ( Script 12.1).
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/ xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/ xhtml" xml:lang="en" lang="en">
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8" />
<title>Storing Images in MySQL</title>
</head>
<body>
<?php
This page will use a combination of XHTML and PHP to display a form and then handle a file upload. This is a standard XHTML header, followed by the initial PHP tag.
Script 12.1. The store_binary.php script allows the user to select an image to be stored in the database. It also lists the currently stored images.
1 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
2 "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
3 <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
4 <head>
5 <meta http-equiv="content-type" content="text/html; charset=utf-8" />
6 <title>Storing Images in MySQL</title>
7 </head>
8 <body>
9 <?php
10
11 // ***** store_binary.php *****
12 // ***** Script 12.1 *****
13 // This script allows the user to upload an image.
14 // The image is then stored in the binary.images table.
15
16 // Connect to MySQL:
17 $dbc = @mysqli_connect('localhost', 'username', 'password', 'binary_data') or die
 ('Could not
connect to MySQL: ' . mysqli_connect_error() . '</body></html>');
18
19 if (isset($_POST['submitted'])) { // If the form has been submitted...
20
21 // Check for an uploaded file:
22 if (isset($_FILES['upload'])) {
23
24 // Validate the type. Should be jpeg, jpg, gif, or png.
25 $allowed = array ('image/gif', 'image/jpeg', 'image/jpg', 'image/pjpeg',
'image/png');
26
27 if (in_array($_FILES['upload']['type'], $allowed)) { // OK
28
29 // Get the image's size in pixels:
30 $image_info = getimagesize($_FILES['upload']['tmp_name']);
31
32 // Read the uploaded file into a variable:
33 $image = fread(fopen($_FILES['upload']['tmp_name'], 'r'),
$_FILES['upload']['size']);
34
35 // Secure the data:
36 $image = mysqli_real_escape_string($dbc, $image);
37 $name = mysqli_real_escape_string($dbc, $_FILES['upload']['name']);
38 $size = (int)$_FILES['upload']['size'];
39
40 // Generate the query:
41 $q = "INSERT INTO images (image, image_name, image_type, image_width,
image_height,
image_size) VALUES ('$image', '$name', '{$_FILES['upload']['type']}',
$image_info[0],
$image_info[1], $size)";
42
43 // Execute the query:
44 $r = mysqli_query ($dbc, $q);
45
46 // Print a message indicating success:
47 if (mysqli_affected_rows($dbc) == 1) {
48 echo '<p><font color="green">The image has been stored!</font></p>';
49 } else {
50 echo '<p><font color="red">The image could not be stored in the
database!</font></p>';
51 echo '<p><font color="red">MySQL reported: '. mysqli_error($dbc) .'<
/font></p>';
52 }
53
54 } else { // Invalid type.
55 echo '<p><font color="red">Please upload a JPEG, GIF, or PNG image.</
font></p>';
56 }
57
58 // Remove the file from the server:
59 if (file_exists($_FILES['upload']['tmp_name']) and
is_file($_FILES['upload']['tmp_name'])) {
60 unlink ($_FILES['upload']['tmp_name']);
61 }
62
63 } else { // No file uploaded.
64 echo '<p><font color="red">Please upload a JPEG, GIF, or PNG image smaller than
512KB.</font></p>';
65 }
66
67 } // End of submitted IF.
68
69 // Display the form:
70 echo '<h2>Use this form to store an image in the database:</h2>
71 <p>(Images must be of type JPEG, GIF, or PNG.)</p>
72 <form action="store_binary.php" method="post" enctype="multipart/form-data">
73 <input type="hidden" name="MAX_FILE_SIZE" value="524288" />
74 <p>Select an image to upload: <input type="file" name="upload" /></p>
75 <input type="hidden" name="submitted" value="true" />
76 <input type="submit" name="submit" value="Submit!" />
77 </form>
78 <br />';
79
80
81 // Show the current list of images.
82 // Link each to view_image.php.
83 echo '<h2>Currently Stored Images</h2><p>(Click an image\'s name to view it.)</p>';
84
85 // Create the query:
86 $q = 'SELECT image_id, image_name FROM images ORDER BY uploaded_date DESC';
87
88 // Execute the query:
89 $r = mysqli_query ($dbc, $q);
90
91 // Check the results:
92 if (mysqli_num_rows($r) > 0) {
93
94 // Display each item in a list.
95 echo '<ul>';
96
97 while ($row = mysqli_fetch_array ($r, MYSQLI_NUM)) {
98 echo "<li><a href=\"view_image.php?i=$row[0]\">$row[1]</a></li>\n";
99 }
100
101 echo '<ul>';
102
103 // Clean up:
104 mysqli_free_result($r);
105
106 } else { // No records returned.
107 echo '<p>There are currently no
stored images.
108 }
109
110 // Close the database connection:
111 mysqli_close($dbc);
112
113 ?>
114 </body>
115 </html>
|
| | | 2. | Connect to MySQL.
$dbc = @mysqli_connect('localhost', 'username', 'password', 'binary_data') or die ('Could
not connect to MySQL: ' . mysqli_ connect_error() . '</body></html>');
If the form has been submitted, an INSERT query is run. Whenever the page is viewed, whether or not the form has been submitted, a SELECT query is run. For this reason, the script will always need a database connection, so that's done first.
The code itself comes from Chapter 7. If a connection could not be made, the error is reported ( Figure 12.3) and the HTML page is completed.
| | | 3. | Check if the form has been submitted.
if (isset($_POST['submitted'])) {
Since this script will both display and handle a form, a conditional has to check for the form's submission. This first part of code (from this point until the closing curly brace) will be run only if the form was submitted (and the POST variable submitted has a value).
| | | 4. | Check that a file of the proper type was uploaded.
if (isset($_FILES['upload'])) {
$allowed = array ('image/gif', 'image/jpeg', 'image/jpg', 'image/pjpeg', 'image/png');
if (in_array($_FILES['upload'] ['type'], $allowed)) {
The first step is to see if anything was uploaded at all. Then I create an array of allowable file types, using the MIME types as the possible values. If the uploaded file is of one of these types, then it can be stored in the database.
| | | 5. | Get the image's information and assign the uploaded image file to a string.
$image_info = getimagesize($_FILES ['upload']['tmp_name']);
$image = fread(fopen($_FILES ['upload']['tmp_name'], 'r'), $_FILES['upload']['size']);
The first line just applies the getimagesize() function so that the script can know the image's height and width in pixels.
The second line is the most important one in the entire script. In this one step, the uploaded file (referred to by $_FILES['upload']['tmp_name') is read into a string called $image. To read the image, it is opened using the fopen() function, with the size of the file as the amount of data to read. The opened file is then read with fread() and assigned to $image. You can break this one line into two separate steps fopen() and fread()to make it easier to understand.
| | | 6. | Secure the query data and define the query.
$image = mysqli_real_escape_string ($dbc, $image);
$name = mysqli_real_escape_string ($dbc, $_FILES['upload']['name']);
$size = (int)$_FILES['upload'] ['size'];
$q = "INSERT INTO images (image, image_name, image_type, image_width, image_height,
image_size) VALUES ('$image', '$name', '{$_FILES ['upload']['type']}', $image_info[0],
$image_info[1], $size)"; Binary data must be escaped prior to use in a query because it contains all sorts of problematic characters that will otherwise break the query. The mysqli_real_escape_string() function is used to accomplish this. The same function is also applied to the image's name, as that comes from the user's computer and could be dangerous. Finally, I type cast the file's size as an integer as an extra precaution.
The query itself is straightforward: a simple INSERT. Since $image has the value of the binary data that is the uploaded file, it can be used as if it were any other string of text.
| 7. | Query the database and report on the success ( Figure 12.4).
$r = mysqli_query ($dbc, $q);
if (mysqli_affected_rows($dbc) == 1) {
echo '<p><font color="green"> The image has been stored! </font></p>';
} else {
echo '<p><font color="red"> The image could not be stored in the database!</font></p>';
echo '<p><font color="red"> MySQL reported: '. mysqli_error ($dbc) .'</font></p>';
}
I've included a line for debugging purposes here, should the query fail. You can also print out the query to see what it is, although you'll print out a lot of gibberish in the process, that gibberish being the binary image data.
| | | 8. | Complete every conditional and remove the file from the server.
} else {
echo '<p><font color="red">Please upload a JPEG, GIF, or PNG image.</
font></p>';
}
if (file_exists($_FILES ['upload']['tmp_name']) and is_file
($_FILES['upload'] ['tmp_name'])) {
unlink ($_FILES['upload'] ['tmp_name']);
}
} else {
echo '<p><font color="red"> Please upload a JPEG, GIF, or PNG image smaller than
512KB.</font></p>';
}
}
The first else clause applies if the wrong type of file was uploaded ( Figure 12.5). The second applies if no file was uploaded.
In the middle, the actual file on the server (which was put into a temporary directory upon upload) is deleted. This line applies if the wrong type of file was uploaded, if the file was uploaded and successfully stored in the database, or if the file was uploaded but couldn't be stored in the database because of a MySQL error.
| | | 9. | Create the HTML form ( Figure 12.6).
echo '<h2>Use this form to store an
image in the database:</h2>
<p>(Images must be of type JPEG, GIF, or PNG.)</p>
<form action="store_binary.php" method="post" enctype="multipart/ form-data">
<input type="hidden" name= "MAX_FILE_SIZE" value="524288" />
<p>Select an image to upload: <input type="file" name="upload" /></p>
<input type="hidden" name="submitted" value="true" />
<input type="submit" name="submit" value="Submit!" />
</form>
<br />';
The most important pieces of this form are the form's action (referring back to this same script), its enctype (which allows for a file to be uploaded), and the name of the file being uploaded (which should match the name used earlier in the script).
You should also adjust the MAX_FILE_SIZE value to accommodate the largest image you will want to store. In my example, up to a 512 Kbyte image will work just fine.
| | | 10. | Show a list of currently stored images ( Figure 12.7).
echo '<h2>Currently Stored Images</h2><p>(Click an image\'s name to view it.)</p>';
$q = 'SELECT image_id, image_name FROM images ORDER BY uploaded_date DESC';
$r = mysqli_query ($dbc, $q);
if (mysqli_num_rows($r) > 0) { echo '<ul>';
while ($row = mysqli_fetch_array ($r, MYSQLI_NUM)) {
echo "<li><a href= \"view_image.php?i=$row[0]\ ">$row[1]</a></li>\n";
}
echo '<ul>';
mysqli_free_result($r);
} else {
echo '<p>There are currently no stored images.</p>';
}
This query retrieves every image ID and image name from the database. If some records are returned, they are printed within an unordered list. Each is linked to view_image.php, passing along the image's ID as an argument ( Figure 12.8).
| | | 11. | Complete the script.
mysqli_close($dbc);
?>
</body>
</html> | 12. | Save the script as store_binary.php, upload it to your Web server, and test in your Web browser ( Figure 12.9).
|
Tips
One potential problem with this script is that it relies upon the Web browser's identification of a file's MIME type. You could instead use a function like mime_content_type() for this purpose. If the uploaded image file does not contain an extension on the user's computer, it may not pass the validation tests.
Retrieving binary data
Now that I've written a script for storing images in a database, I'll create another that will retrieve and display the image in a Web browser. The retrieval part is easy: it's just a SELECT query. Getting the image to display from that point is a matter of telling the Web browser what kind of data to expectan image of a certain typeand then sending the image to the browser. PHP's header() function can let the browser know what type of data is forthcoming, as you'll see in this script.
To retrieve and display binary data:
| | 1. | Create a new PHP script ( Script 12.2).
No HTML is required by this script, as it only displays an image.
Script 12.2. The view_image.php script retrieves an image from a database and sends it to the Web browser.
1 <?php
2
3 // ***** view_image.php *****
4 // ***** Script 12.2 *****
5 // This script displays an image stored in the database.
6 // No HTML required as this page just shows an image.
7
8 if (isset($_GET['i'])) { // Need an image number.
9
10 // Type cast the number for security:
11 $i = (int) $_GET['i'];
12
13 if ($i > 0) { // Must be a positive integer!
14
15 // Connect to MySQL:
16 $dbc = @mysqli_connect('localhost', 'username', 'password', 'binary_data')
or die
('Could not connect to MySQL: ' . mysqli_connect_error());
17
18 // Retrieve the image information.
19 $q = "SELECT image, image_name, image_type, image_size FROM images WHERE
image_id=$i";
20
21 // Execute the query:
22 $r = mysqli_query ($dbc, $q);
23
24 // Check the results:
25 if (mysqli_num_rows($r) == 1) {
26
27 // Retrieve the image information:
28 $row = mysqli_fetch_array ($r, MYSQLI_ASSOC);
29
30 // Clean up:
31 mysqli_free_result($r);
32
33 // Send the image to the browser:
34 header ("Content-Type: {$row['image_type']}\n");
35 header ("Content-disposition: inline; filename=\"{$row['image_name']}\"\n");
36 header ("Content-Length: {$row['image_size']}\n");
37 echo $row['image'];
38
39 }
40
41 // Close the database connection:
42 mysqli_close($dbc);
43
44 }
45
46 }
47 ?>
|
| 2. | Check for and validate an image number.
if (isset($_GET['i'])) {
$i = (int) $_GET['i'];
if ($i > 0) {
This page will be called by using the syntax view_image.php?i=x, where x refers to the image_id in the database for the corresponding image ( Figure 12.10). This conditional first checks that $_GET['i'] exists and then checks that it has an integer value greater than 0.
| 3. | Connect to the database.
$dbc = @mysqli_connect('localhost', 'username', 'password', 'binary_data') or die ('Could not
connect to MySQL: ' . mysqli_connect_error()); | | | 4. | Retrieve the image and image information from the database.
$q = "SELECT image, image_name, image_type, image_size FROM images WHERE image_id=$i";
$r = mysqli_query ($dbc, $q);
if (mysqli_num_rows($r) == 1) {
$row = mysqli_fetch_array ($r, MYSQLI_ASSOC);
mysqli_free_result($r);
The query returns the image itself (the binary data), its name, and its size in bytes from the table based upon the image_id value (which corresponds to i, passed to the script). If the query is successful, an array of information is assigned to the $row variable.
| 5. | Send the image to the Web browser.
header ("Content-Type: {$row['image_type']}\n");
header ("Content-disposition: inline; filename=\"{$row['image_name']}\"\ n");
header ("Content-Length: {$row['image_size']}\n");
echo $row['image'];
The first use of the header() function will tell the browser what type of data to expect, which is the stored MIME type of the image. Then the browser is told to display this data inline (in the Web browser) and the image's name is provided. The content-length value is also sent, which isn't required but is a good idea. The length value is the size of the image in bytes. Finally, after all of this preparation, the image itself is sent to the browser by simply echoing it.
| | | 6. | Complete the script.
}
mysqli_close($dbc);
}
}
?> | 7. | Save the file as view_image.php, upload it to your Web server, and test in a Web browser ( Figure 12.11) by clicking the links in store_binary.php.
|
Tips
You can use the view_image.php script to display images anywhere within a Web page by using the code <img src="view_image.php?i=89" />. You can use the stored image width and height to add that information to this image tag. Depending upon your Web server, you may find that the page loads and the image is displayed rather slowly. This is the performance issue mentioned earlier, and it may be a reason not to use this technique. If your image only partially displays, this is probably because it was too big for the blob column in the database. Change the column's definition, perhaps to LONGBLOB, so that more data can be stored.
|
Js editor
Website development