Creating thumbnails for Image Large Blob data and storing thumb in mySQL database

I have a database that has farily large images stored as large blobs in the database.

The code that stores the image after it is submitted via POST is like this:

if ($_REQUEST['completed'] == 1) {
        // Need to add - check for large upload. Otherwise the code
        // will just duplicate old file ;-)
        // ALSO - note that latest.img must be public write and in a
        // live appliaction should be in another (safe!) directory.
        $instr = fopen("latest.img","rb");
        $image = addslashes(fread($instr,filesize("latest.img")));
            $imageSize = strlen($image);
        if (strlen($image) < 5000000) {
                mysql_query("UPDATE images SET cellframeimageData ='".$image."' WHERE cellId ='".$_REQUEST['cellId']."' AND cellframeOrder ='".$_REQUEST['cellframeOrder']."'",getdbHandle());
        } else {
                $errmsg = "Too large!";

For displaying images I am currently using something like this:

<img src='imageGetter.php?cellId=1&cellframeOrder=0' width=200

where the target page grabs the blob data from the database and spits it back.  The base code for that is like this:

$query_image="select cellframeimageData from images where cellId = '".getUrlStringValue('cellId',null)."' and cellframeOrder = '".getUrlStringValue('cellframeOrder',null)."'";
$row = mysql_fetch_assoc($res);
if (isset($cellframeimageData)) {
header("Content-type: image/jpeg");
echo $cellframeimageData;

All of that is working as far as storing the images in the database and also for displaying them, although I do have a couple of questions.

1.  It would be nice if it were possible to bypass the call to the imageGetter.php page and somehow diplay the image inline by directly pulling it from the database rather than having it reference the external page.  I guess there isn't a convenient way to do this as you would have to somehow embed the image source in the page rather than in a external file on the server of via the on-the-fly method using the external php page?

2.  I also want to be able to create some thumbnail images from the larger blob images above and store them in the database as well.  Again, if it were possible to pull and display the thumbs from the database without having to use the external php page that would be nice.

3.  I noticed that there is alot of discussion about the merits of storing images or large files in a database rather than in files on the server.  I am kind of playing with that to see how the performance is and to see what I would prefer, although I like the idea of having a self-contained file that stores the whole set of data rather than having the images on the server, but I guess you can take a pretty big performance hit if the db gets large.  In my app, I would probably display the thumbs most of the time and only retrieve the larger images as requested.  I was hoping that that would perform fairly well for small images.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hello sscotti, , I have seen in too many database info web pages, that always say something like - "never store images in a database if you can avoid it!". There not only the large size of the data (upto 5000000 in your case, 100 images might be 500 megabytes), but more importantly, for the amount of data through put and access time that the database engine has to do. Every database through put affects all other database operations and through put at that time, as in slowing down some.

as for thumbnails, when the image  files are uploaded, there is a temp file that you can create thumbnails from, as latest.img in your code-
$instr = fopen("latest.img","rb");

for "bypass the call to the imageGetter.php" I have not seen any way to get an image froim a database, only from a php access

sscottiAuthor Commented:
Thanks for that.  I probably would limit the size of the images to less than 1 MB, and the thumbnails would be quite small.  Most of the comments that I've seen do suggest storing the images on the file server rather than in the database, although it is nice to have them in one file, the database.  The web app actually would usually display the thumbnails only, and the larger images would only be display one at a time on demand.  Still seems like there should be a way to create an image directly from the database rather than having to use the imageGetter php file.

Do you actually have code for creating the thumbnails.  I am not sure how to do that.  I do have the ?GD library installed.
OK, sorry about the delay, things to do, LOL. . .
you say - "seems like there should be a way to create an image directly from the database"
NO WAY!, you can not get into MySQL databases from a file like - "images/getimg.sql", you must call a server side script PHP or CGI (or others) to get the server to make function calls and access the database.

I have some code here for you to try, it is a TEST php page, to see if you understand and can use the GD images functions for a thumbnail, the main code for thumbs if in the function  doThumb( ) there is some dimension testing in the code

this is a TEST, so much of the user input is not really user input except for the upload

AND - I have in included some mysqli database code, to show you a better and more secure way to use a user info database  INSERT
if you do not have mysqli or do not want to consider it , please ignore my suggestion
<html><head><title>DoUp File Upload1</title></head><body BGCOLOR="#E3F7FF"><h2>Can Upload a file</h2>
define("MAX_THUMB", 108);//set this to the thumbnail DIMENTION max

// doThumb( ) if the IMPORTANT resize to thumb function here
function doThumb($imInfo, &$imgT){
if (!is_array($imInfo)) return false;
$height = MAX_THUMB;
$width = MAX_THUMB;
if ($imInfo[0] < $imInfo[1]) {
$width = (int) ((MAX_THUMB / ($imInfo[1] / $imInfo[0]))+ 1);}
elseif ($imInfo[0] > $imInfo[1]) {
$height = (int) ((MAX_THUMB / ($imInfo[0] / $imInfo[1]))+ 1);}
if ($height < 2) $height = 2;
if ($width < 2) $width = 2;
$outImg = ImageCreateTrueColor($width, $height);
if (!ImageCopyResampled($outImg, $imgT, 0, 0, 0, 0, $width, $height, $imInfo[0], $imInfo[1])) return false;
return $outImg;

$aDo = 0;
$fmime = '+';
$smImg = false;
$stat = (empty($_POST['status1'])) ? '+' : $_POST['status1'];
if (($stat != '+') && (strlen($stat)==7) &&($_POST['MAX_FILE_SIZE'] == '65536')) $aDo = 1;
if ($aDo == 1) {$stat = '+'; $fsize =$_FILES['upfile']['size'];
	if ($_FILES['upfile']['tmp_name'] == '') $aDo = 5; elseif
	($fsize < 16) $aDo = 3; elseif
	($fsize <= 65536) {
	// ALWAYS make sure the upload is a valid IMAGE file
	$imageinfo = getimagesize($_FILES['upfile']['tmp_name']);
	if ($imageinfo['mime'] == 'image/gif') {$stat = '.gif'; $fmime = $imageinfo['mime']; $temImg = imagecreatefromgif($_FILES['upfile']['tmp_name']);}
	elseif ($imageinfo['mime'] == 'image/jpeg') {$stat = '.jpg'; $fmime = $imageinfo['mime']; $temImg = imagecreatefromjpeg($_FILES['upfile']['tmp_name']);}
	elseif ($imageinfo['mime'] == 'image/png') {$stat = '.png'; $fmime = $imageinfo['mime']; $temImg = imagecreatefrompng($_FILES['upfile']['tmp_name']);}
	else $aDo = 2;} else $aDo = 5;
if (($aDo == 1) && ($stat != '+')) {
	// this starts the thumb Image code
	if ($imageinfo[0] > MAX_THUMB || $imageinfo[1] > MAX_THUMB) 
		$smImg = doThumb($imageinfo, $temImg); else
		{$smImg = $temImg;}
	if($smImg != false){
	// DO NOT save the thumb as the same file type of upload
	// this is the end of the thumb image code
	$upfile = 'images3/ups/';
	for ($i = 0; $i < 7; $i++) $upfile .= chr(rand(97, 122));
	$upfile .= $stat;
	//unlink($upfile); // deletes up file

	if (!move_uploaded_file($_FILES['upfile']['tmp_name'], $upfile)) {$aDo = 4; $stat = $_FILES['upfile']['error'];}
	else {$iread = file_get_contents($upfile);
	if ($iread === FALSE) {$aDo = 6; $stat = 'File contents iread NOT valid';} else {
	$null = NULL;
	$mysqli = new mysqli('localhost', 'user', 'password', 'db_name');
	if (!mysqli_connect_errno()) { $user2 = 'bob'.chr(rand(97, 122));
		$query = "INSERT INTO imageb1 (size1, path1, patho2, user1, imgBlob1) VALUES (?,?,?,?,?)";
		$stmt = $mysqli->prepare($query);
// WARNING - Do NOT use the BLOB type for the bind_param("sssib" , it will not write anything, you have to use packet send send_long_data for blob
		$stmt->bind_param("isssb", $fsize, $upfile, $fmime, $user2, $null); // use "sssis" and not send_long_data( )
		$stmt->send_long_data(4, $iread);
		if(!$stmt->execute()) echo 'execute has failed<br />';
		if ($mysqli->errno) echo 'QUERY Failure1 '.$mysqli->error.'<br />'; 
	} else {$aDo = 6; $stat = mysqli_connect_error().'<br />';} // if (!mysqli_connect_errno()
	} // else -if ($iread === FALSE
	} // else -if (!move_uploaded_file
	} // if (($aDo == 1)&&($stat != '+')
	} // if ($aDo == 1)
if($smImg != false){echo '<img src="images/temp.jpg" />';}
if ($aDo == 1) echo '<h3>Flie was uploaded</h3><img src="'.$upfile.'" /><br />'; elseif
($aDo == 2) echo '<h3>ERROR - Uploaded File was NOT a JPG or GIF Image</h3>'; elseif
($aDo == 3) echo '<h3>ERROR - File Size is NOT acceptible</h3>'; elseif
($aDo == 4) echo '<h3>ERROR - '.$stat.'</h3>'; elseif
($aDo == 5) echo '<h3>ERROR - File Size was greater than 65536 bytes</h3>'; elseif
($aDo == 6) echo '<h3>ERROR - '.$stat.'</h3>';
$upfile = '4';
for ($i = 0; $i < 6; $i++) $upfile .= chr(rand(97, 122));
Only JPG and GIF images less than 65536 bytes allowed. (64 Kb)
<form enctype="multipart/form-data" action="doup1.php" method="POST">
<input type="hidden" name="status1" value="<?php echo $upfile; ?>" />
<input type="hidden" name="MAX_FILE_SIZE" value="65536" />
Load Up this file: <input name="upfile" type="file" /><br />
<input type="submit" value="Send Up This" />

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
it says "only JPG and GIF images"
this is NOT true, it can also do PNG images as well
you must change the
in the form to your php page
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.