Storing Binary Data in MySQL

TheOnly92 · 08-27-2009, 09:17 AM

I have been trying to storing binary datas like images into the MySQL database. The column type is set to LONGBLOB, but I'm unable to successfully store the file. It seems when I execute the query, it will somehow go corrupted. I have tried using mysql_real_escape_string, addslashes to escape and tried bin2hex to store but all failed. Any suggestion?

ioan1k · 08-27-2009, 01:18 PM

How exactly does the query become corrupted? What errors are given

You should be able to store the image using

php Code:

$binaryData = addslashes(fread(fopen($file, "r"), filesize($file)));

When you want to pull the image you will need to use header() to display the binary data.

php Code:

header("Content-type: $type");
header("Content-length: $size");
echo $data;

TheOnly92 · 08-27-2009, 01:38 PM

Well, after inserting the data into the database, when I can retrieve from the database and echo it, it appears corrupted. The png image file is unable to be displayed. I'm wondering if it has to do with encoding or something.

ioan1k · 08-27-2009, 01:42 PM

Are you send the header information before echoing the binary data?

You will need to send out the following headers.

php Code:

header('Content-type: image/png;');
header('Content-length: $iamgesize);

So you code will appear something like

php Code:

$data = mysql_query('SELECT data, filesize FROM images WHERE imageId = 1');
$data = mysql_fetch_assoc($data);
header('Content-type: image/png;');
header('Content-length: '.$data['filesize'].'');
echo $data['data'];

You cannot print out the data with other html content and must load the images through a php file, for example

html Code:

<div id="user">
    <img src="load-image.php?id=23" alt="Image" />
</div>

TheOnly92 · 08-28-2009, 02:12 AM

Yes, I definitely did, it tells me that the image cannot be displayed because it contains errors.

TheOnly92 · 08-29-2009, 10:20 AM

I wonder if there is anymore suggestions? I'm getting really stuck here.

TheOnly92 · 08-29-2009, 10:27 AM

Oops, I'm sorry, the problem was when the database record was retrieved, my code did an extra unescape which was unnecessary and ruined the data. Sorry for the trouble caused, all is going well now.