Insert image data into MySQL DB as a blob

PHP
1

Hi guys …

I have an image, which I am trying to read with PHP, then insert the data into a MySQL blob field. For some reason, I cannot get the blob data to get published to the DB field. I have the following code:


$handle = fopen("images/test.jpg", "r");
$img = fread($handle, filesize("images/test.jpg"));   
fclose($handle);
$img = base64_encode($img);
$sql = "INSERT INTO user_images SET image='".$img."', test_field='TEST DATA'";
if(!mysql_query($sql)){
    print "FAILED<br />".mysql_error();
}else{
    print "QUERY OK!";
}

“test_field” is just a field I use to check records are getting inserted from this particular query. “TEST DATA” gets inserted fine everytime, but the blob fields value remains “[BLOB]” (viewed in NaviCat).

Can somebody please advise as to where I am going wrong.

No errors show up when the query is being run.

Cheers,

Dave

2

If you have no errors, then for me it means that the query was successful.
I don’t work with mysql, but ypur query is incorrect in term of SQL92.
I know that mysql have a very tolerant syntax though, so it might work anyway, I’m just pointing this as a side note.
The correct insert query syntax would be


insert into user_images (image, test_field) values ('$img','TEST DATA');

Back to your question, try to run this query:


select length(image) from user_images where test_field='TEST DATA';

It should give you the size of the field in the db.
If it’s null, or 0, then there was a problem.

My guess would be that BLOB field being binary, navicat don’t even try to fetch anything.
Which makes sense in my opinion. A BLOB field could contain a field that would not be usable without a given program.
And beside, if your BLOB filed have 2 To of datas in it, it would surely crash your server.

3

Hi …

OK, I get a result of 37504 from running the query, so looks ike you are right about the NaviCat display issue. Thanks very much.

All working great now, thanks again.

4