How to store and retrieve images from mysql database

In this tutorial I’m going to show you how you can store and retrieve images from mysql database. Remember that this is not always the best way to go if what you want to do is to be able to add image upload functionality to your website. You can always specify a folder in the web server to be used as the upload directory. And there are php functions which allows you to create directories in your web server in case your problem is categorizing the images into different folders.

Requirements

  • Wampserver
  • Text Editor

 

Creating the Table

First we need to create the table where we are going to store the images. If you already have researched on storing images on a database, you might already know that in order to store images, we need a special data type called blob. Therefore we need to specify the image field to be of type blob. At its simplest level, your table might look like this.

Field Data type
image_id int[PK][Auto-increment]
image blob

 

Storing the Image

Once you’ve built the table its time to write the script that will store the image into the database.

First, include your database configuration file:

require_once('image_config.php');

Open the image file using the fopen() function:

$file = fopen("switch.jpg", "rb");

Then use the fread() function to store all the necessary image information. The fread() function requires 2 arguments. The file resource, and the file size. The file size is easily obtained using the filesize() function. Just be sure to supply the correct argument, an image which actually exists on a specific location on your computer.

$image = fread($file, filesize('switch.jpg'));

Then use the fclose() function to close the file. This is to make sure that the image resource can’t be used later on.

fclose($file);

Next, use the base64_encode() function to encode the image into base 64. This is to make sure that the image won’t be truncated when you store it on the database. More like a file covering that makes sure that the file will be intact.

$image = base64_encode($img);

Lastly, perform a usual insert query to store the encoded image data into the database:

$db->query("INSERT INTO tbl_images SET image='$img'");

The data would look like this in phpmyadmin:

image

It has the word BLOB on it, together with the file size in KiloBytes. In the example above, the file size is 60KB. But the actual size of the image that I used is just 45 KB.

The file size increased because we used the base64_encode() function a while ago. Based from what I’ve read at PHP.Net, file size increases by 33% if you use base64_encode(). This is a down-side when compared to that of just storing the images in a folder in the web server.

Basically, your database will grow into a big monster which eats up lots of space if you use this method to store images into your database.

 

Retrieving the Image

Okay, were here at the most awaited part. The actual retrieval of the image that has been stored in the database.

First of all you need to specify the type of content that you want to display on the browser. This is the very first thing that should appear on the script which will output the images. Not even your database include, doctype or <html> tags will come first. Everything will crumble if the content type declaration doesn’t come first.

<?php

header("Content-Type: image/jpeg");

?>

Just to give you an example of what happens when you did not specify the content type at the upper most portion of the page:

image

Yep! All you will see is gibberish that a normal human person could not understand. Maybe not even the geeks who have reach geek nirvana is able to understand this.

Next, include your database configuration file.

Then retrieve a single row from the table:

$retrieve = $db->get_row("SELECT image

FROM tbl_images WHERE image_id = 2");

Lastly, do a one-liner code to output the image in the browser:

$image = imagejpeg(imagecreatefromstring(base64_decode($retrieve->image)));

Let’s break the code down:

  • base64_decode($retrieve->image) – what this does is to decode the image data. This simply converts the image back to its original form before it was uploaded.
  • imagecreatefromstring() base64_decode() returns the gibberish set of characters that I showed you a while ago. Those set of gibberish characters is used by the imagecreatefromstring() function to create an image
  • imagejpeg() this simply outputs the image file into the browser

Here’s a sample output:

image

 

Conclusion

That’s it! You’ve learned how to store image data into the mysql database. As I have said earlier, this method consumes much more space than just saving the uploaded images into a specified folder in the server. And this isn’t actually very nice, because when specifying the content type to be an image. All other contents like text will not be displayed. Only the image will be displayed.

5 thoughts on “How to store and retrieve images from mysql database

  1. dear sir,
    how to store hotel data in website like photo , map amenities ect .
    how can display to web site. You have used example for cleartrip.com .
    kindly please anser .
    thanking you
    tundra

  2. Hello sir,
    what is that image_config file, is it already exist in system or we need to create it
    plz do reply
    thanking you.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s