Need help downloading image (type blob) from mysql

[kelly649]

Hello,

I've already posted this message on the 'database' forum, but didn't get a response, so I'm trying again here.

Basically, I can upload an image to the mysql database without a problem (storing it as type blob). However, I'm having great difficulty coding it so that a user can download it to their hard-drive via a link.

Here is my basic code that executes once the user clicks the download link:

$sql = "SELECT image, image_type, image_name, image_size FROM Designs WHERE design_id=$design_id";

$result = @($sql, $connect_ID);
$data = @mysql_result($result, 0, "image"); // the blob info
$name = @mysql_result($result, 0, "image_name");

$size = @mysql_result($result, 0, "image_size");
$type = @mysql_result($result, 0, "image_type");

header("Content-type: $type");
header("Content-length: $size");
header("Content-Disposition: attachment; filename=$name");
header("Content-Description: PHP Generated Data");
echo $data;

To make sure the variables were set correctly, I printed out $name, $size, and $type and they read as follows: car.jpg, 2256, image/pjpeg.

When I click the download link, I do get a "Save as" window (link I want), but it attempts to save the file as type: HTML (instead of jpg). I think the problem is the 'image/pjpeg' Content-type, since when I hard-code 'image/jpeg' into the Content-type header the save as window does list jpeg as the file type.

If I do go ahead and hard-code the Content-type as 'image/jpeg' and save the image to my hard-drive it works and does appear on my desktop. However, when I try to open it in windows preview-pane, Paint, or I.E., it isn't viewable. But, if I open it in Photoshop it displays fine. The only thing I can think is that it must be something I'm not doing correctly in the headers.

Any help you can provide would be greatly appreciated as I'm trying to complete a project and the user must be able to view downloaded image files in a browser after having been save to their hard-drive (not all users will have access to Photoshop).

Thanks a lot!!

[nemonoman]

First off: DON'T STORE IMAGES IN A DATABASE. THAT'S NUTS!!
Store the image as a file, and save a reference to the file in the database.

But to answer your question:

I BELIEVE you want to use the content type 'jpg' instead 'jpeg'.

Next: I'd really, really proofread your PHP generated Content-type: header. Make COMPLETELY SURE it says 'image/jpeg'...with no typos.

If it works when you hardcode it, something's different, and wrong, with the generated version.

To check, echo it out:
$mycontent="Content-type: $type";
die($mycontent); //for testing

header("Content-type: $type");
header("Content-length: $size");
header("Content-Disposition: attachment; filename=$name");
header("Content-Description: PHP Generated Data");

echo $data;

[kelly649]

Thanks for your advice. I've never stored images before and the first tutorial I found online suggested doing it directly in the database. Your way definitely sounds easier.

However, I'm still having the same problem. Once I download the file (that's now being stored as a .jpg in the web server file system), I'm still unable to view or open it in IE, Paint, or anything other than Photoshop.

Here's my new code:

$sql = "SELECT image_ref FROM Designs WHERE design_id=$design_id";
$result = @($sql, $connect_ID);
$image_ref = @mysql_result($result, 0, "image_ref");
$file_name = basename($image_ref);

// $image_ref is the url where the image file is stored on the server

// translate file name properly for Internet Explorer.
if (strstr($_SERVER['HTTP_USER_AGENT'], "MSIE")){
$file_name = preg_replace('/./', '%2e', $file_name, substr_count($file_name, '.') - 1);
}

// make sure the file exists before sending headers
if(!$fdl=@fopen($image_ref,'r')){ die("Cannot Open File!");}
else {

header("Cache-Control: ");// leave blank to avoid IE errors
header("Pragma: ");// leave blank to avoid IE errors
header("Content-type: image/jpg");
header("Content-Disposition: attachment; filename=\"".$file_name."\"");
header("Content-length:".(string)(filesize($image_ref)));
sleep(1);

fpassthru($fdl);
}

It really doesn't seem like this should be so difficult for such a common thing. I guess it must be IE's sensitivity to header information. Thanks.

[rachel2004]

My guess is the way you have escaped your Content-Disposition header arguments. This is a long shot, but give this a try:

1. replace:

    
   header("Content-type: image/jpg"); 
   

with:

header("Content-Type: application/octet-stream"); 

2. change from:

 
header("Content-Disposition: attachment; filename=\"".$file_name."\""); 

to:

header("Content-Disposition: attachment; filename=\"$file_name\""); 

Try that in combination with your other header() lines. If it still doesn't work, comment out your other header() lines and just use the two that I've suggested. Of course, modify the remainder of your code accordingingly if you comment any dependencies.

If you choose to keep the Content-Type header, I'm very certain you had it right with image/jpeg vice image/jpg

Post back your results. Also, specify what operating system the MySQL db is on and whether you have root level priviliges to both the O.S. and the db.

[onion2k]

Originally posted by nemonoman
First off: DON'T STORE IMAGES IN A DATABASE. THAT'S NUTS!!
Store the image as a file, and save a reference to the file in the database.

There are some very good reasons for storing images in a database, as well as very bad ones.

Good reasons:

1. Considerably easier to backup/move.
   1b. Much easier to replicate across load balancing servers.
2. Searchable (tricky with jpg data, fairly easy with bmp).
3. Well organised with easily attachable meta data.
4. Not relying on the server filesystem.

Bad reasons:

1. If it crashes you lose the lot (not a worry if you're sensible).
2. Database files can get massive very quickly.

There is no hard and fast rule that states No Images In Databases. In depends entirely on the environment and the application.

[Weedpacket]

There's another bad reason you missed:
3: it's a lot slower.

Oh, and good reason 4: doesn't count - the database files have to be stored somewhere. And if that's still a good reason, then here's a bad reason:
4: You are relying on the database implementation.

[onion2k]

Originally posted by Weedpacket
There's another bad reason you missed:
3: it's a lot slower.

Actually that depends on your application. If you're doing anything to the images on the fly then storing them in files would be slower.

[Weedpacket]

Originally posted by onion2k
If you're doing anything to the images on the fly then storing them in files would be slower.

Reckon? All I see is an extra step (extract the data from the database's files stored in the filesystem). Are you talking about saving them back again?

[kelly649]

Thanks, Rachel2004.

I made the changes you suggested, but I still ran in to trouble. If I remove header("Content-type: image/jpg"), the initial 'file download' box pops up with the correct File Name (061004_logo.jpg) and File Type (JPEG). However, when I click the 'Save' option, the 'Save As' dialogue box opens with the File Name as '061004_log' and the File Type as 'HTML Document'. If I go ahead and click 'save', it does save as an HTML page and opens to gibberish.

If I add the "Content-type: image/jpg" header back in (must be listed under "Content-type: application/octet-stream"), the 'Save As' box opens with the File Name as '061004_log' (still no .jpg in the file name) and the File Type as 'JPEG Image'. If I click save, the image does save as a jpg and I can open and view it in Photoshop. Unfortunately, it still won't show in the preview pane and if I open it with IE (from my hard-drive) it displays as an empty box (like a broken image). Also, if I upload it and try to view it on IE from the server, it displays as gibberish.

I tried commenting out the other headers you mentioned and then tried all different combinations of the headers, but to no avail. I don't think it's the variables, since I've printed them out for verification and even tried hard-coding then in the headers. I also tried adding .jpg to the File Name in the 'Save as' box, but that didn't do anything either.

To answer your other questions, the mysql database is on a linux os that's also running apache server. And yes, I do have root level privileges to both the os and db.

I don't know where else to turn for information as I've posted this same question on other messageboards, but no one seems to have any suggestions. Thanks for trying!!

[rachel2004]

I'm going to try to reproduce your setup when I have time in my lab, but I probably won't be able to get back to you until tomorrow with results.

May I ask what are you using as references in the way of books or URLs? There's a good chance I may have those books as well and if I can look at the same page or URL, perhaps, I will see something that maybe you are intererpreting slightly different. It happens, not to insult you, and it happens to me when I stare at something too long.

[rachel2004]

One more thing:

Can you post your code as it stands now (after your latest modifications) and I will start with that?

[kelly649]

Thanks!!! Neither of my PHP books really mention how to set up links which download images, so I've been looking for online info. Most of the code I've gotten has been from the fpassthru function page at PHP.net: http://www.php.net/manual/en/function.fpassthru.php

Here's my current code:

$design_id = $_GET['design_id'];
$connect_ID = mysql_connect("localhost", "kellym", "merta35") or die("Could not connect");
mysql_select_db("ily_digitizing");

$sql = "SELECT image_ref FROM Designs WHERE design_id=$design_id";
$result = @($sql, $connect_ID);
$image_ref = @mysql_result($result, 0, "image_ref"); // relative URL of image
$file_name = basename($image_ref);

// translate file name properly for Internet Explorer.
if (strstr($_SERVER['HTTP_USER_AGENT'], "MSIE")){
$file_name = preg_replace('/./', '%2e', $file_name, substr_count($file_name, '.') - 1);
}

// make sure the file exists before sending headers
if(!$fdl=@fopen($image_ref,'r')){
die("Cannot Open File!");
} else {
header("Cache-Control: ");// leave blank to avoid IE errors
header("Pragma: ");// leave blank to avoid IE errors
header("Content-type: application/octet-stream");
header("Content-type: image/jpg");
header("Content-Disposition: attachment; filename=\"$file_name\"");
header("Content-length:".(string)(filesize($image_ref)));

fpassthru($fdl);
}

[nemonoman]

Are you using a Mac and IE for the Mac?????????

IE for the Mac Officially Sucks with downloads and causes many problems.

Before you go off the deep end, prove where the problem is:
Try using your existing code with Netscape, Safari or Firefox for the Mac. If it works (as I suppose it will), then you have isolated the problem to the browser. There are nasty little workarounds if that's the problem.

[kelly649]

I'm using IE on a PC. I tried viewing the downloaded image in Netscape and received the following error:

The image “file:///C:/Documents%20and%20Settings/default/Desktop/060904_27_car.jpg” cannot be displayed, because it contains errors.

It appeared as a broken image in Opera.

I thought maybe there was something wrong with the uploaded image, but when I view the image on the server with IE it appears fine. So, the errors must be occurring somewhere during the download.

[drew010]

in your previous code you have 2 header declarations for content type. only one of them will work. image/jpg is not actually a recognized mime type, image/jpeg is the actual mime type.

after all your sql code, try this, it worked for me in both ie and mozilla

header("Cache-Control: ");// leave blank to avoid IE errors
header("Pragma: ");// leave blank to avoid IE errors
header("Content-type: image/jpeg");
header("Content-Disposition: attachment; filename=\"$file_name\"");
header("Content-length:".(string)(filesize($image_ref)));

echo file_get_contents($image_ref);

[nemonoman]

You wrote:

but when I view the image on the server with IE it appears fine. So, the errors must be occurring somewhere during the download

OK:
Are you SURE that the data inserted in the database == the data of the image??

Are you SURE that the data downloaded <> the data on the database?

You might want to post your file to database blob loading code.

[kelly649]

I'm sorry I'm not being more helpful with my comments. I'm fairly new at this and so don't really know how to debug.

The data stored in the server file system is identical to the image/data that was uploaded. I can view it with IE and the file size is exactly the same (3519 bytes). How can I tell if the downloaded data is the same as that on the server? When I download it and save to my desktop the size of the file grows slightly (3899 bytes). Should this be the case? Netscape still says the file has errors, but then why can Photoshop read it?

[nemonoman]

The data stored in the server file system is identical to the image/data that was uploaded.

Have you done a byte for byte compare?

I can view it with IE

?? How do you do this exactly?

and the file size is exactly the same (3519 bytes)

?? what exactly are you comparing here that is 'exactly the same'?

I see that you are making a file with this command:

if(!$fdl=@fopen($image_ref,'r')){

You need to open this as a BINARY type file:

if(!$fdl=@fopen($image_ref,'rb')){ // NOTE the 'b'

from the manual [man]fopen[/man]
Unix based systems use \n as the line ending character, Windows based systems use \r\n as the line ending characters and Macintosh based systems use \r as the the line ending character.

If you use the wrong line ending characters when writing your files, you might find that other applications that open those files will "look funny".

Windows offers a text-mode translation flag ('t') which will transparently translate \n to \r\n when working with the file. In contrast, you can also use 'b' to force binary mode, which will not translate your data. To use these flags, specify either 'b' or 't' as the last character of the mode parameter.

[rachel2004]

To kelly649:

I just checked this thread again, and saw that your problem had already been resolved by drew010 since last we spoke. This is just some follow-up info:

Using:

header("Content-type: application/octet-stream");

instead of:

header("Content-Type: image/jpeg");

may or may not have some impact on forcing a download via the browser, which is what you had described was your end goal. application/octet-stream is a generic content type and may fool a browser into downloading the content. If you choose to use it, be sure to follow it up with:

header("Content-Disposition: attachment; filename=\"$file_name\"");

to suggest to the browser a filename and extension for the download, naturally.

I did a few run throughs with appliation/octet-stream. "d" indicates the browser downloaded and "b" indicates the content was displayed in browser. All downloaded file sizes were the same number of bytes as the source binary (a jpeg):

Linux
Konqueror 3.0.0-12: d
Mozilla 0.9.9: d
Netscape 7.1: d
Netscape 4.79: d

Win2k
IE 6: d
Netscape 7.1: d

Mac OS X 10.3.4
Safari 1.2.2: b
IE for Mac 5.2.3: b
Netscape 7.1: d

I tried everything with:

fopen($image_ref,'r')

and then with:

fopen($image_ref,'rb')

and it did not affect the behavior of the browsers to display gibberish, but I only tested on a single Win2k box with 2 browsers... PHP manual best practices says to use 'rb' so do it (credit to nemonoman for pointing it out first). However, that wasn't the root cause of your problems as I suspected and drew010 confirmed by testing.

I would like to see the code that you use for uploading the file to your MySQL db as I'm interested in what you did to insert into a BLOB. Please post after you read this.

[deb248211]

I know that this was solved, but I spent ages trying to find a solution to this problem, so hopefully it will help others too.

A simple way to solve the Mac IEimage display problem - apart from deleting IE completely, and in contrast to others who say it is bad to store images in a database, this seemed to work.

Set the content type header as image/.jpg and use only jpegs, rather than using image/$type or image/jpg.

Mac IE then picks it up, and so does IE on other less pleasant platforms.