Storing Images in MySQL with PHP
An introduction to BLOBs
Storing images in MySQL
by Kevin Waterson
Contents
- Isn't this a bad idea?
- What is a BLOB?
- Creating an image table
- The upload form.
- Uploading the image
- The upload() function?
- Display an image from the database
- Displaying all the information
- Storing Thumbnails
- Another Approach
Isn't this a bad idea?
This arguement continues to rage and many lives have been lost trying to prove that this is a Bad Idea(tm). The facts are this method of binary storage is very successful and has many advantages over file system storage. Some advantages are:
- Referential integrity (ACID Consistency)
- Ease of backup
- Saving of Inodes
- Easy categorisation
- No Name collisions
- Storage of Meta Data (data about data)
- Central point of operations
- Replication over multiple servers
Many of the ney sayers will have you believe that storing of images or other binary data creates too much overhead. A large database can be stored on a RAW partition and infact be faster as it has no filesystem overhead to contend with. Some would say that access is slow when SELECTing from the data base, but most of these issues are caused by coders doing things
like:
SELECT * FROM image_table
when all they need is image ID.
It should be remembered also that filesystems are not immune to constraints. The ext3 filesystem only allows 32000 sub-directories, so if each user is given an upload directory a filesystem limitation is effected and only a maximun of 32000 users is possible. This of course can be overcome by doing things like
/S/t/e/Steve.gif
but this of course raised the issue of referential integrity and complexity. Should the system be a webfarm, the issue of how to sync all web servers to have all the files also becomes problematic. The ext3 filesystem itself tries to mimic database operations with the implentation of dir_index option. Funnily enough, this uses a btree index on directories, which is the base for some databases. Many newer filesystems are practically database implementations themselves, and many UNIX filesystems use filenames as indexes to inodes which point to files, a very database-esque implementation.
As PHP has grown, so has the popularity of *nix and apache which has given rise to the LAMP stack (Linux Apache MySQL PHP). The most common filesystem used in Linux is ext3. The ext3 filesystem provides excellent journalling at the expense of speed, and is the slowest filesystem I have tested.
Some have said that a central point of operations is a central point of failure, should the database be corrupted or destroyed everything is lost. We see the same for the file system, it is a central point of failure also should it become corrupted. Infact the file system causes more problems with the possibility of running out of inodes. Then there is the constant maintenance of referential integrity to ensure the database has the correct path to the file on the file system, this can cause a lot of work when moving from one machine to another. Referential intregrity is assured with a database backup. Of course, there is no greater test for referential integrity than moving an application from one filesystem to another, such as from Windows to Linux. We've all been through that nightmare.
Backing up data from a database is a simple one step operation that allows easy restoration or transfer of the data. Can you imagine a completed dataset spread out over 30,000 files instead of a single backup file? For those obsessed with performance issues, the cycles required to tar and bzip2 that amount files at the same time, PLUS and sqldump of the of all the rest of the information, then glue it together and send it off to backup storage (again on the filesystem). This method is full of dangers particularly if the process is interupted, there is no rollback for the filesystem.
When storing files on a file system you must have a unique name for each file. Should your application have an upload directory for example, and two users tried to upload frog.jpg at the same time the results would be less than desirable. You could of course have seperate upload directories for each user, but then we get back to the issue of referential integrity and limitations of the ext3 file system.
Most of the arguements against the storage of images, or binary data, in a database focus on performance and claims that 'file systems were made to do that'. This is not to say that the filesystem is a bad idea either. The real issue becomes one of application and innovation. The greatest constraint you can place, is on yourself by not being open to new advances, and we are constrained to age old technology. Filesystem storage is not always the universal solution for every binary storage problem ever encountered, and neither is database storage. For many implementations, filesystem storage is optimal and preferable, but again the fatal flaw appears to be the insistence that one approach is right 100% of the time...which 99% of the time is false.
The Microsoft Terraserver contains over 3 terrabytes of images stored in database BLOBs.
Quite often it is preferable not to allocate file permissions to applications. By storing images or binary data within the database, access is limitted to application code and file system priveleges are not available for escalation.
With all that said, there are several reasons not to store images or other binary data in a database.
- Better caching
- External proxies
- MySQL can store 'normal" data better
- Data through-put is higher with the filesystem
- Speed of retrieval
Depending on yuor application goal will decide which method of storage/retrieval is best. No one blanket solution fits all needs.
What is a BLOB?
Binary Large Object. In short, a BLOB is a field type for storing Binary Data. MySQL has four BLOB types.
- BLOB
- TINYBLOB
- MEDIUMBLOB
- LONGBLOB
For more information on this we highly recommend you read the MySQL manual.
Creating an image table?
Of course we need to create a table to store our images in. Here is a simple table
CREATE TABLE testblob (
image_id tinyint(3) NOT NULL AUTO_INCREMENT,
image_type varchar(25) NOT NULL,
image longblob NOT NULL,
image_size varchar(25) NOT NULL,
image_ctgy varchar(25) NOT NULL,
image_name varchar(50) NOT NULL,
KEY image_id (image_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Ok, nothing dramatic here, but lets break it down. First we have an id field, this should be an auto incremented field so we avoid confusion when selecting. Next we have the image type. This is handy as we can use PHP to determine this and use it for sending the correct headers when we need to display the image. Next we have the image_blob field. This is the field that will contain the actual image data. Then we have the image size as detirmined by getimagesize(). Then we have a category for our images, you may wish to have have a second table of categories to populate your form dropdown for this. But for our purposes we will not be dealing with the categories field and it is included here only to show possibilities. Finally, we have the image name. You can add more items to this table such as exif info and descriptions, but for our purposes we will keep it simple.
The upload form.
The upload for is basically the same as for any file upload
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head><title>File Upload To Database</title></head>
<body>
<h2>Please Choose a File and click Submit</h2>
<form enctype="multipart/form-data" action="<?php echo htmlentities($_SERVER['PHP_SELF']);?>" method="post">
<input type="hidden" name="MAX_FILE_SIZE" value="99999999" />
<div><input name="userfile" type="file" /></div>
<div><input type="submit" value="Submit" /></div>
</form>
</body></html>
No surprises here, although we do stress you have the hidden field for MAX_FILE_SIZE. This can be handy for not allowing images that are too large to be uploaded. Also it is important to use the enctype="multipart/form-data" so that the server knows what is coming at it.
Uploading the image.
Here we get funky and can stick the image into the database. As with all user input it is vital that it is checked with some sort of sanity checking to be sure we are getting what we asked for. In this case, we are asking for an image files of a type that is supported by getimagesize. First of all, we need to check that when somebody accesses our page the they have submitted a file. If they have not, we can throw an exception with a message and if they have, we can call the upload() function that we will create below:
<?php
/*** check if a file was submitted ***/
if(!isset($_FILES['userfile']))
{
echo '<p>Please select a file</p>';
}
else
{
try {
upload();
/*** give praise and thanks to the php gods ***/
echo '<p>Thank you for submitting</p>';
}
catch(Exception $e)
{
echo '<h4>'.$e->getMessage().'</h4>';
}
}
?>
The upload function?
Here we need to create a function that does several things. An outline might look like this.
/*
* Check the file is of an allowed type
* Check if the uploaded file is no bigger thant the maximum allowed size
* connect to the database
* Insert the data
*/
We have already checked that an image has been uploaded with the first if statement. We should also check that it is an uploaded file So now we need to check if the file is of an allowed type with getimagesize(). We will begin our function with that and build on it from there.
<?php
/**
*
* the upload function
*
* @access public
*
* @return void
*
*/
function upload(){
/*** check if a file was uploaded ***/
if(is_uploaded_file($_FILES['userfile']['tmp_name']) && getimagesize($_FILES['userfile']['tmp_name']) != false)
{
/*** get the image info. ***/
$size = getimagesize($_FILES['userfile']['tmp_name']);
/*** assign our variables ***/
$type = $size['mime'];
$imgfp = fopen($_FILES['userfile']['tmp_name'], 'rb');
$size = $size[3];
$name = $_FILES['userfile']['name'];
$maxsize = 99999999;
/*** check the file is less than the maximum file size ***/
if($_FILES['userfile']['size'] < $maxsize )
{
/*** connect to db ***/
$dbh = new PDO("mysql:host=localhost;dbname=testblob", 'username', 'password');
/*** set the error mode ***/
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
/*** our sql query ***/
$stmt = $dbh->prepare("INSERT INTO testblob (image_type ,image, image_size, image_name) VALUES (? ,?, ?, ?)");
/*** bind the params ***/
$stmt->bindParam(1, $type);
$stmt->bindParam(2, $imgfp, PDO::PARAM_LOB);
$stmt->bindParam(3, $size);
$stmt->bindParam(4, $name);
/*** execute the query ***/
$stmt->execute();
}
else
{
/*** throw an exception is image is not of type ***/
throw new Exception("File Size Error");
}
}
else
{
// if the file is not less than the maximum allowed, print an error
throw new Exception("Unsupported Image Format!");
}
}
?>
Displaying the image
Now we have the ability to get the images into the database, we need to be able to get it out. Here we see a simple script to display an image based on its image_id in the database. Of course, you should do more checking on the value of $_GET['image_id'] here as we have no checks for out of bounds if and image_id of 99999 was submitted. We will call this file showfile.php.
<?php
/*** some basic sanity checks ***/
if(filter_has_var(INPUT_GET, "image_id") !== false && filter_input(INPUT_GET, 'image_id', FILTER_VALIDATE_INT) !== false)
{
/*** assign the image id ***/
$image_id = filter_input(INPUT_GET, "image_id", FILTER_SANITIZE_NUMBER_INT);
try {
/*** connect to the database ***/
$dbh = new PDO("mysql:host=localhost;dbname=testblob", 'username', 'password');
/*** set the PDO error mode to exception ***/
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
/*** The sql statement ***/
$sql = "SELECT image, image_type FROM testblob WHERE image_id=$image_id";
/*** prepare the sql ***/
$stmt = $dbh->prepare($sql);
/*** exceute the query ***/
$stmt->execute();
/*** set the fetch mode to associative array ***/
$stmt->setFetchMode(PDO::FETCH_ASSOC);
/*** set the header for the image ***/
$array = $stmt->fetch();
/*** check we have a single image and type ***/
if(sizeof($array) == 2)
{
/*** set the headers and display the image ***/
header("Content-type: ".$array['image_type']);
/*** output the image ***/
echo $array['image'];
}
else
{
throw new Exception("Out of bounds Error");
}
}
catch(PDOException $e)
{
echo $e->getMessage();
}
catch(Exception $e)
{
echo $e->getMessage();
}
}
else
{
echo 'Please use a real id number';
}
?>
Displaying all the information
Now we have the basic building blocks, we could create a script to see all the information about the image that we have stored in our table. The trick here is to avoid sending headers twice. To do this we use two files. the first we will call file.php and we use the above showfile.php in our img tag. Of particular importance here is not to make the mistake mentioned at the beginning of this tutorial and do something silly like
SELECT * FROM testblob
This is not required and would slow us down in our query because we would need to store the image itself in memory when we are not going to use it yet. So we simply SELECT the other information from the fields that we do want to show. So our view.php file will look like this below.
<?php
/*** Check the $_GET variable ***/
if(filter_has_var(INPUT_GET, "image_id") !== false && filter_input(INPUT_GET, 'image_id', FILTER_VALIDATE_INT) !== false)
{
/*** set the image_id variable ***/
$image_id = filter_input(INPUT_GET, "image_id", FILTER_SANITIZE_NUMBER_INT);
try {
/*** connect to the database ***/
$dbh = new PDO("mysql:host=localhost;dbname=testblob", 'username', 'password');
/*** set the PDO error mode to exception ***/
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
/*** The sql statement ***/
$sql = "SELECT image_type, image_size, image_name FROM testblob WHERE image_id=".$image_id;
/*** prepare the sql ***/
$stmt = $dbh->prepare($sql);
/*** exceute the query ***/
$stmt->execute();
/*** set the fetch mode to associative array ***/
$stmt->setFetchMode(PDO::FETCH_ASSOC);
/*** set the header for the image ***/
$array = $stmt->fetch();
/*** the size of the array should be 3 (1 for each field) ***/
if(sizeof($array) === 3)
{
echo '<p>This is '.$array['image_name'].' from the database</p>';
echo '<img '.$array['image_size'].' src="showfile.php?image_id='.$image_id.'">';
}
else
{
throw new Exception("Out of bounds error");
}
}
catch(PDOException $e)
{
echo $e->getMessage();
}
catch(Exception $e)
{
echo $e->getMessage();
}
}
else
{
echo 'Please use a valid image id number';
}
?>
From the above script we can see the first image from the database. At the top of the page it has echoed a little message and the image name from the database. You could, as mentioned earlier store other information about the image in the same way and display it. In this script we also use the image_size field from that we had gained from getimagesize(). This stores the image width and height in a string that we can use in img tags in the for of:
<width="270" height="228>"
So that our HTML to include the looks like this:
<img width="270" height="228" src="showfile.php?image_id=11" alt="fresh.gif">
Note that we have only called on the information we wanted from the database. If we had tried to SELECT * FROM testblob we would have been getting the image twice, once in the view.php and again in showfile.php. This has a great deal to do with horror stories about binary database storage being slow. Yes there is a performance penalty for this type of storage, but this is minimal and when compared to the ease of storage and retrieval it is insignificant.
Storing and creating Thumbnails
Traditional wisdom tells that we cannot do this at the same time as we create the images string. Once again, these come with the horror stories of Binary data storage in databases. Many will tell you that you need to make several calls to the database to upload the image and then save the images to disk, then create a new thumbnail image and upload once again. If this were the case, they would be correct in using other methods, however, it is simply poor coding that gets these people into trouble. Two PHP features are your friend here, GD and output buffering. We can use the same file in tmp_name to create two similar input streams, the first containing the image to put in the database, the second to resize to a thumbnail and INSERT. With a small change to our database schema we could
do something like this..
First the database tables (feel free to submit optimizations)
CREATE TABLE testblob (
image_id tinyint(3) NOT NULL AUTO_INCREMENT,
image_type varchar(25) NOT NULL,
image longblob NOT NULL,
image_height INT(5),
image_width INT(5),
image_thumb mediumblob NOT NULL,
thumb_height INT(5),
thumb_width INT(5),
image_ctgy varchar(25) NOT NULL,
image_name varchar(50) NOT NULL,
KEY image_id (image_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
The above dump was from a MySQL 5.* database. With the database in place, we can now add some images to it. We will make a small addition to our form from above by adding some categories for our images. You may choose to have a seperate table to store your categories in and populate the categories dropdown from there. For our purposes today, we will hard code them in.
The upload function itself is not greatly different from the upload function above. All we will be adding is some output buffering and a few functions from the GD image library to create the thumbnail. The database query will also change to facilitate a new category value along with the newly formed image_thumb field. The image_thumb field has been created as a mediumblob for smaller binary storage.
Note that the image_size field has been removed in favour of image_width and image_height fields. As we saw above, the size returned by getimagesize is of the type
<width="270" height="228>"
The problem here is that it is not valid for XHTML documents, so we need to be a little more creative in producing our image width and height. The first two members of the array returned by getimagesize contain this information and it is a simplel task to get the width and height of the original image and to store it. Using some grade 6 math we can also calculate the width and height of the thumbnail that will be created on the fly.
A little math is also required to get the correct aspect ratio of the image, but if you acturally passed grade 6 you should be able to follow. In the following example the form and upload script are combined. By following the comments in the script below, you should be able to get a grasp of the flow. A category field has been added to the form and this will INSERT the category into the database, however, we will not be making use of it and it is shown here only for the purpose demonstration.
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">
<head>
<title>Storing Images in DB</title>
</head>
<body>
<h2>Basic upload of image to a database</h2>
<form method="post" action="<?php echo htmlentities($_SERVER['PHP_SELF']); ?>" enctype="multipart/form-data">
Select Image File:
<input type="file" name="userfile" size="40">
<input type="hidden" name="MAX_FILE_SIZE" value="10000000">
<select name="image_ctgy">
<option value="animals">Animals</option>
<option value="vegetables">Vegetables</option>
<option value="minerals">Minerals</option>
</select>
<br />
<input type="submit" value="submit">
</form>
<?php
/*** check if a file was submitted ***/
if(!isset($_FILES['userfile'], $_POST['image_ctgy']))
{
echo '<p>Please select a file</p>';
}
else
{
try {
upload();
/*** give praise and thanks to the php gods ***/
echo '<p>Thank you for submitting</p>';
}
catch(PDOException $e)
{
echo '<h4>'.$e->getMessage().'</h4>';
}
catch(Exception $e)
{
echo '<h4>'.$e->getMessage().'</h4>';
}
}
/**
*
* the upload function
*
* @access public
*
* @return void
*
*/
function upload(){
/*** check if a file was uploaded ***/
if(is_uploaded_file($_FILES['userfile']['tmp_name']) && getimagesize($_FILES['userfile']['tmp_name']) != false)
{
/*** an array of allowed categories ***/
$cat_array = array("animals", "vegetables", "minerals");
if(filter_has_var(INPUT_POST, "notset") !== false || in_array($_POST['image_ctgy'], $cat_array) !== false)
{
$image_ctgy = filter_input(INPUT_POST, "image_ctgy", FILTER_SANITIZE_STRING);
}
else
{
throw new Exception("Invalid Category");
}
/*** get the image info. ***/
$size = getimagesize($_FILES['userfile']['tmp_name']);
/*** assign our variables ***/
$image_type = $size['mime'];
$imgfp = fopen($_FILES['userfile']['tmp_name'], 'rb');
$image_width = $size[0];
$image_height = $size[1];
$image_size = $size[3];
$image_name = $_FILES['userfile']['name'];
$maxsize = 99999999;
/*** check the file is less than the maximum file size ***/
if($_FILES['userfile']['size'] < $maxsize )
{
/*** create a second variable for the thumbnail ***/
$thumb_data = $_FILES['userfile']['tmp_name'];
/*** get the aspect ratio (height / width) ***/
$aspectRatio=(float)($size[0] / $size[1]);
/*** the height of the thumbnail ***/
$thumb_height = 100;
/*** the thumb width is the thumb height/aspectratio ***/
$thumb_width = $thumb_height * $aspectRatio;
/*** get the image source ***/
$src = ImageCreateFromjpeg($thumb_data);
/*** create the destination image ***/
$destImage = ImageCreateTrueColor($thumb_width, $thumb_height);
/*** copy and resize the src image to the dest image ***/
ImageCopyResampled($destImage, $src, 0,0,0,0, $thumb_width, $thumb_height, $size[0], $size[1]);
/*** start output buffering ***/
ob_start();
/*** export the image ***/
imageJPEG($destImage);
/*** stick the image content in a variable ***/
$image_thumb = ob_get_contents();
/*** clean up a little ***/
ob_end_clean();
/*** connect to db ***/
$dbh = new PDO("mysql:host=localhost;dbname=testblob", 'username', 'password');
/*** set the error mode ***/
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
/*** prepare the sql ***/
$stmt = $dbh->prepare("INSERT INTO testblob (image_type ,image, image_height, image_width, image_thumb, thumb_height, thumb_width, image_ctgy, image_name)
VALUES (? ,?, ?, ?, ?, ?, ?, ?, ?)");
$stmt->bindParam(1, $image_type);
$stmt->bindParam(2, $imgfp, PDO::PARAM_LOB);
$stmt->bindParam(3, $image_height, PDO::PARAM_INT);
$stmt->bindParam(4, $image_width, PDO::PARAM_INT);
$stmt->bindParam(5, $image_thumb, PDO::PARAM_LOB);
$stmt->bindParam(6, $thumb_height, PDO::PARAM_INT);
$stmt->bindParam(7, $thumb_width, PDO::PARAM_INT);
$stmt->bindParam(8, $image_ctgy);
$stmt->bindParam(9, $image_name);
/*** execute the query ***/
$stmt->execute();
}
else
{
/*** throw an exception is image is not of type ***/
throw new Exception("File Size Error");
}
}
else
{
// if the file is not less than the maximum allowed, print an error
throw new Exception("Unsupported Image Format!");
}
}
?>
</body>
</html>
By following the comments in the above script you can get a feel for what is happening with the output buffering. Before the image is exported, we begin buffering and then assign the thumbnail contents. When the buffer is cleaned, all memory of the buffer is erased. To retrieve the thumbnails we simply create a variation of the first script we used to fetch a single image from the database. This time however when we view the images, we will loop through and array of image ID's and have the showfiles.php file show each thumbnail. A little CSS to make it tidy will complete the task. Here we show the view.php file that is now prepared for thumbnails.
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">
<head>
<title>Introduction to SPL</title>
<link rel="stylesheet" type="text/css" href="./tutorial.css" />
<style type="text/css">
div.thumb {
float: left;
width: 25%;
border: thin silver solid;
margin: 0.5em;
padding: 0.5em;
}
div.thumb p {
text-align: center;
font-style: italic;
font-size: smaller;
text-indent: 0;
}
</style>
</head>
<body>
<h2>Thumbnails</h2>
<p>By Kevin Waterson</p>
<?php
/*** Check the $_GET variable ***/
try {
/*** connect to the database ***/
$dbh = new PDO("mysql:host=localhost;dbname=testblob", 'username', 'password');
/*** set the PDO error mode to exception ***/
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
/*** The sql statement ***/
$sql = "SELECT image_id, thumb_height, thumb_width, image_type, image_name FROM testblob";
/*** prepare the sql ***/
$stmt = $dbh->prepare($sql);
/*** exceute the query ***/
$stmt->execute();
/*** set the fetch mode to associative array ***/
$stmt->setFetchMode(PDO::FETCH_ASSOC);
/*** set the header for the image ***/
foreach($stmt->fetchAll() as $array)
{
echo '<div class="thumb" style="width: '.$array['thumb_width'].'px; height: '.$array['thumb_height'].'px;">
<p><a href="showfile.php?image_id='.$array['image_id'].'">
<img src="showthumbs.php?image_id='.$array['image_id'].'" alt="'.$array['image_name'].' /">
</a></p>
<p>'.$array['image_name'].'</p></div>';
}
}
catch(PDOException $e)
{
echo $e->getMessage();
}
catch(Exception $e)
{
echo $e->getMessage();
}
?>
</body>
</html>
You can see in the script above where we have looped over the image_id's from the database and called the file showthumbs.php and have linked this to the original showfile.php file so when a thumbnail is clicked, it will load the fullsized image. The showthumbs.php script is very similar to the showfile.php script with only a change to the database SELECT to fetch the image_thumb field rather than the image field. The showthumbs.php script is shown below.
<?php
/*** some basic sanity checks ***/
if(filter_has_var(INPUT_GET, "image_id") !== false && filter_input(INPUT_GET, 'image_id', FILTER_VALIDATE_INT) !== false)
{
/*** assign the image id ***/
$image_id = filter_input(INPUT_GET, "image_id", FILTER_SANITIZE_NUMBER_INT);
try {
/*** connect to the database ***/
$dbh = new PDO("mysql:host=localhost;dbname=testblob", 'username', 'password');
/*** set the PDO error mode to exception ***/
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
/*** The sql statement ***/
$sql = "SELECT image_thumb, image_type FROM testblob WHERE image_id=$image_id";
/*** prepare the sql ***/
$stmt = $dbh->prepare($sql);
/*** exceute the query ***/
$stmt->execute();
/*** set the fetch mode to associative array ***/
$stmt->setFetchMode(PDO::FETCH_ASSOC);
/*** set the header for the image ***/
$array = $stmt->fetch();
/*** check we have a single image and type ***/
if(sizeof($array) == 2)
{
/*** set the headers and display the image ***/
header("Content-type: ".$array['image_type']);
/*** output the image ***/
echo $array['image_thumb'];
}
else
{
throw new Exception("Out of bounds Error");
}
}
catch(PDOException $e)
{
echo $e->getMessage();
}
catch(Exception $e)
{
echo $e->getMessage();
}
}
else
{
echo 'Please use a real id number';
}
?>
Another Appoach
Till now, the above methods have not included any database normalization. Here we will re-design the database so that the images are stored in a seperate table with a foreign key index. Our database structure will now look like this:
With the above scripts you will be able to store and retrieve you images, in MySQL database. If you have any further questions about what you see here, please contact us via the contact page.
Enjoy!