Pulling BLOB image data from MySQL to PHP
I was reading from several tutorials on how to upload my image to the DB as binary versus putting them on the server itself, so 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 problem is how you fetch it from the database now, I read several ways to do it, tried them all, can't figure it out, I don't get the MySQL error, this is how I try:
$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."\" >";
}
It works ... kind of what's going on is that it prints raw binaries in the IMG tag.
How do I get this to compile the read image again? Also, storing images in the database is silly? Should I just do what I usually do and store them on the server?
Thank. -Mike
source to share
You can store images in your database if you like (although there is nothing wrong with just saving them as files, choose whichever suits your situation), but store the raw binary data in a BLOB (i.e., don't encode its with base64). You can directly insert binary data from file_get_contents
into your query if you use the correct evacuation function ( mysql_real_escape_string
in your case) first.
As for the output of the image, you can do it the way you are doing it right now, but you will have to output it base64 and using the URI scheme data
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 to inline image data. Some important disadvantages to keep in mind are the heavy base64 encoding overhead (about 33% more than the original) and potential caching issues.
source to share
Unless you have a specific reason to store image data in your database, I highly recommend that you just do it the usual way.
This is because your database engine will experience significant performance gains as you will receive and push more data than is necessary.
Also, BLOB fields in MySQL tables are significantly larger than other tables in terms of size and perform most operations in most scenarios.
Just imagining the overhead on your server, once you implement this I get chills.;)
I think it all comes down to scalability . Once your database is full, your server will become less responsive and become a real swamp over time. Your additional parameters would be to increase the server's RAM or change the code to accommodate more load.
Just my 2 cents, hope this helps!
Good luck!
source to share
Actually, the best aproach is to have a PHP script to output the image binary with appropriate headers (like getimage.php) and the script used to generate HTML would have 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 this:
<img src="getimage.php?id=12345" title="the caption of" />
You must have a primary key (why not?) On a table images
, say id
.
The "getimage.php" script should fetch the binary and output the content (assuming the field img_location
contains the actual 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 misinform the variables and choose a different MySQL connection method like MYSQLi or PDO. Another good idea is to use prepared statements to prevent SQL injection.
source to share