Saturday, June 9, 2012

How to store image in MySql

There is two ways to achieve this  :
  • Storing the image as binary object
  • Storing the image as string
Screenshot


Method-1 : Storing image as binary object

       MySql supports BLOB data type.
       A blob (alternately known as a binary large object, basic large object, BLOB, or BLOb) is a collection of binary data stored as a single entity in a database management system. Blobs are typically images, audio or other multimedia objects, though sometimes binary executable code is stored as a blob. Database support for blobs is not universal. 

    Four types of BLOB supported by MySql:

  • TINYBLOB - 28 bytes
  • BLOB - 216 bytes
  • MEDIUMBLOB - 224 bytes
  • LONGBLOB - 232 bytes 
Create Table
CREATE TABLE `image` ( 
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(64) character SET utf8 NOT NULL,
  `type` varchar(20) NOT NULL,
  `data` mediumblob NOT NULL,
  PRIMARY KEY  (`id`) 
);
SET utf8 - allows you to store other than english
MEDIUMBLOB -  allows up to 16MB 
Storing image into database
<?php
//configuration details first
$db_host 'localhost';
$db_user 'root'$db_pwd 'root';
$database 'image';
$table 'image';
//connect to specific mysql
mysql_connect($db_host$db_user$db_pwd) or die(mysql_error());
//select a specific database
mysql_select_db($database) or die(mysql_error());
//insert into database
if(isset($_FILES['image']) && isset($_POST['imageTitle']))
{
    $type=mysql_real_escape_string($_FILES['image']['type']);
    $title=mysql_real_escape_string($_POST['imageTitle']);
    $data=mysql_real_escape_string(file_get_contents($_FILES['image']['tmp_name']));

    mysql_query("INSERT INTO {$table}
                    SET type='$type', title='$title',
                        data='$data'") or die(mysql_error());
}

//retrive from database
if(isset($_GET['id']))
{
    $results=mysql_query("SELECT type,data FROM {$table} WHERE id={$_GET['id']}") or die(mysql_error());
    if(@mysql_num_rows($results))
    {
        @list($type$data) = mysql_fetch_row($results);

        header("Content-Length: ".strlen($data));
        header("Content-type: {$type}");
        echo $data;
        mysql_close();
        exit;
    }
}

//display all the images
$results=mysql_query("SELECT * FROM {$table}") or die(mysql_error());

if(@mysql_num_rows($results))
{
    while(@list($id,$title,$type$data) = mysql_fetch_row($results))
    {
        echo "<a href=\"{$_SERVER['PHP_SELF']}?id=$id\"> <img src=\"http://{$_SERVER['HTTP_HOST']}{$_SERVER['PHP_SELF']}?id=$id\" width=\"150\" height=\"150\" title=\"{$title}\"/></a> &nbsp;";
    }
}
?>
 
<!--image uploader form-->
<div style="background: none repeat scroll 0pt 0pt rgb(245, 245, 245); border: 1px solid rgb(217, 217, 217); padding: 30px; font-size: 17px; width: 511px;">
<h2 style="text-align: center;">Image uploders</h2>
<form id="imageUploader" name="imageUploader" method="POST" action="<?php echo $_SERVER['PHP_SELF']; //refers current file ?>" enctype="multipart/form-data">
<p>ImageTitle: <input type="TEXT" style="font-size: 15px; border: 1px solid rgb(217, 217, 217); margin: 3px; padding: 3px; float: right;width:75%;" name="imageTitle"></p>
<p>Image: <input type="FILE" style="width:75%;font-size: 15px; border: 1px solid rgb(217, 217, 217); margin: 3px; padding: 3px; float: right;" name="image"></p>
<p style="text-align:right;"><input type="SUBMIT" value="SUBMIT" style="background-color: #4D90FE; color:#fff; border:1px solid #3079ED; font-weight:bold; text-align:center;"/></p>
</form>
</div>
Method-2 : Storing image as String

          For storing image as string , we can use our existing table with BLOB or we may create new table with MEDIUMTEXT instead of MEDIUMBLOB.Here i'm going to create a new table with MEDIUMTEXT.
CREATE TABLE `imageText` ( 
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(64) character SET utf8 NOT NULL,
  `type` varchar(20) ,
  `data` mediumtext NOT NULL,
  PRIMARY KEY  (`id`) 
);
Storing image into database
<?php

//configuration details first
$db_host 'localhost';
$db_user 'root'$db_pwd 'root';
$database 'image';
$table 'imagetext';

//connect to specific mysql
mysql_connect($db_host$db_user$db_pwd) or die(mysql_error());

//select a specific database
mysql_select_db($database) or die(mysql_error());

if(isset($_FILES['image']) && isset($_POST['imageTitle']))
{
    $type=mysql_real_escape_string($_FILES['image']['type']);
    $title=mysql_real_escape_string($_POST['imageTitle']);
    
    //read image file
    $handle=fopen("{$_FILES['image']['tmp_name']}""r");
    $img=fread($handlefilesize("{$_FILES['image']['tmp_name']}"));
    fclose($handle);

    $data=mysql_real_escape_string(base64_encode($img));

    mysql_query("INSERT INTO {$table}
                    SET type='$type', title='$title',
                        data='$data'") or die("INSERT ERROR : ".mysql_error());
}

//get image for display
if(isset($_GET['id']))
{
    $results=mysql_query("SELECT type,data FROM {$table} WHERE id={$_GET['id']}") or die(mysql_error());

    if(@mysql_num_rows($results))
    {
        @list($type$data) = mysql_fetch_row($results);

        header('Content-Length: '.strlen($data));
        header("Content-type: {$type}");
        echo base64_decode($data);
        mysql_close();
        exit;
    }
}

//display all the images
$results=mysql_query("SELECT * FROM {$table}") or die(mysql_error());
if(@mysql_num_rows($results))
{
    while(@list($id,$title,$type$data) = mysql_fetch_row($results))
    {
        echo "<a href=\"{$_SERVER['PHP_SELF']}?id=$id\"> <img src=\"http://{$_SERVER['HTTP_HOST']}{$_SERVER['PHP_SELF']}?id=$id\" width=\"150\" height=\"150\" alt=\"{$title}\"/></a> &nbsp;";;
    }
}
?>
<!--image uploder form-->
<div style="background: none repeat scroll 0pt 0pt rgb(245, 245, 245); border: 1px solid rgb(217, 217, 217); padding: 30px; font-size: 17px; width: 511px;">
<h2 style="text-align: center;">Image uploders</h2>
<form id="imageUploader" name="imageUploader" method="POST" action="<?php echo $_SERVER['PHP_SELF']; //refers current file ?>" enctype="multipart/form-data">
<p>ImageTitle: <input type="TEXT" style="font-size: 15px; border: 1px solid rgb(217, 217, 217); margin: 3px; padding: 3px; float: right;width:75%;" name="imageTitle"></p>
<p>Image: <input type="FILE" style="width:75%;font-size: 15px; border: 1px solid rgb(217, 217, 217); margin: 3px; padding: 3px; float: right;" name="image"></p>
<p style="text-align:right;"><input type="SUBMIT" value="SUBMIT" style="background-color: #4D90FE; color:#fff; border:1px solid #3079ED; font-weight:bold; text-align:center;"/></p>
</form>
</div>
base64_encode - Encodes data with MIME base64
base64_decode - Decodes data encoded with MIME base64

Disadvantage of BLOB is that it's not available on all database. 
Disadvantage  in storing as string is that it occupy more space.  Since Base64-encoded data takes about 33% more space than the original data.

3 comments: