1

I'm storing small thumbs in a MySQL database. With PHP i create a thumbnail from an image and i simply store that into a BLOB column.

It seems that MySQL saves the binary image as a Base64 string in the database. The problem is that MySQL seems to store it as a application/octet-stream instead of jpg file.

I know that because i tested the Base64 string with this code:

<?php
$encoded_string = "....";
$imgdata = base64_decode($encoded_string);

$f = finfo_open();

$mime_type = finfo_buffer($f, $imgdata, FILEINFO_MIME_TYPE);

echo $mime_type;
?>

Is MySQL automatically converting my image to a Base64 string? If so, is there a way to make sure it saves it as a JPG?

I know saving thumbs to the filesystem is better, but unfortunately this is not the case and i am NOT looking for answers to save it to the file system. I want to know WHY it is saved as octed stream.

I'm using WideImage to create the thumb, like so:

        $thumb = \WideImage::load ( $filepath )
                            ->resize(117, 88)
                            ->crop ( 'center', 'center', 117, 88 )
                            ->asString('jpg');

The variable $thumb contains valid binary data. Because i can print it and it looks good, but also fwrite writes a good JPG.

I then save it to the database with an INSERT query. But when i then SELECT it again it gives me back an octed stream format.

Vivendi
  • 20,047
  • 25
  • 121
  • 196
  • 4
    Should be storing images on the file system and referencing them via MySQL. – Wayne Whitty Jul 19 '13 at 17:14
  • 2
    mysql does not use base64 for storing blobs. base64 is a very space-inefficient format, and costs you ~33% over the raw binary version. MySQL also couldn't care less about mime-types. If you say it's blob, it'll just go in a sequence of bits. doesn't matter if it's a jpg, a pdf, or your granny's apple pie recipe in text format. – Marc B Jul 19 '13 at 17:15
  • You can just echo the blob to get your image. i dont think mysql really cares about mime type. – SoWhat Jul 19 '13 at 17:18
  • show us the code that stores an image. most likely it's been converted to octet stream by processing functions – Jarek.D Jul 19 '13 at 17:24
  • @Jarek.D I'm using WideImage to create a thumb and write the binary data to a string which i save to the DB. When i `fwrite` the binary data to the disk then the image looks good. When i INSERT it to the database and then retrieve it again and then write it to the file system, it then is a `octet stream` type. I'm simply using a MySQL query to insert it. Nothing more... – Vivendi Jul 19 '13 at 17:52

2 Answers2

2

The best approach is to store the image on your filesystem and then reference its location via MySQL. Storing images in MySQL is not recommended. Why put an extra unnecessary strain on your database? All of the large websites (Facebook etc) store their images on a filesystem.

Wayne Whitty
  • 19,513
  • 7
  • 44
  • 66
  • 4
    All of the large websites probably have one or more dedicated servers. For the average Joe with a hosting plan, there could very well be a file count limit. For example I'm limited to 1000. Deduct the php files from that and it doesn't leave much. – Twifty Apr 20 '14 at 02:26
-1

There are many good reasons to store pictures and other documents in a database. For 1, to have certain control over where the picture or document is. When stored as individual files, it is easy for things to be moved, accidentally erased, or lost. Backing up is also facilitated. by keeping documents in a database. Access will be slower as the document must be retrieved from the database.

If stored as individual files and referenced in the database, the reference becomes invalid when the document is moved.

Ed Roberts
  • 1
  • 4