Select binary data from mysql

Select binary data from mysql

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).

Figure 12.1. To begin this example, I make a new database using the mysql client.

Select binary data from mysql


CREATE DATABASE binary_data;
USE binary_data;


3.

Create the necessary table (Figure 12.2).

Figure 12.2. This single table will store all of the data this example requires.

Select binary data from mysql


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.

Select binary data from mysql
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:

  • The file must already exist on the same computer as the MySQL database.

  • You must be connected to MySQL as a user with FILE permission.

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 
Select binary data from mysql
('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',
Select binary data from mysql
'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'),
Select binary data from mysql
$_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,
Select binary data from mysql
image_height, image_size) VALUES ('$image', '$name', '{$_FILES['upload']['type']}',
Select binary data from mysql
$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) .'<
Select binary data from mysql
/font></p>'; 52 } 53 54 } else { // Invalid type. 55 echo '<p><font color="red">Please upload a JPEG, GIF, or PNG image.</
Select binary data from mysql
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
Select binary data from mysql
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.

Figure 12.3. Connection errors are immediately reported, and the script then stops executing.

Select binary data from mysql


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,
Select binary data from mysql
image_size) VALUES ('$image', '$name', '{$_FILES ['upload']['type']}', $image_info[0],
Select binary data from mysql
$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).

Figure 12.4. If the INSERT query fails, a message indicating such is printed. For debugging purposes, the MySQL error is also printed.

Select binary data from mysql


$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.

The first

else

clause applies if the wrong type of file was uploaded (Figure 12.5). The second applies if no file was uploaded.

Figure 12.5. The script limits what kinds of files can be stored in the database.

Select binary data from mysql

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).

Figure 12.6. This HTML form will allow users to select an image on their hard drive that will be stored in the database.
Select binary data from mysql


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).

Figure 12.7. Each stored image is listed as a link to the view_image.php script.

Select binary data from mysql


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).

Figure 12.8. The HTML source shows how each link passes the image's ID value to the view_image.php script.

Select binary data from mysql


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).

Figure 12.9. Another image has been uploaded and stored by this script.
Select binary data from mysql


Select binary data from mysql
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')
Select binary data from mysql
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
Select binary data from mysql
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.

Figure 12.10. The records in the images table after uploading a few images.
Select binary data from mysql


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

.

Figure 12.11. The view_image.php script retrieves an image from the database and sends it to the Web browser.
Select binary data from mysql


Select binary data from mysql
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.


Select binary data from mysql

How do I query binary in MySQL?

The MySQL BINARY function is used for converting a value to a binary string. The BINARY function can also be implemented using CAST function as CAST(value AS BINARY). The BINARY function accepts one parameter which is the value to be converted and returns a binary string.

How do I query BLOB data in MySQL?

It is a kind of data type in MySQL that can store files or images in the database in binary format. It has four types i.e TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB..
Alter the table and add a column having data type TEXT..
Add content to that column after converting BLOB data to TEXT date..
Drop BLOB column..

What is binary data in MySQL?

The BINARY attribute is a nonstandard MySQL extension that is shorthand for specifying the binary ( _bin ) collation of the column character set (or of the table default character set if no column character set is specified). In this case, comparison and sorting are based on numeric character code values.

How do I select data in MySQL?

Introduction to MySQL SELECT statement First, specify one or more columns from which you want to select data after the SELECT keyword. If the select_list has multiple columns, you need to separate them by a comma ( , ). Second, specify the name of the table from which you want to select data after the FROM keyword.