images in mysql db

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

yamokosk
Forum Newbie
Posts: 16
Joined: Wed Jun 04, 2003 2:35 pm

images in mysql db

Post by yamokosk »

First I want to say that I have scoured this site and many others for answers, but now I just want a fresh set of eyes to look at my code to see where i am going wrong. (Oh and secondly, I already know most people are against storing images in databases.. but i have my reasons.)

Everything seems to work great, except when it comes to displaying the image. Here is what I have:

This is the function I wrote to put images into the database. Some might notice the "mysql_insert_id()".. which seemingly defeats the purpose of using PEAR DB, but I am lazy and it works. I will fix that when/if I get this working.

Code: Select all

function add_image($article_id, $description, $file) {
                $db = DB::connect(CMS_DSN);
	if (DB::isError($db))
		die ($db->getMessage());
	
	if ($fileї'size'] > MAX_FILE_SIZE) {
		return "ERROR: File is larger than max allowed.";
	}
	
	$sql = "INSERT INTO cms_images (article_id, description, filename, filesize, filetype) 
			VALUES ('" . $article_id . "', '" . $description . "', '" . $fileїname] . "', '" . $fileїsize] . "', '" . $fileїtype] . "')";
			
	$result = $db->query($sql);
	if (DB::isError($result))    
		die ($result->getMessage());
	
	$id = mysql_insert_id($db->connection);
	
	$fp = fopen($fileї'tmp_name'],"rb");
	while (!feof($fp)){
		$data = addslashes(fread($fp, MAX_SQL));
		
		$sql = "INSERT INTO cms_imagedata (image_id, data) 
				VALUES ('" . $id . "', '" . $data . "')";
				
		$result = $db->query($sql);
		if (DB::isError($result))    
			die ($result->getMessage());
	}
	
	$db->disconnect();
}
Code to display image (this was a test file (test.php) I made to simplify things:

Code: Select all

$fetchmode = DB_FETCHMODE_ASSOC;
$images = array();

$article_id = $HTTP_GET_VARSї'id'];

$db = DB::connect(CMS_DSN);
if (DB::isError($db))
	die ($db->getMessage());

$sql = "SELECT *
		FROM cms_images
		WHERE article_id = '" . $article_id . "'";
			
$result = $db->query($sql);
if (DB::isError($result))    
	die ($result->getMessage());

$i = 0;
while ($row = $result->fetchRow($fetchmode)) {
	$imagesї$i] = $row;
	
	$sql = "SELECT data
		FROM cms_imagedata
		WHERE image_id = '" . $rowї'image_id'] . "'
		ORDER BY file_id DESC";
		
	$result_data = $db->query($sql);
	if (DB::isError($result_data))    
		die ($result_data->getMessage());
	
	while ($row_data = $result_data->fetchRow($fetchmode)) {
		$imagesї$i]ї'filedata'] .= stripslashes($row_dataї'data']);
	}
	$i++;
}
	
Header( "Content-type: image/pjpeg");
echo $imagesї0]ї'filedata'];
$db->disconnect();
The add_image() function above seems to work just fine. I can store multiple images with it (but I have not yet seen if I can store an image larger that MAX_SQL.. my test images are all smaller than that so far). But when I print_r() the images array, I get a nice listing of all of the params i stored in the dbase, including what looks like the binary data.

In a browser I would type "http://mysite/test.php?id=1" and I get the red X. So if you see something wrong with what I have, please let me know. I would greatly, greatly appreciate the help.
User avatar
Wayne
Forum Contributor
Posts: 339
Joined: Wed Jun 05, 2002 10:59 am

Post by Wayne »

pjpeg?? sorry a bit rust on image formats, but what format is this? are you sure the browser supports it?
yamokosk
Forum Newbie
Posts: 16
Joined: Wed Jun 04, 2003 2:35 pm

hmm..

Post by yamokosk »

Interesting. Thats just what I pulled from $files['filetype']. Except that I made it static just to narrow down and isolate the problem. So for *.jpg files is the type = image/jpeg?
User avatar
cactus
Forum Regular
Posts: 343
Joined: Tue Jun 10, 2003 4:16 am
Location: UK

Post by cactus »

yamokosk
Forum Newbie
Posts: 16
Joined: Wed Jun 04, 2003 2:35 pm

getting there..

Post by yamokosk »

Thanks cactus, good link. Well I changed that so now that line reads:

Code: Select all

Header( "Content-type: image/jpeg");
And I tried opening up "http://mysite/test.php?id=1" in Mozzilla for a change and got the error: "The image "http://mysite/test.php?id=1" cannot be displayed, because it contains errors." Which I guess is analogus to MSIE's red X. I used Mozzilla because I have read it is a bit more strict about the Content-type declaration in the header.

So I guess there is some sort of problem with either how I load the image into the DB or how i get it back out. I thought at first that maybe I was reading the datafile in the wrong order due to:

Code: Select all

$sql = "SELECT data
		FROM cms_imagedata
		WHERE image_id = '" . $rowї'image_id'] . "'
		ORDER BY file_id DESC";
I thought that maybe I had ordered the query in the wrong order. But I forgot that my test images are all smaller than MAX_SQL... therefore the test images only occupy one record.

Any ideas anyone?
User avatar
cactus
Forum Regular
Posts: 343
Joined: Tue Jun 10, 2003 4:16 am
Location: UK

Post by cactus »

Make sure there is no whitespace before sending your header, PHP barfs when something is outputted before you send a header.

Because you are expecting to see an image you won't see the error, check by changing the mime type to text/html as a test, you should be able to see any errors that are thrown.

Regards,
yamokosk
Forum Newbie
Posts: 16
Joined: Wed Jun 04, 2003 2:35 pm

whitespace?

Post by yamokosk »

What exactly do you mean by make sure there is not whitespace before sending the header? Do you mean no whitespaces in the php file? Or are you referring to having no white space in the actual header() call?

Well I tried setting content-type = text/html, and I got a screen full of garbage characters.. as expected. Does anyone have any other troubleshooting steps to try?

And thanks cactus for you help with this.
User avatar
cactus
Forum Regular
Posts: 343
Joined: Tue Jun 10, 2003 4:16 am
Location: UK

Post by cactus »

Whitespace generally occurs before you open your PHP tags, anthyting outside of the PHP tags is considered output (as well as if you did an echo/print etc), the header() method does'nt like this (or should I say, the RFC's don't allow this??? hmmm) and throws an error.

If you changed your mime type to text/html and saw garbage that is good, you are getting output that isn't true text/html (otherwise it wouldn't be garbage).

What I think you should do is use HTML and don't trust the browser to work out the mime type (all by it's self, in the usuaol M$ way):

Code: Select all

<img src="/path/to/your/script/test.php" height="50" width="50" alt="my image" border="0"/>
That way the render engine of the browser will kick in :)

Regards,
yamokosk
Forum Newbie
Posts: 16
Joined: Wed Jun 04, 2003 2:35 pm

Post by yamokosk »

ok i understand what you mean by whitespace now. and I double checked the script and there is none.

I am still getting the pretty little red X though... i just want my image! haha.

I read somewhere a guy used the php image functions to produce the image. Specifically, imagefromstring() (I think that was the function he used). Is this legit?
User avatar
cactus
Forum Regular
Posts: 343
Joined: Tue Jun 10, 2003 4:16 am
Location: UK

Post by cactus »

How did you get the image data into the database ? Was it base64_encoded() ? If so you will need to base64_decode() it :)

Sorry, I forgot to mention before (another post just reminded me), try throwing this header:

Code: Select all

header(" Content-Disposition: inline");
We will have that image displayed ;)

Regards,
yamokosk
Forum Newbie
Posts: 16
Joined: Wed Jun 04, 2003 2:35 pm

Post by yamokosk »

Here are the changes I made:

Code: Select all

//header("Content-type: image/jpeg");
header( "Content-Disposition: inline");
When I loaded the page, I got nothing but garbage characters again.

As far as getting the image into the database, the code that inserts the image is displayed above, but I will repost it here. It was a function that is passed a description, another id, and the contents of $_FILES. All that data came from a simple upload form. Here is that function again:

Code: Select all

function add_image($article_id, $description, $file) {
	$db = DB::connect(CMS_DSN);
	if (DB::isError($db))
		die ($db->getMessage());
	
	if ($file['size'] > MAX_FILE_SIZE) {
		return "ERROR: File is larger than max allowed.";
	}
	
	$sql = "INSERT INTO cms_images (article_id, description, filename, filesize, filetype) 
			VALUES ('" . $article_id . "', '" . $description . "', '" . $file[name] . "', '" . $file[size] . "', '" . $file[type] . "')";
			
	$result = $db->query($sql);
	if (DB::isError($result))    
		die ($result->getMessage());
	
	$id = mysql_insert_id($db->connection);
	
	$fp = fopen($file['tmp_name'],"rb");
	while (!feof($fp)){
		$data = addslashes(fread($fp, MAX_SQL));
		
		$sql = "INSERT INTO cms_imagedata (image_id, data) 
				VALUES ('" . $id . "', '" . $data . "')";
				
		$result = $db->query($sql);
		if (DB::isError($result))    
			die ($result->getMessage());
	}
	
	$db->disconnect();
}
Like I said before, I have not really tested it with images larger than MAX_SQL, to see if this function actually properly chops up images or not. My test image is smaller than MAX_SQL and gets saved into one row in the DB.

And no, I did not base64 encode (as you can see from the function). Although I did read somewhere else that that could solve problems with having to worry about using stripslashes() and addslashes(). (Oh and i just saw the PHP BBCode tags.. pretty.)
User avatar
cactus
Forum Regular
Posts: 343
Joined: Tue Jun 10, 2003 4:16 am
Location: UK

Post by cactus »

Firstly, you still need to throw the mime type for the image so uncomment the header.

Also, I have never tried to write the actual file into the database, I've always (been some time though) converted the image into a sting using the base64_encode() method.

Once in a string then I have wrote that to the database, to get it out, do exactly what you have been doing before but you will need to base64_decode() the returned string:

Code: Select all

header("Content-type: image/jpeg"); 
header( "Content-Disposition: inline");
echo base64_decode($images&#1111;0]&#1111;'filedata']);
Then create a simple HTML page with:

Code: Select all

<img src="/path/to/your/script/test.php" height="50" width="50" alt="my image" border="0"/>
To base64_encode() your image data you will ne to fread() the image:

Code: Select all

$handle = fopen($fileName, "r");
$contents = fread($handle, filesize($fileName));
fclose($handle);
Once you have the $contents then you will need to base64_encode() it:

Code: Select all

$b64Contents = base64_encode($contents);
You will need to write the $b64Contents to your mysql BLOB.

We must be getting closer now ;)

Regards,
yamokosk
Forum Newbie
Posts: 16
Joined: Wed Jun 04, 2003 2:35 pm

hopefully getting closer

Post by yamokosk »

Ok, first I just want to thank you cactus for all your help in this... but unfortunately its still not working... just to review, here are the changes I made.

At the end of test.php (the file that grabs the image data and echos it):

Code: Select all

header("Content-type: image/jpeg");
header( "Content-Disposition: inline");
echo base64_decode($images[0]['filedata']);
To my function that adds imagedata to the DB:

Code: Select all

$fp = fopen($file['tmp_name'],"r");
	
	while (!feof($fp)){
		$data = base64_encode(fread($fp, MAX_SQL));
		//$data = addslashes(fread($fp, MAX_SQL));
		
		$sql = "INSERT INTO cms_imagedata (image_id, data) 
				VALUES ('" . $id . "', '" . $data . "')";
				
		$result = $db->query($sql);
		if (DB::isError($result))    
			die ($result->getMessage());
	}
	fclose($fp);
And I created the .htm file with the <img> tag....

So I did all that, deleted the old image file that was in the database and then uploaded a new one so that it could be encoded64. I checked the DB and sure enough there was a record containing the imagedata in a long string. Then I loaded the .htm file. I got the red X. Then to troubleshoot, I changed Content-type = text/html and loaded test.php?id=1 and got a huge screen of garbage characters.

All I can conclude right now is that its actually encoding and decoding the bin data just fine. Maybe its a problem with my test image? Should I be concerned if the image type is reported as image/pjpeg? Should I create a test jpeg using say the image functions in php? Or am I incorrectly loading the string data into the DB? I am pretty sure that my SQL syntax is correct and the loop i setup is ok.

Hmmmm....
User avatar
cactus
Forum Regular
Posts: 343
Joined: Tue Jun 10, 2003 4:16 am
Location: UK

Post by cactus »

I would definitely try another image, JPEG/GIF etc, but remember to throw the correct mime type.

I'll have a dig around my server, I'm sure I have some code in CVS somewhere that should help.

:)
User avatar
Wayne
Forum Contributor
Posts: 339
Joined: Wed Jun 05, 2002 10:59 am

Post by Wayne »

try this when you are outputting the image from the database,

Code: Select all

echo stripslashes($images&#1111;0]&#1111;'filedata']);
instead of :

Code: Select all

echo $images&#1111;0]&#1111;'filedata'];
Post Reply