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.

How to create an image uploader using php

This is a 2-part tutorial series where I’m going to show you how to create an image viewer in php. Which will consist of:

  1. image uploader
  2. image viewer

What you’re reading right now is the first part. That is how to upload images using html and php.

 

Requirements

You are only going to need Wampserver if you are on Windows.

 

Procedure

First you’re going to need to build the table that were going to use:

CREATE TABLE IF NOT EXISTS `tbl_products` (
  `productID` int(11) NOT NULL AUTO_INCREMENT,
  `str_productName` varchar(100) NOT NULL,
  `str_category` varchar(80) NOT NULL,
  `str_description` varchar(700) NOT NULL,
  `dbl_qty` double NOT NULL,
  `dbl_price` double NOT NULL,
  `str_filename` varchar(70) NOT NULL,
  PRIMARY KEY (`productID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=16 ;

Just access phpmyadmin. Create a sample database, then execute the code above as an sql to build the table.

Next, create a new php file. And copy the following code:

<form action="create_item.php" method="post" enctype="multipart/form-data">
<p>
<label for="item_img">Item Image:</label>
<input type="file" name="item_img" id="item_img">
</p>
<p>
<label for="product">Item:</label>
<input type="text" name="product" id="product">
</p>
<p>
<label for="category">Category:</label>
<input type="text" name="category" id="category">
</p>
<p>
<label for="description">Description:</label>
<textarea id="description" name="description" id="description">
</textarea>
</p>
<p>
<label for="qty">Quantity:</label>
<input type="text" name="qty" id="qty">
</p>
<p>
<label for="price">Price:</label>
<input type="text" name="price" id="price">
</p>
<p>
<input type="submit" name="create_item" value="Create Item">
</p>
</form>

There’s really nothing new and fancy in this code. Except for the enctype attribute for the form. Then the file input type.

First I’ll explain what enctype is. Enctype is short for encoding type. Which means that when we are using this attribute, we are specifying how the data which is submitted through that form is encoded. By default you don’t really need to use this attribute if you’re just submitting text. But since were going to submit an image were gonna need to specify the enctype. And the proposed enctype for submitting images is multipart/form-data.

Next, we process the submitted data:

if(!empty($_POST)){
  //code goes here
}

I just did that to make things short. But normally you would need to check if the post variable for the submit button contains anything.

Next, do a simple data processing. To make things easier on the fingers. Just create a loop which will loop through all the post data. We then do the string cleansing inside the loop, and assign the cleansed data into an array called $proc.

$proc = array();

foreach($_POST as $key=>$raw){
	$proc[$key] = strtoupper(trim(mysql_real_escape_string($raw)));
}

In case you need a little helping what each function does:

  • strtoupper – converts a string of text into uppercase format. Something like this-> “hELLo”. Will turn to this-> “HELLO”. This is to ensure that all the data that will be stored in the database has the same format. Of course you can also do strtolower.
  • trim – removes the leading and trailing whitespaces on your strings. It won’t really make much sense if I created an example here. So just try it by yourself.
  • mysql_real_escape_string – removes any invalid characters from a string. By invalid characters, I mean something that could ruin an sql query if that character actually existed in the query. Most of the time this is the only thing that you’ll need to ensure the sanity of data that’s going to enter into your database.

 

Next, assign the processed data into their corresponding variable:

$item = $proc['product']; $category = $proc['category']; $descr = $proc['description']; $qty = $proc['qty']; $price = $proc['price'];

$filename = $_FILES["item_img"]["name"];

By corresponding variable, I mean that you’re going to assign it into something that would actually describe what that variable is going to store. Not something gibberish like $thisVariable or $str1 or $var1.

I think something needs some explanation here. That is the $filename variable. This stores the filename of the image that was selected using the form. This only stores the filename and not the actual path. Example: angrybirds.jpg.

Then perform the sql query that would insert the data into the table:

$create = $db->query("INSERT INTO tbl_products SET str_productName='$item', str_category='$category', 
				str_description='$descr', dbl_qty='$qty ', dbl_price='$price', str_filename='$filename'");

Finally, we check if the file that is being uploaded meets our criteria:

  • It has to be an image file (jpg, png, gif)
  • It has to be 1 953.125 Kb. (It could go lower or higher depending on what you want)
  • It must not already exist in the folder which stores images (Of course, you can just fake this one by giving a different filename to the same exact image)

Here’s the code:

if ((($_FILES["item_img"]["type"] == "image/gif")
|| ($_FILES["item_img"]["type"] == "image/jpeg")
|| ($_FILES["item_img"]["type"] == "image/pjpeg")
|| ($_FILES["item_img"]["type"] == "image/png"))

&& ($_FILES["item_img"]["size"] < 2000000))
  {
  if ($_FILES["item_img"]["error"] > 0)
    {
    echo "Return Code: " . $_FILES["item_img"]["error"] . "<br />";
    }
  else
    {
    echo "Upload: " . $_FILES["item_img"]["name"] . "<br />";
    echo "Type: " . $_FILES["item_img"]["type"] . "<br />";
    echo "Size: " . ($_FILES["item_img"]["size"] / 1024) . " Kb<br />";
    echo "Temp file: " . $_FILES["item_img"]["tmp_name"] . "<br />";

    if (file_exists("../img/items/" . $_FILES["item_img"]["name"]))
      {
      echo $_FILES["item_img"]["name"] . " already exists. ";
      }
    else
      {
      move_uploaded_file($_FILES["item_img"]["tmp_name"],
      "../img/items/" . $_FILES["item_img"]["name"]);
      echo "Stored in: " . "upload/" . $_FILES["item_img"]["name"];
      }
    }
  }
else
  {
  echo "Invalid file";
  }	
}

Here’s a breakdown of the variables that we used:

  • $_FILES[“item_img”][“type”] – item_img is the name that we gave to our input with a type of file(input type=file) . And the type just stores its file extension. Not necessarily the type since you can just freely change the file extension.
  • $_FILES[“item_img”][“name”] – this stores the filename, together with the file extension. I have a feeling that I already said that a while ago but never mind.
  • $_FILES[“item_img”][“error”]  – in case there is an error in the image to be uploaded. Whatever it is, this variable stores it.
  • $_FILES[“item_img”][“size”] – as the name suggests. This simply stores the size of the file.

And here’s a breakdown of the functions that we used:

  • file_exist() – checks whether a file exist on a specified folder in the server. This requires 1 argument by default. That is, the absolute or relative address of the image in the server. What we implemented in the above code is the relative address. If you want you can also use the absolute address: “http://localhost/store_is/img/items/” . $_FILES[“item_img”][“name”]
  • move_uploaded_files()  – as the name of the function suggests. This simply moves the selected file to a specified folder in the server. This requires 2 parameters: first is the source address, second is the address where you want to move it. You don’t actually need to write a bunch of code to determine the actual location of the file in the client computer(the one accessing the server). Because its already stored in a temporary folder in the server. Once you submit a data to the server. Normally it is stored under: C:wamptmp . But of course, the files stored in that folder doesn’t actually take the form of an image.

 

Conclusion

I guess that’s it for this tutorial. You have learned how to:

  • sanitize string using foreach loop.
  • validate file size, and file type of an uploaded file in php
  • check if the file already exists in the specified folder in the server
  • move the uploaded files to a specified folder in the server

In the part 2 of this series. I’m going to show you how you can build an interface where you can view those images along with a description fetched from the database.