Storing Images in MYSQL
This is the article about the way of storing images in mysql database using the blob column type in mysql. When dealing with a large amount of images, it is often useful to store them in a database. This makes accessing and changing the pictures easy and streamlined, even when you need to change more than one at once. One common task performed on stored images is to resize the image. Uploading and resizing an image using PHP and MySQL can be very simple, and it allows you to do many things, like create a batch of thumbnails for your image gallery, or just display a given image in a size other than the original.
To begin, let us discuss about blob data type. It is a Binary Large Object. In short, a blob is a field type for storing Binary Data. Images are made up of binary data. This data cannot be stored in normal text fields in MySQL (such as text or varchar). Instead, we must use the blob type. MYSQL has four types of blobs:
- tinyblob: about 256B
The actual type you choose doesn’t really matter, since it only stores as much data as you need. Therefore I’d recommend using the longblob type. It’s better to be prepared for files larger than 16MB than having to change the column later.
Creating a Database Table
To begin with storing images in MySQL, let’s create a database table. For the purposes of this article, I’ll assume you already have a database set up with the following details:
Server address: localhost
Database name: phpfresher_demo
Database username: phpfresher_demo
Database password: phpfresher3
The following listing shows how you can create this database on your server.
Creating a database and a database user (listing-1.txt)
mysql> create database phpfresher_demo;
Query OK, 1 row affected (0.00 sec)
mysql> grant all on phpfresher_demo.* to phpfresher_demo@localhost identified by 'phpfresher123';
Query OK, 0 rows affected (0.00 sec)
mysql> use phpfresher_demo;
Database changed
Let’s now create the database table. In addition to storing the file data, we are also going to the store following:
- A unique ID for the image. We’ll use the serial type for this (this is a shortcut for bigint unsigned auto_increment).
- The original filename of the image. We’ll use varchar(255) for this, meaning we can easily index the table by the filename if we wanted to.
- The file mime type. We’re allowing users to upload images, which might be in jpg, png or gif format. We use the mime type when sending the image back to users. We could determine the mime type when required, but this is never going to change for a file so we might as well save some future processing power by determining it when the database record is created.
- The size of the file. When we send the image back to the user we want to use this value to tell the user’s browser how big the image is. Since this value won’t change, we can simply store it when we insert the image into the database.
The statement used to create the table is shown in the following listing.
Creating a database table in which to store images (listing-2.sql)
create table images (
image_id serial,
filename varchar(255) not null,
mime_type varchar(255) not null,
file_size int not null,
file_data longblob not null,
primary key (image_id),
index (filename)
);
Connecting to the Database
Let’s now look at some PHP code to connect to the database. We’re going to include this in a global PHP script that we’ll include from other scripts. In this code, we first try and connect to the database server. Once that connection is made we try and select our database. If either of these steps fails we output an error message and exit.
listing-3:Connecting to the database (globals.php)
<?php
$db = mysql_connect('localhost', 'phpfresher_demo', 'phpfresher123');
if (!$db) {
echo "Unable to establish connection to database server";
exit;
}
if (!mysql_select_db('phpfresher_demo', $db)) {
echo "Unable to connect to database";
exit;
}
?>
The Upload Form
Now that we’ve created a database table to store images and written code to connect to the database, let’s create an upload form so users can upload images. In order to accept uploaded files from a form, you must add the enctype attribute to the HTML <form> tag. The value of this attribute must be multipart/form-data, which indicates that binary data will be uploaded.
Including this attribute means the PHP superglobal $_FILES will be populated with information about the uploaded file. We’ll look at this more closely soon. In this example, the upload form will be stored in a file called upload.php. It will post its values to a file called process.php.
listing-4:The image upload form (upload.php)
<?php
require_once('globals.php');
?>
<html>
<head>
<title>Upload an Image</title>
</head>
<body>
<div>
<h1>Upload an Image</h1>
<p>
<a href="./">View uploaded images</a>
</p>
<form method="post" action="process.php" enctype="multipart/form-data”>
<div>
<input type="file" name="image" />
<input type="submit" value="Upload Image" />
</div>
</form>
</div>
</body>
</html>
It is important to use the enctype=”multipart/form-data” so that the browser uploads the binarydata correctly.
Handling the File Upload
Now that users can select a file and upload it, we must implement the form processor. We will do this in the process.php script that the upload form points to. The details of the uploaded file are stored in the $_FILES superglobal. One of the fields included in the upload data is an error code. This helps you determined if the file was successfully uploaded.
The following listing shows a function we can use to check that a file was successfully uploaded based on its error. If the file was not successfully uploaded an exception is thrown with a relevant error message. If successful, nothing happens.
Asserting a file upload was successful (listing-5.php)
<?php
function assertValidUpload($code)
{
if ($code == UPLOAD_ERR_OK) {
return;
}
switch ($code) {
case UPLOAD_ERR_INI_SIZE:
case UPLOAD_ERR_FORM_SIZE:
$msg = 'Image is too large';
break;
case UPLOAD_ERR_PARTIAL:
$msg = 'Image was only partially uploaded';
break;
case UPLOAD_ERR_NO_FILE:
$msg = 'No image was uploaded';
break;
case UPLOAD_ERR_NO_TMP_DIR:
$msg = 'Upload folder not found';
break;
case UPLOAD_ERR_CANT_WRITE:
$msg = 'Unable to write uploaded file';
break;
case UPLOAD_ERR_EXTENSION:
$msg = 'Upload failed due to extension';
break;
default:
$msg = 'Unknown error';
}
throw new Exception($msg);
}
?>
Once we know the upload was successful, we must perform some other validation on the upload. We are checking the following things:
Ensuring the file saved on the server was in fact from a PHP upload and not tampered with on the server. This is done using is_uploaded_file().Ensuring the uploaded file is an image. We can use the getImageSize() function to determine this. To make checking of all of these conditions simpler, we wrap the validation in try/catch block. We can then handle the exception to retrieve any error messages.
The following listing shows how we validate the uploaded file. This includes firstly ensuring the upload data is in $_FILES, then making use of the assertValidUpload() function.
Validating the uploaded file (listing-6.php)
<?php
$errors = array();
try {
if (!array_key_exists('image', $_FILES)) {
throw new Exception('Image not found in uploaded data');
}
$image = $_FILES['image'];
// ensure the file was successfully uploaded
assertValidUpload($image['error']);
if (!is_uploaded_file($image['tmp_name'])) {
throw new Exception('File is not an uploaded file');
}
$info = getImageSize($image['tmp_name']);
if (!$info) {
throw new Exception('File is not an image');
}
}
catch (Exception $ex) {
$errors[] = $ex->getMessage();
}
?>
Once we know the uploaded file is valid we can insert it into the database. The following listing shows the entire upload.php script. When validating the upload we used the getImageSize() function. This is useful not only to determine if a file is an image, but also what kind of image it is. We use the returned mime value to fill the mime_type column in the database.
listing-7:The full upload script (process.php)
<?php
require_once('globals.php');
function assertValidUpload($code)
{
if ($code == UPLOAD_ERR_OK) {
return;
}
switch ($code) {
case UPLOAD_ERR_INI_SIZE:
case UPLOAD_ERR_FORM_SIZE:
$msg = 'Image is too large';
break;
case UPLOAD_ERR_PARTIAL:
$msg = 'Image was only partially uploaded';
break;
case UPLOAD_ERR_NO_FILE:
$msg = 'No image was uploaded';
break;
case UPLOAD_ERR_NO_TMP_DIR:
$msg = 'Upload folder not found';
break;
case UPLOAD_ERR_CANT_WRITE:
$msg = 'Unable to write uploaded file';
break;
case UPLOAD_ERR_EXTENSION:
$msg = 'Upload failed due to extension';
break;
default:
$msg = 'Unknown error';
}
throw new Exception($msg);
}
$errors = array();
try {
if (!array_key_exists('image', $_FILES)) {
throw new Exception('Image not found in uploaded data');
}
$image = $_FILES['image'];
// ensure the file was successfully uploaded
assertValidUpload($image['error']);
if (!is_uploaded_file($image['tmp_name'])) {
throw new Exception('File is not an uploaded file');
}
$info = getImageSize($image['tmp_name']);
if (!$info) {
throw new Exception('File is not an image');
}
}
catch (Exception $ex) {
$errors[] = $ex->getMessage();
}
if (count($errors) == 0) {
// no errors, so insert the image
$query = sprintf(
"insert into images (filename, mime_type, file_size, file_data)
values ('%s', '%s', %d, '%s')",
mysql_real_escape_string($image['name']),
mysql_real_escape_string($info['mime']),
$image['size'],
mysql_real_escape_string(
file_get_contents($image['tmp_name'])
)
);
mysql_query($query, $db);
$id = (int) mysql_insert_id($db);
// finally, redirect the user to view the new image
header('Location: view.php?id=' . $id);
exit;
}
?>
<html>
<head>
<title>Error</title>
</head>
<body>
<div>
<p>
The following errors occurred:
</p>
<ul>
<?php foreach ($errors as $error) { ?>
<li>
<?php echo htmlSpecialChars($error) ?>
</li>
<?php } ?>
</ul>
<p>
<a href="upload.php">Try again</a>
</p>
</div>
</body>
</html>
If the upload is successful we redirect the user to the view.php script. We’ll implement this script shortly. We pass the ID stored in the database of the newly uploaded image. We retrieve this using the mysql_insert_id() method. If the upload is not successful, the reason for the failure is displayed and the user can go back and try another upload.
Sending an Image
After an image is uploaded the user is shown that image. This is achieved using the view.php script that the user is redirected to after uploaded their image. We’ll also redirect to this script from the image listing we’ll implement in the next section.To implement this script we need to read in the ID that is passed to the script in the id URL parameter. We then try to load the image from the database based on the given ID.
If an image cannot be found for the given ID (or if the ID isn’t included) we send a 404 File Not Found error.If the image is found we can output it to user. We must send various headers so the user’s browser can understand the response. The only mandatory header is the Content-type header. This tells the browser to expect an image. The value we send with this is stored in the mime_type column in our database.
We also send the Content-length header. This tells the browser how big the file is. This is especially useful for extremely large files, as it allows the browser to tell the user how long the file will take to download.
listing-8:Downloading an image stored in the database (view.php)
<?php
require_once('globals.php');
try {
if (!isset($_GET['id'])) {
throw new Exception('ID not specified');
}
$id = (int) $_GET['id'];
if ($id <= 0) {
throw new Exception('Invalid ID specified');
}
$query = sprintf('select * from images where image_id = %d', $id);
$result = mysql_query($query, $db);
if (mysql_num_rows($result) == 0) {
throw new Exception('Image with specified ID not found');
}
$image = mysql_fetch_array($result);
}
catch (Exception $ex) {
header('HTTP/1.0 404 Not Found');
exit;
}
header('Content-type: ' . $image['mime_type']);
header('Content-length: ' . $image['file_size']);
echo $image['file_data'];
?>
Outputting list of Images
The final step is to list to output a list of all files that have been uploaded. We can do this by performing a select on the table, then looping over the returned files. It is important to specify which fields you want to retrieve from the table, since if you select all (using *) it will result in the file data of every single image being returned. This may bog down your server significantly.
In the following code, we retrieve a list of image IDs and corresponding filenames (for display purposes) and build an array that we can easily loop over. This code also checks if there are no images and displays a corresponding message accordingly.
listing-9: Displaying a list of uploaded images (index.php)
<?php
require_once('globals.php');
$query = sprintf('select image_id, filename from images');
$result = mysql_query($query, $db);
$images = array();
while ($row = mysql_fetch_array($result)) {
$id = $row['image_id'];
$images[$id] = $row['filename'];
}
?>
<html>
<head>
<title>Uploaded Images</title>
</head>
<body>
<div>
<h1>Uploaded Images</h1>
<p>
<a href="upload.php">Upload an image</a>
</p>
<ul>
<?php if (count($images) == 0) { ?>
<li>No uploaded images found</li>
<?php } else foreach ($images as $id => $filename) { ?>
<li>
<a href="view.php?id=<?php echo $id ?>">
<?php echo htmlSpecialChars($filename) ?>
</a>
</li>
<?php } ?>
</ul>
</body>
</html>
Conclusion
This article covered the uploading of images in PHP and inserting them into a BLOB field in MySQL.