Pulling BLOB image data from MySQL in PHP

Question

I was reading from a few tutorials on how to upload my image into the DB as binary opposed to putting them on the server itself, well I got it to work like this:

PHP:

$image = chunk_split(base64_encode(file_get_contents($tmpfile)));
    mysql_query("INSERT INTO images (`img_location`, `caption`, `user`, `genre`, `when`) VALUES ('$image', '$caption', '$id', '$genre', '$when')");

My issue is how do you now pull it from the database, I've read several ways of doing it, tried them all, can't figure it out, I'm not getting a MySQL error, here's how I'm trying it:

$get_pics = mysql_query("SELECT * FROM images WHERE user='$id' ");
while($get_pics2 = mysql_fetch_array($get_pics))
{
$sixfour_enc = base64_decode($get_pics2['img_location']);

$new .= "<img src=\"".$sixfour_enc."\" >";
}

This works... kind of, what's happening is that it's printing out raw binary in the IMG tag.

How do I get this to compile to a readble image again? Also, is storing the images in the database stupid? Should I just do what I usually do and store them on the server?

Thank you -mike

Another Code · Accepted Answer · 2012-03-10 11:40:02Z

6

You can store images in your database if you want to (though there's nothing wrong with just storing them as files either, choose whatever is appropriate in your situation), but store the raw binary data in a BLOB (i.e. don't encode it with base64). You can embed the binary data you get from file_get_contents in your query directly, provided you use the proper escape function (mysql_real_escape_string in your case) first.

As for the outputting of the image, you can do it the way you're doing it right now, but you'll have to output it base64-encoded and with a data URI scheme like this:

echo '<img alt="embedded image" src="data:image/png;base64,' . chunk_split(base64_encode($get_pics2['img_location'])) . '">';

Note that there are some advantages and disadvantages of embedded image data. Some important disadvantages to be aware of are the severe overhead of base64 encoding (around 33% larger than original) and potential caching problems.

answered Mar 10, 2012 at 11:40

Another Code

3,14123 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1053263 Over a year ago

This makes complete sense, I reverted back to the old way of doing things after reviewing on the performance hit, but I did just throw this is to see if it worked, and it did very well! Thank you sir!

jdalangin · Accepted Answer · 2012-03-10 11:41:46Z

2

If you don't have any particular reason to store image data in your database then I strongly advise that you just do it the normal way.

This is because, your database engine will be taking a significant performance hit since you will be getting and putting more data than necessary.

Furthermore, BLOB fields on MySQL tables are significantly larger than other tables in terms of size and perform slower on most scenarios.

Just imagining the overhead on your server once you implement this gives me the chills. ;)

I guess it all boils down to scalability. Once your database fills up, your server become less responsive and will eventually become a real hog. Your next options would be to increase server RAM or revise your code to accommodate the bigger load.

Just my 2 cents, hope this helps!

Good luck!

answered Mar 10, 2012 at 11:41

jdalangin

1975 bronze badges

2 Comments

user1053263 Over a year ago

Yeah I'm going to do it the regular way. I was trying something new to see how it worked but then I realized how heavy my DB would be. I was thinking if it would be more secure and maybe easier doing it that way but it will most likely as you said take a huge performance hit. Thank you!

jdalangin Over a year ago

No worries, just here to help! :)

Tinel Barb · Accepted Answer · 2015-01-09 16:17:15Z

Actually, the best aproach is to have a PHP script to output the binary of the image with proper headers (say getimage.php) and the script used to generate the HTML will have a code like this:

$get_pics = mysql_query("SELECT id FROM images WHERE user='$userid' ");
while($imglist = mysql_fetch_array($get_pics))
{
    $new .= '<img src="getimage.php?id='.$imglist['id'].'" title="'.$imglist['caption'].'" />';
}

So the HTML will look like:

<img src="getimage.php?id=12345" title="the caption of" />

You must have a primary key (why not already?) on the table images, let's say id.

The "getimage.php" script shall extract the binary and output the content (assuming that the field img_location contains the actually content of the image:

<?php
// this will output the RAW content of an image with proper headers
$id=(int)$_GET['id'];

$get_img = @mysql_fetch_assoc(@mysql_query("SELECT img_location FROM images WHERE id='$id' "));

$image = imagecreatefromstring(base64_decode($get_img['img_location']));
if ($image){
    //you may apply here any transformations on the $image resource
    header("Content-Type: image/jpeg"); //OR gif, png, whatever
    imagejpeg($image,null,75);          //OR imagegif, imagepng, whatever, see PHP doc.
    imagedestroy($image);               //dump the resource from memory
}
exit();
@mysql_close();
?>

This approach will give you flexibility. However, you may need to check and sanitize the variables and choose another method of connection to MySQL, as MYSQLi or PDO. Another good idea is the use of prepared statements to prevent SQL injections.

Collectives™ on Stack Overflow

Pulling BLOB image data from MySQL in PHP

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related