Save image in database(MySQL & Blob datatype)

Hi,

This is rather interesting topic, but is among the simpler one's. In this I will show how to save an image in its actual format in the database as data and fetching it as an image to the web page. First of all create a database table and assign "blob" data type to the fields that will hold the image. Here is the schema for the database table that I am using:

CREATE TABLE `employees` (
`id` INT(10) NOT NULL AUTO_INCREMENT,
`empname` VARCHAR(50) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
`profile_pic` LONGBLOB NULL,
`ext` VARCHAR(5) NULL DEFAULT NULL COLLATE 'latin1_general_ci',
PRIMARY KEY (`id`)
)

[You can find the entire source code here]

Now that my table is ready lets move on to the HTML. It is a pretty simple one just a form with couple of fields as follows:

<form method="post" action="" enctype="multipart/form-data">
Enter Name:<br/><input type="text" name="emp_name" /><br/><br/>
Profile Pic:
<input type="file" name="pic" /><br/><br/>
<input type="submit" value="Save Image" />
</form>

The page looks something like:

After saving the data, this is what I get:

Code and Explanation:

When the form is submitted here is what I have done:


$content=file_get_contents($_FILES['pic']['tmp_name']);
$content=mysql_escape_string($content);

@list(, , $imtype, ) = getimagesize($_FILES['pic']['tmp_name']);

if ($imtype == 3){
$ext="png";
}elseif ($imtype == 2){
$ext="jpeg";
}elseif ($imtype == 1){
$ext="gif";
}


$q="insert into employees set empname='".$_POST['emp_name']."',profile_pic='".$content."',ext='".$ext."'";
mysql_query($q);

This is pretty straight forward code. The main part of this code is:

$content=file_get_contents($_FILES['pic']['tmp_name']);

$content=mysql_escape_string($content);

In this unlike our traditional approach I am not using the copy or move_uploaded_file function to save the image in a files. Rather I am reading the content of the file using file_get_contents(), then I am encapsulating the special characters in that content with mysql_escape_string(), so that it can be inserted in the database table. And last but not the least saving the content in the table.

In the following lines:

@list(, , $imtype, ) = getimagesize($_FILES['pic']['tmp_name']);

if ($imtype == 3){

$ext="png"; 

}elseif ($imtype == 2){

$ext="jpeg";

}elseif ($imtype == 1){

$ext="gif";

}

I am just retrieving the extension of the image and finally store the above content and the extension in the database by:


$q="insert into employees set empname='".$_POST['emp_name']."',profile_pic='".$content."',ext='".$ext."'";
mysql_query($q);

So one part of my job is done. My image is now saved in the database in blob format.

Now I have to fetch the data that is stored and display it in image format. For doing that is the following code:

<table>
<?php
$q="select * from employees";
$resultset=mysql_query($q);
if(mysql_num_rows($resultset)==0){
?>
<tr>
<td width="400">No Images Saved</td>
</tr>
<?php
}
while($rec=mysql_fetch_array($resultset)){
?>
<tr>
<td width="200"><img src="load_image.php?pic_id=<?php echo $rec['id']; ?>"></td>
<td><?php echo $rec['empname']; ?></td>
</tr>
<?php
}

?>
</table>

This again is pretty simple to understand. I am fetching all the records and displaying them in the grid format. The most important part here is the line:

<img src="load_image.php?pic_id=<?php echo $rec['id']; ?>">

here to load the image I am passing the record id to another page, load_image.php. This page will convert the data into image. In load_image.php I have:


$q="select * from employees where id=".$_GET['pic_id'];
$rec=mysql_fetch_array(mysql_query($q));
$data=$rec['profile_pic'];
header('Content-Length: '.strlen($data));
header("Content-type: image/".$rec['ext']);
echo $data;

First three lines are pretty straight forward. I am fetching one particular record from the table based on the ID supplied in the get parameter as was earlier given in <img src="load_image.php?pic_id=<?php echo $rec['id']; ?>">.

Next I am setting the content type and content length that is to be returned. The content type is the extension or rather file type that I was storing earlier, and content length is simply the length of the content in bytes. And finally to return the data in image format I give "echo $data;" to wrap things up.

Hope this helped. Download: blob.zip