Inserting Images Into MySQL and Retrieving Them Using PHP

Welcome to another exciting episode! Today we will be discussing a topic that seems to be a bit of a confusing one for everyone… storing BLOB images in MySQL and then showing them again using PHP. While it is always advised against, we will show you a little demo code for how this can be done and discuss why/why not do this. We will then talk a little about the alternative and why you should prefer that over storing images directly into the database. All this and more on the Programming Underground!

Ok, so BLOBs or “Binary Large Objects” are fields in a database used to store raw binary data. This is data that may represent things like pictures, files or perhaps text in a binary format. Since these types of objects are rather large (could be even Gigabytes in size) the field is designed to hold a lot of information. How do we use these fields in PHP to insert, let’s say, a profile picture? Well it is a pretty simple process.

1) We have to read the image as binary.
2) We have to prepare it to be inserted into our database (escape it).
3) We insert it into the database using typical SQL

For our example we will use a three step process…

1) Set the content type of the document to match the type of picture it is (image/jpeg for jpg images)
2) Locate the image in the database and pull it out
3) Show it

So here is some PHP code I whipped up real quick to demonstrate inserting it…

// Image submitted by form. Open it for reading (mode "r")
$fp = fopen($_FILES['file_name']['tmp_name'], "r");
	
// If successful, read from the file pointer using the size of the file (in bytes) as the length.	 
if ($fp) {
     $content = fread($fp, $_FILES['file_name']['size']);
     fclose($fp);
	
     // Add slashes to the content so that it will escape special characters.
     // As pointed out, mysql_real_escape_string can be used here as well. Your choice.		 
     $content = addslashes($content);
		
     // Insert into the table "table" for column "image" with our binary string of data ("content")	 
     mysql_query("Insert into table (image) Values('$content')");
}

Here we open the file for reading and read the image into a variable. This call to fread() is reading the bytes of our image and so we give it the byte length of the image file. This can be obtained from the $_FILES array using the “size” key. Once we have it in a variable, it is just a matter of inserting it into the table for the column that is of type “BLOB”. In our example the column “image” is a “BLOB” data type. Make sure that the BLOB you choose in MySQL will also be big enough to hold the data since MySQL does have different size BLOB data types.

Now we want to fetch that image out!

// Read the row we want to pull out of the database.
$result = mysql_query("select image from table where id = 1");

// If successful, fetch the row as an array and store the data from the "image" column into a variable.
if ($result) {
    if ($row = mysql_fetch_array($result)) {
       $img = $row["image"];
    }
}
	
// Set the content type of this page to image/jpeg since the image we are pulling out is a jpg image.	
header("Content-type: image/jpeg");

// Echo out the image.
echo "$img";

Here we are locating the desired image we want using the ID of the row. We then pull out the data from the column “image” and put it into a variable which we will echo out in a second. After we set the header of the page to have a content type of image/jpeg, we can then echo out the binary data from our variable.

Now keep in mind that you can’t print anything prior to the header() call. If you do, even printing a space, it will flush the headers and cause an error when you try to call header() again. Note: The error will be along the lines of “Headers already sent”.

This process will be very similar with other databases that have blob fields as well. The idea here is that we are taking a binary object, inserting it as an escaped string, reading it back and displaying it with the content type that tells the browser that we are showing image data.

So why shouldn’t we store images into a database like this? Well, databases (MySQL especially) are good for quick data lookups and short chunks of data. Databases typically take this data and break it up into pages that they can quickly locate on disk. Large fields like BLOBs really bloat databases and the advantages we enjoy, like using the file name in a search. Bloating a database with large amounts of binary data can slow our queries down since databases are then forced to read through a 2 Gigabyte BLOB (potentially) just to read it for display. Why do this to yourself? 😉

The solution: Instead of storing a large file as binary in the database, why not keep the database lean and mean by storing a path to the file in the database instead? Think of this as a pointer to the object. In the event you need to show the image you can look up this field in the database, get the file path and then use the path to locate the picture from disk for display. This gives you two advantages: 1) It keeps the data the database has to manage to a minimum (aka its footprint) and 2) with a file path we can search it using queries to locate a file by file name.

Then why have BLOB fields at all? Well, the field can be a good thing if your images were really small (like an icon). I don’t recommend using a ton of icons in your database either, but small chunks of binary data can be ok without causing too much of a performance nightmare. But leave the database to manage simple quick pieces of data and large files to the file system. Follow this rule and your systems will always thank you for it!

Thanks for reading! 🙂