Store And Retrive Images Into MYSQL Using PDO

In this tutorial I’m going to show you how you can store and retrieve images from MySQL database. We use to store images into DB , so as to avoid all those complexity of storing in file system. But storing in database will consume more memory and retrieval is slow for bigger file.
I am using PHP PDO library to DB handle, so that large binary objects such as images can be properly handled.
Step -1  Connect To DB
$dsn = 'mysql:dbname=testbd;host=192.168.1.11';
$user = 'myuser';
$password = 'mypassword';
try {
    $dbh = new PDO($dsn, $user, $password);
    echo 'DB connected<br>';
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}
Step -2 Upload file
Upload an image file using HTML form upload and assign $_FILES into respective variables
$id=1;
$filetype = $_FILES['file']['type'];
$filePointer = fopen($_FILES['file']['tmp_name'], 'rb'); //rb - read , binary
Step-3  Database insertion
I am using late binding method of PDO to store data into imagefiles table where field file is of large BLOB type.
$dbh->beginTransaction(); //** PDO transcation starts
$stmt = $dbh->prepare("insert into imagefiles (id, filetype, file) values (?, ?, ?)");
$stmt->bindParam(1, $id);
$stmt->bindParam(2, $filetype);
$stmt->bindParam(3, $filePointer, PDO::PARAM_LOB);
$stmt->execute(); //** query gets executed
$dbh->commit(); //** commit the whole transcation
//PDO::PARAM_LOB is used to stream large binary objects
so at step 3 we are storing whole image file as binary data into file field of that table. As we bind variable $filePointer which contains file pointer to tmp location of the uploaded image, PDO will read that file using file pointer and store that into DB.

we write below code in php file getimage.php
Step -4 Retrieve Image
To retrieve that image i write a select query to fetch file from table imagesfiles.
$stmt = $dbh->prepare("select filetype,file FROM imagefiles where id='1' LIMIT 0,1");
$stmt->execute();
$stmt->bindColumn(1, $type, PDO::PARAM_STR, 256);
$stmt->bindColumn(2, $file, PDO::PARAM_LOB);
$stmt->fetch(PDO::FETCH_BOUND);
//print_r($file); 
//** will print full special characters on screen if data is image ie Binary Data, else if data is
//** Text it will display as it is
Binding a column from a result set to a variable using PDO::PARAM_LOB is supposed to return a stream resource into the variable when PDO::fetch() is called. This stream can then be operated on using any PHP function that handles files.Unfortunately there’s a bug which means that instead of returning a stream into $file PDO returns a string containing the binary data. When this is then passed to fpassthru() an error is triggered. Fortunately there’s a simple fix for displaying the image: replace the call to fpassthru() with echo or print. Since the browser is expecting an image after the call to header() writing the binary data. Using echo or print has the same effect as calling fpassthru(). In my code I’ve added the following just in case this bug is fixed in a future release:

Way - 1 To show image in webpage

if(is_string($file))
{
$file = imagecreatefromjpeg("data://image/jpeg;base64," . base64_encode($file));
}
//encode string data from DB result into stream data/binary image data using PHP wraper
//With Base64 encoding image data can be send inline ie in example 1 whole image data is 
// embeded in <img> tag ( normally src would do another HTTP request for image ) and 
//it wouldn’t require additional HTTP requests, just some extra millise-conds for sending 
//this data.
/*  Example -1
$img_src = "img/sample.jpg"; $imgbinary = fread(fopen($img_src, "r"), filesize($img_src)); $img_str = base64_encode($imgbinary); echo '<img src="data:image/jpeg;base64,'.$img_str.'" alt="Alt text" />';
*/
/* gif - data:image/gif;base64, ....

jpg - data:image/jpeg;base64, ....
png - data:image/png;base64, ...
*/

//** Method - (1) - To in image tag
ob_clean();   //Clean (erase) the output buffer
flush();   //Flush (send) the output buffer

header('Content-Type: image/jpeg');

imagejpeg($jpegimage);

exit();

Way -2 To show image in webpage
if(is_string($file))
{
//** Every Time control comes inside this if condition ****
//** our file is a string
$file = fopen('data://text/plain;base64,' . base64_encode($file), 'r');  //* Line A
//print_r($file); 
     //**  would print Resource id #3 ie a filepointer
    //**Line A - data: stream wrapper is used to encode the actual string in Base64
   //Base64 is an encoding format that represent binary data.
}
//we can use above code to convert string data into a stream first then continue accessing it as if it were a string.

The following the methods to show files in browser - image file is jpeg
//** Method - (2) - To in image tag
file_put_contents("/var/www/video/aa.jpeg",$file);
echo "<img src='http://localhost/video/aa.jpeg'>";

//**Method  - (3) 
//$file contain file pointer
 file_put_contents("/tmp/aa.jpeg",$file); //create a file at temporal location
 ob_clean(); 
 flush(); 
 // header('Content-Type: image/jpeg');
 readfile('/tmp/aa.jpeg'); //Reads a file and writes it to the output buffer.

//**Method  - (4) 
// for this, Line A should be first made commented, so as to have $file consist of binary image data as string, with PHP wraper
ob_clean(); 
 flush(); 
 header('Content-Type: image/jpeg');
 echo $file;
Both method 1,3 and 4 , we can view image using image Tag as follows.
<img src="localhost/myproject/getimage.php"  id="img1" alt="myimage" >

following is the table structure

CREATE TABLE IF NOT EXISTS `myimage` (
  `id` int(11) NOT NULL,
  `filetype` varchar(200) NOT NULL,
  `file` blob NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


File field is Blob, used to store binary data

Handling Binary Data with PDO in PHP

We can handel binary data using PDO database access library in PHP 5 . In one project i am storing images in database as binary Data. PDO allows you to bind a file handle to a parameter in a prepared statement and when the statement is executed the contents of the file are slurped into the database. 
This works perfectly but the problem comes when getting the image out of the database again to display it. 
According to the PHP manual the following code should work:

$db = new PDO('odbc:SAMPLE', 'db2inst1', 'ibmdb2');
$stmt = $db->prepare("select contenttype, imagedata from images where id=?");
$stmt->execute(array($_GET['id']));
$stmt->bindColumn(1, $type, PDO::PARAM_STR, 256);
$stmt->bindColumn(2, $lob, PDO::PARAM_LOB);
$stmt->fetch(PDO::FETCH_BOUND);
header("Content-Type: $type");
fpassthru($lob);

Binding a column from a result set to a variable using PDO::PARAM_LOB is supposed to return a stream resource into the variable when PDO::fetch() is called. This stream can then be operated on using any PHP function that handles files. Unfortunately there’s a bug which means that instead of returning a stream into $lob PDO returns a string containing the binary data. When this is then passed to fpassthru() an error is triggered. Fortunately there’s a simple fix for displaying the image: replace the call to fpassthru() with echo or print. Since the browser is expecting an image after the call to header() writing the binary data. Using echo or print has the same effect as calling fpassthru(). In my code I’ve added the following just in case this bug is fixed in a future release:

if (is_string($lob))
      {
   echo $lob;
} else {
   fpassthru($lob);
}

//or we can use following code to convert string data into a stream first then continue accessing it as if it were a string.
if (is_string($lob)) {
// $lob is now a resource pointing to a stream composed of the data at hand
$lob = fopen('data://text/plain;base64,' . base64_encode($lob), 'r');
} 
fpassthru($lob);

This neatly gets around the problem if you just want to send the binary data back to the browser to be displayed. Anything more requiring the use of any file functions or image editing functions would need quite a few contortions in the code. The information from the database would probably need to be written to a temporary file to allow it to be operated on.
This bug was first reported almost three years ago in PHP 5.2.6 and it’s still not fixed today in the most recent version, 5.3.1.

To encrypt data using mycrypt function


$key = 'mysalt password';

$string = 'string to be encrypted s%$%&*^$#@! <img src="hjdsfshjf/djfhskdhjf"> <a href="#">my link</a> my texy sample text sample text';

$encrypted = base64_encode(mcrypt_encrypt(MCRYPT_RIJNDAEL_256, md5($key), $string, MCRYPT_MODE_CBC, md5(md5($key))));

$decrypted = rtrim(mcrypt_decrypt(MCRYPT_RIJNDAEL_256, md5($key), base64_decode($encrypted), MCRYPT_MODE_CBC, md5(md5($key))), "\0");


print_r($encrypted); //32
echo "<br>";
print_r($decrypted); //22