AmiteshBharti's PHP Learning blog: Tutorial on how to store images in Mysql BLOB field

mysql

Sometimes in our web application we need the image upload feature. User can browse an image from his/her PC and clicks the upload/submit button to upload this image to the server. Basically this happens when we create web applications like photo gallery or user avatar selection etc.
There are two ways uploaded images can be stored in server- In server’s physical directory or in the database. Storing in physical directory is like copying the image from user’s machine to server directory. The advantage is that it only takes spaces from the server’s physical directory. But disadvantage is the extra headache of taking backup for website administrator. In database we can store the images easily and taking backup of database is sufficient. But here the disadvantage is that large volume of data might slow down the database operation.
In this tutorial we’ll discuss about image upload in mysql database and how to display it. In MySQL images or any files can be stored in a specific field called BLOB (Basic Large Object or Binary Large Object). BLOB fields are designed to store any binary files. There are 4 types of BLOB fields are available in MySQL: TINYBLOB, BLOB, MEDIUMBLOB and LONGBLOB. The descriptions are self explanatory.
In our sample PHP-MySQL code we’ll use only BLOB field. So open your phpmyadmin and execute this SQL to create the table.

CREATE TABLE `img_tbl` (
 `id` INT(4) NOT NULL AUTO_INCREMENT,
 `img_name` VARCHAR(255) COLLATE latin1_general_ci NOT NULL,
 `img_type` VARCHAR(4) COLLATE latin1_general_ci NOT NULL,
 `img_size` INT(8) NOT NULL,
 `img_data` BLOB NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

`id` is the unique id for the table row. This is the primary key. `img_name` field stores the image name. `img_type` stores the information about the type of images like JPG or GIF etc. `img_size` holds the size of the image and img_data stores the actual image file.
Now we need a HTML file which will allow the user to browse and select the image to upload. Elow is the code snippet for this:

<!DOCTYPE HTML PUBLIC “-//W3C//DTD HTML 4.0 Transitional//EN”>
<HTML>
<HEAD>
<TITLE> Image Upload to BLOB field </TITLE>
</HEAD>
 
<BODY>
<FORM NAME=”f1″ METHOD=”POST” ACTION=”upload.php” ENCTYPE=”multipart/form-data”>
<table>
<tr><td> Image Upload Page </td></tr>
<tr><td> <input type=”file” name=”imgfile”/></td></tr>
<tr><td> <input type=”submit” name=”submit” value=”Save”/> </td></tr>
</table>
</FORM>
</BODY>
</HTML>

Remember always put the ENCTYPE attribute to be “multipart/form-data” to make it post files to the server. If you forget to add this attribute in the HTML <form /> tag, then no files will be posted to the server. The HTML element that displays the file select dialog box to the user is the tag <input type=”file” name=”imgfile” />. When user submits the selected image to upload to the server the control goes to the PHP script upload.php which handles the upload of the image file to the server. The code snippet of upload.php is given below:
upload.php

<?
include “dbconfig.php”;
 
$dbconn = mysql_connect($dbhost, $dbusr, $dbpass) or die(”Error Occurred-”.mysql_error());
mysql_select_db($dbname, $dbconn) or die(”Unable to select database”);
 
if(isset($_REQUEST[submit]) && $_FILES[imgfile][size] > 0)
{
          $fileName       = $_FILES[imgfile][name]; // image file name
          $tmpName     = $_FILES[imgfile][tmp_name]; // name of the temporary stored file name
          $fileSize           = $_FILES[imgfile][size]; // size of the uploaded file
          $fileType         = $_FILES[imgfile][type]; // file type
 
          $fp                    = fopen($tmpName, ‘r’); // open a file handle of the temporary file
          $imgContent  = fread($fp, filesize($tmpName)); // read the temp file
          fclose($fp); // close the file handle
 
          $query = “INSERT INTO img_tbl (`img_name`, `img_type`, `img_size`, `img_data` )
                        VALUES (’$fileName’, ‘$fileType’, ‘$fileSize’, ‘$imgContent’)”;
 
          mysql_query($query) or die(’Error, query failed’);
          $imgid = mysql_insert_id(); // autoincrement id of the uploaded entry
          mysql_close($dbconn);
 
          echo "<br>Image successfully uploaded to database<br>";
          echo "<a href=\”viewimage.php?id=$imgid\”>View Image</a>";
 
}else die(”You have not selected any image”);
?>

dbconfig.php

<?
  $dbhost           =             “localhost”; // server host name
  $dbusr            =              “root”; // mysql username to connect
  $dbpass           =             “”; //password
  $dbname           =            ”compass”; //database name to select to
?>

Let me explain the script step by step. dbconfig.php script contains the database connection information like host name, userid and password and database name to select. The script upload.php first includes the dbconfig.php in order to include the database information because we’ll be executing the SQL to insert the uploaded image file into the database BLOB field.

$_FILES is the global variable which is created by PHP itself to hold the information about the uploaded file object. First the uploaded image is stored as a temp file in the temp folder of the server. The temp name can be retrieved from $_FILES[imgfile][tmp_name]. Now the temp image is read and stored in the BLOB field.

The next script will show you how you can download the image from the BLOB field of the MySQL database and display to the browser.
showimages.php

<?
    include “dbconfig.php”;
    $dbconn = mysql_connect($dbhost, $dbusr, $dbpass) or die(”Error Occurred-”.mysql_error());
    mysql_select_db($dbname, $dbconn) or die(”Unable to select database”);
 
    $query = “SELECT `id`, `img_name`, `img_type`, `img_size`, `img_data`
                     FROM img_tbl ORDER BY `id`”;
 
    $result = mysql_query($query) or die(’Error, query failed’);
 
    while($row = mysql_fetch_array($result)){
               echo “<img src=\”viewimage.php?id=$row[id]\” width=\”55\” height=\”55\” /> <br/>”;
    }
 
    mysql_close($dbconn);
?>

This script is actually fetching all the data from database and printing the images in the <img src=””/> tag. So the content is written to the browser by the viewimage.php script.

viewimage.php

<?
if(isset($_REQUEST['id']))
{
   // get the file with the id from database
      include “dbconfig.php”;
      $dbconn = mysql_connect($dbhost, $dbusr, $dbpass) or die(”Error Occurred-”.mysql_error());
      mysql_select_db($dbname, $dbconn) or die(”Unable to select database”);
 
      $id    = $_ REQUEST ['id'];
      $query = “SELECT `img_name`, `img_type`, `img_size`, `img_data`
                       FROM img_tbl WHERE id = ‘$id’”;
 
      $result = mysql_query($query) or die(mysql_error());
      list($name, $type, $size, $content) = mysql_fetch_array($result);
 
      header(”Content-length: $size”);
      header(”Content-type: $type”);
      print $content;
 
      mysql_close($dbconn);
}
?>

In this script the header () function is playing an important role. This function actually tells the browser what to do with the BLOB content. It takes several parameters like size, type etc. “Content-length” sets the size of the file, and “Content-type” sets the type of the file (JPG or GIF or PNG etc.). After setting the header, the actual BLOB content is written using print $content. The <img src=”” /> tag then is able to display the images.

AmiteshBharti's PHP Learning blog

Monday, 3 February 2014

Tutorial on how to store images in Mysql BLOB field

No comments:

Post a Comment