Subscribe to Martyr2's Programming Underground        RSS Feed
***** 3 Votes

Inserting Images Into MySQL and Retrieving Them Using PHP

Icon 15 Comments
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!

<Lady GaGa and Usher's love child and the theme song "it" would make would play here>

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! :)

If you want more blog entries like this, check out the official blog over on The Coders Lexicon. There you will find more code, more guides and more resources for programmers of all skill levels!

15 Comments On This Entry

Page 1 of 1

Sergio Tapia Icon

25 July 2011 - 01:24 PM
Good write up for a topic lots of people would be curious about at some point in their careers.

Just wanted to point out that Microsoft SQL Server has a specific data type for this situation, called a Filestream. Sometimes it's best to choose this data type, other times it's best to link to the path on the filesystem.

More info here:
http://technet.micro...y/bb933993.aspx
1

Martyr2 Icon

25 July 2011 - 02:29 PM
Thanks for the addition Sergio! Yeah there are some database vendors out there that have created vendor specific types to help solve the problems that BlOB had originally. :^:
0

RudiVisser Icon

25 July 2011 - 02:51 PM
Rather than $content = addslashes($content);, you should be using mysql_real_escape_string to catch any characters that addslashes may not! :)
0

Martyr2 Icon

25 July 2011 - 11:17 PM
Addslashes here in this particular case is fine. We are not worried here about SQL injections nor is character set really an issue. The differences between the two are minor in that mysql_real_escape_string handles character sets, null bytes etc. Now if I was doing a MySQL specific query involving unknown user input, then yeah mysql_real_escape would probably be a smarter choice for a mysql database.

But again, this difference is not an issue here. Use which ever you like.

:)
0

RudiVisser Icon

26 July 2011 - 03:14 AM
True, but for the purposes of a tutorial wouldn't it be best to go "best practice" route? Will teach the youngsters to always do it I guess :)
0

Martyr2 Icon

26 July 2011 - 09:00 AM
Most certainly, but what I am saying is that it isn't best practice to always go with mysql_real_escape_string. Both do the job here equally well here and both are perfectly acceptable for what we are showing. mysql_real_escape_string isn't the magic bullet solution to SQL injection prevention anyways. But your points are taken. :)
1

JackOfAllTrades Icon

28 July 2011 - 01:11 PM
Thank you, Martyr2, for yet another excellent blog entry :)
1

Shane Hudson Icon

30 July 2011 - 11:53 AM
Oh dear... whenever people have asked me how to store images in the database I have replied saying "This in not Microsoft Access! Real databases link to images, not store them."... I did not know about this method at all! I have seen BLOB but never looked into it.

So thanks for the great blog entry. How come you do not post these as tutorials? Much easier to find!
0

yondaime Icon

31 July 2011 - 09:18 AM
thank you ....
I always read your topics and I like what you write
0

Martyr2 Icon

31 July 2011 - 02:35 PM

Shane Hudson, on 30 July 2011 - 11:53 AM, said:

Oh dear... whenever people have asked me how to store images in the database I have replied saying "This in not Microsoft Access! Real databases link to images, not store them."... I did not know about this method at all! I have seen BLOB but never looked into it.

So thanks for the great blog entry. How come you do not post these as tutorials? Much easier to find!



Shane, you are towing the line with the regard to the proper message. I very often tell others that they should not store images into the database either. So thumbs up for that! :^:

I wrote this as a message to those who have seen the BLOB field type, have heard rumors of storing images and are trying to do it just to see how it is done. But if asked in the forums I say the same, always store a path since that is the most flexible and non performance robbing thing to do.

Why don't I write these as tutorials? Well, they are not usually extensive enough to be a tutorial. While I go through the process of step by step explanations, it is just for clarity. Not to be exhaustive like most tutorials should be.

Thanks for the comments and reading up on my blog! I hope you continue to do so in the future. :)
0

Jstall Icon

01 August 2011 - 12:31 PM
Nice entry :).

I've noticed questions about storing files as BLOBs popping up allot on the PHP and MySQL forums lately(maybe you did too and this is why you wrote this). Now we can have a nice in-site link to point them towards as a reference :)
0

kiwi2 Icon

09 August 2011 - 12:22 PM
As usual your articles are very informative, however, when working on a real world project, and considering space limitations on file storage for images, blobs seemed like a better option since overall they took up less space.
see
http://www.kidslovepc.com
for futher info
0

Martyr2 Icon

13 August 2011 - 10:05 AM
Blobs in a database and images on a file system still require the same amount of data to be stored. If anything, databases become less efficient at storing images over 256KB. I have worked on a number of real world projects involving images and a database and I have always chosen to go the route of storing images on the file system for a number of reasons:

1) Databases theoretically have this performance issue with images bigger 256KB
2) Scenarios where someone else is uploading the image I can't control how big the images they use are, so I wouldn't want them to store anything bigger than 256KB in the database and ruin performance
3) File systems are generally better equipped to handle bigger files and why would you want to bloat your database for resources that probably wouldn't change that often?
4) You gain the advantage of text filename searching when you store the path to a file. I can search for records that contain "mypic.jpg".

Now am I saying there is no advantage to blobs? No. Again, if you are under 256KB (depending on your platform) you can very well handle images effectively. However, to imply that some how that blobs are space savers is not quite right. If anything they can take more space. MySQL for instance stores the first 256 bytes in the database and then creates a pointer to the rest of the data which is stored in a 2000 byte field. Meaning if you have 257 bytes, the first 256 is stored and then 1 byte is stored in a field that is 2000 bytes. This results in a field which is 2256 rather than 257.

Just be cautious about using images in a database. If they are small images (again like icons) there shouldn't be too much of a problem using blobs. If you are looking to store bigger, do consider a file system alternative and store the path in the database. 9 out of 10 times I recommend that path.

:)
1

aaron1178 Icon

26 August 2011 - 04:02 AM
Is it good practice to store an image into the database, or to upload the image to a directory? Would adding say a 5MB png to the database, will this not consume server resources?
0

Martyr2 Icon

26 August 2011 - 01:58 PM
aaron, that is what we have been talking about all this time. Ideally it is NOT good practice to upload large images to a database. Most of the time you want to upload it to a directory on the server and store the path to that image in the database instead.

However, if the image is small (typically a few KB) then you can certainly put it into the database with little real impact. A 5MB file I would definitely put in a directory and link to it from the database.

When it comes to large files often the file system of the server's operating system can handle it much more efficiently than a database. You can re-read my blog post where I talk about the pros/cons of storing images in a database and my recommendations.

:)
0
Page 1 of 1

April 2014

S M T W T F S
  12345
6789101112
131415 16 171819
20212223242526
27282930