Tutorial on how to store images in Mysql BLOB field

Posted on Jan 11, 2009 | 26 Comments

mysql

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.

Suvoraj Biswas

A seasoned Technical Consultant with 9+ years of hands on experiences in Business Analysis, Enterprise Technology Integration and core development. He holds the globally-recognized PMP® credential and is an active member of the Project Management Institute, Inc. Raj has proven consistent track record of success in crucial and technically challenging enterprise projects involving system development, system integration, IT migration and mobile solutions where he maintained a strong balance between client's business objectives and technical development in Offshore-Onshore model framework and contributed as and when needed as Technical Consultant, Lead, Business Analyst and Project Expediter. He holds technical expertise in middle-ware Solution Architecture, Web Service (SOAP & RESTful), Android Mobile development and Enterprise Web development with Spring Core & MVC, Struts2, Hibernate. Besides work, he loves doing sketches and listening to music and contribute articles at www.techcubetalk.com & suvoraj-biswas.hubpages.com.

26 comments

  1. ryan says:

    Thanks for the code.
    i’ll try this.

  2. Chris says:

    I keep getting “Unable to select database” and I’ve checked my database connection (host name, userid and password and database name to select) ..it all seems correct. What else could I be missing???

  3. Chris says:

    I’m confused..is $dbname referring to the database table name?

  4. Chris says:

    Nevermind, on the $dbname, just realized it was the same name as my username. Now I’m getting “Error, query failed” this time and I’m almost sure everything in the “$query” is correct. Anything else I should look out for?

  5. Chris says:

    OK…traced the problem..but not sure how to solve it as I get this answer once I upload the image…

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ”img_name’,'img_type’,'img_size’,'img_data’) VALUES (‘DSCN0196.JPG’,'ima’ at line 1

  6. Chris says:

    Hmmm…can I get any help please?? ANyone out there?

  7. francis says:

    I have tried to use above method but the contents was not displayed in the web page.It asks to open or save the file into system. how can i show the contents of word file in mypage stored from mysql .

  8. ammar says:

    i copied and pasted as you mention. but it shows the following error:
    Warning: fopen(C:\DOCUME~1\polo\LOCALS~1\Temp\php11E.tmp) [function.fopen]: failed to open stream: No error in C:\xampp\htdocs\mybiz\tutorial-5\upload.php on line 14

    Warning: fread(): supplied argument is not a valid stream resource in C:\xampp\htdocs\mybiz\tutorial-5\upload.php on line 15

    Warning: fclose(): supplied argument is not a valid stream resource in C:\xampp\htdocs\mybiz\tutorial-5\upload.php on line 16
    ’Error, query failed

  9. scraphero says:

    Error, query failed

    how do i fix this error please helm out

  10. rotan says:

    the quotation marks”"” are not right

  11. uut3 says:

    can u explain what is the blog data type is?

  12. uut3 says:

    im sorry i mean blob data type.
    and what is the main function of blob is?

  13. Blues says:

    i recommend those who have query failed problem to add in this behind the code…
    .mysql_error()
    this will help you to understand the problem better

  14. Mewben says:

    Fixed.

    For Error, query failed…
    Just change the img_type from varchar(4) to varchar(15) or more…

  15. Njagi says:

    this is my error
    Error, query failed
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘()*456789:CDEFGHIJSTUVWXYZcdefghijstuvwxyzƒ„…†‡ˆ‰Š’“”•–—˜™š¢£¤¥¦§¨©ª²³´µ¶·¸¹ºÂÃÄ’ at line 1

    it is not able to place on the img_data for the blob.. what could be the problem

  16. adio says:

    Thank you very much for the code, but there is something wrong with it ! i tried using fopen and fread function but there was something wrong with the whole thing, then i tried file_get_content and it worked

  17. Paid Critique says:

    i have same Problem!

    I always display “Error, query failed”…

    What could be the answer?

    @Newben, I tried your suggestion but its not working.

  18. Paid Critique says:

    I tried that .mysql_error() and I hava this result.

    Error: query failedYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘()*456789:CDEFGHIJSTUVWXYZcdefghijstuvwxyz’ at line 2

    Does that mean that I have problem with my database?

    i put the whole code in “C:\xampp\htdocs\upload photo”

    thanks.

  19. Alex says:

    @Paid Critique – it looks like you have a error in your database. Can you make sure you have the tables created in mysql and the names are matching with the columns names in your database table

  20. kais says:

    i did it all correctly and its working perfectly fine, the only problem is it only display 1 image,

  21. siv says:

    so much errors// please check it before anything upload.. lot of time waste to me

  22. Muneer Alam says:

    Here are the few changes in the code:-
    1:-open upload.php file then insert few line
    $imgContent = fread($fp, filesize($tmpName));
    $imgContent1=base64_encode($imgContent);

    change also in insert query ($imgContent to $imgContent1)

    2:-Also change in viewimage.php
    print $content to print base64_decode($content);

    then you can view your uploaded image…

  23. prohor says:

    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 ”
    Image successfully uploaded to database
    “; echo “View Image”; }else die(“You have not selected any image”); ?>

  24. surajddk says:

    Need to add following line after the ,
    $imgContent = fread($fp, filesize($tmpName)); of upload.php,
    $imgContent=addslashes($imgContent);

  25. probhat says:

    In showimages.php why $query = “SELECT `id`, `img_name`, `img_type`, `img_size`, `img_data`
    FROM img_tbl ORDER BY `id`”;
    query is required???? Where we only using the id????

  26. Raj says:

    @probhat – pls check closely.. we are also using image name, size and type in the header() functions.

Leave a comment


7 × = thirty five

Advertisement

Subscription

You can subscribe by e-mail to receive news updates and breaking stories.