Insert and Retrieve file with MySQL

Some of you might be knowing that there is a functionality in MySQL database for storing files and retrieving the same.

This is mainly used when we need to reduce response time for retrieving and using files in our application.

I am listing here the method for inserting image file in and retrieving image file from MySQL database using PHP.

1) For inserting data into MySQL you need to convert the image file into binary format, this is accomplished by using ‘BLOB’ datatype in MySQL. Basically there are four datatypes TINYBLOB,MEDIUMBLOB,BLOB and LONGBLOB which differs by their storing capacity for characters. For further reference please visit this site MySQL.

2) Create a column having datatype from any of the above described datatypes.

3) Suppose name of the column is ‘image’ and table name is ‘uploadtest’.

<?php

// make connection with database
$connection = mysql_connect("hostname", "dbusername", "dbpassword") or die ("Unable to connect!");
mysql_select_db("dbname") or die ("Unable to select database!");

$qry = "INSERT INTO `uploadtest`(`image`) VALUES(LOAD_FILE('/var/www/html/urfolder/abc.jpg'))"; // where "/var/www/html/urfolder/abc.jpg" is the full physical path of the image.

mysql_query($qry) or die (mysql_error());

?>

4) Now for displaying image in the browser you need to execute following code

<?php

$selqry = "SELECT `image` FROM `uploadtest`";
$selrs = mysql_query($selqry);
header('Content-type: image/jpeg '); //set header with content type 'jpeg' for displaying image
while ($row = mysql_fetch_assoc($selrs))
{       
    echo $row['image'];    
}
?>

5) Note you can upload and retrieve any type of file.