Just Try This: storing image into database

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 - 2⁸ bytes
BLOB - 2¹⁶ bytes
MEDIUMBLOB - 2²⁴ bytes
LONGBLOB - 2³² 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($handle, filesize("{$_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.

Just Try This

Saturday, June 9, 2012

How to store image in MySql

Four types of BLOB supported by MySql: