GD image, save to MySql.

[httpwebwitch]

I've got an image that was created using imagecreatetruecolor() from the GD library [ca.php.net].

What I end up with is an image object. Usually, I'd output it to the stream, or save it as a file.

Today, I want to put that image in my database. And I'd like it to be saved in JPG format.

The awesome function imagejpeg() [ca.php.net] creates a JPG. But it doesn't expose the image in a way I can pop into a SQL blob.

Besides writing the file then reading it back again, is there a better way?

[httpwebwitch]

oh, maybe I need to do something with a "buffer"... output the image but don't really output it, whatsay?

[eelixduppy]

Buffer sounds about right. Have you tried it yet?

[httpwebwitch]

ob_start();
imagejpeg($thumbimage,null,100);
$imageblob = ob_get_contents();
ob_clean();

darn, that didn't work.

[httpwebwitch]

Eureka!

I've got it.

to get the image ready for saving:

ob_start();
imagejpeg($thumbimage,null,100);
$imageblob = ob_get_contents();
ob_clean();

to put it in your db:

mysql_query("INSERT INTO photos (image) 
VALUES (
'".mysql_real_escape_string($imageblob)."'
)") or die(mysql_error());

buffering did work after all. the final hoop was using mysql_real_escape_string() to escape the data for SQL. My database contains a blob of image data now!

[henry0]

Could you explain why you have chosen the blob route instead of the most effective storing data in DB way:
img-path-saved-to-my-img-dir

[httpwebwitch]

What I'm storing are thumbnail views of product images uploaded by users of a service. They're not my images - they're user images. These images aren't within my control, they're not static, users will be creating and deleting them all the time, and there may be kabillions of them.

When I want the image of a product, I can grab it from the db, and I don't have to care about its file type, path, filename, etc.
It's just a SQL query away:

SELECT photo FROM itemphotos WHERE itemid = $itemid

Using a RewriteRule in my .htaccess, I rewrite any requests for a product image:

RewriteRule ^itemphotos/([0-9]+).jpg itemphoto.php?itemid=$1 [QSA,L]

Anywhere in my application, I can ask for an image using its item id:

<img src='/itemphotos/123.jpg'/>

And I can append optional querystring variables to customize the request.

Then in my itemphoto.php, I grab the photo associated with item $itemid and echo it with a JPG header. Or if there is no photo in the db, I output a "no image" placeholder. Or if the item doesn't exist, I can send a 404 header. Or ... etc.

This keeps all the logic concerning the delivery of images in a PHP file, not in a cumbersome naming convention for files.

On many previous projects I've moved uploaded files into the file system and kept them indexed in the database, or else I had a naming convention for images which made them easy to access. For this project I thought I'd try putting thumbs in the database.

So far I like it a lot. It's easy for me to keep multiple images for each item, and multiple pre-processed sizes (thumb, large, small, icon) for each of those. Since images are stored in the same database as item data, it keeps application scripts (files in the source tree) separate from user data.

I also believe this strategy may be more scalable (or, more easily scaled) in case I ever have to do load balancing

[nick279]

I use a similar system, but have it like:-

/thumb/url
/image/url

However, I have a caching system which looks at a 'last modified' column in DB, and if it's newer than the cached image (in a tmp dir etc) will load that

I also (instead of [0-9]) have a 'url' field for lookups & helping with SEO