Blobbin images

[Danzig]

Hello everybody,

I have a question regarding storing images in the DB. Currently I'm using MYSQL. I read an article about blobbing data in the database. Now my question to you. For storing images, should they also be "blobbed", or is it recommanded to just store the link to these images on the server, and thus build the HTML like that...

Maybe a stupid question, my apologies ... But I'm wondering about this as I always used the latter option and it worked okay for me. But what is the best way to go? The fastest? the most stable?

Thank you in advance.

Danzig

[keith]

i store files with blob. i like it, it saves creating all sorts of images directories, and when you want to run a backup [changing hosts, etc...] all you do is save the database with phpmybackup, rather than download 500+ images, like my brother and his caricature site use.

now whether it's faster or slower, i doubt the difference is even noticeable.

[Maarten Martens]

Thanks, sounds interesting!!
Any other opinions?

Cheers,
Maarten

[CareBear]

Unless there's a specific need for you to put files into a dabatase (don't see why you'd need to at all with images) then don't.
As far as switching hosts is concerned, you should already have a copy of all the static content of your site (HTML, downloadable files, images, scripts, etc). The only part that you'd need to regularly backup is the database.
Another issue is that caching of dynamic pages doesn't usually work too well so expect an increase in data transfer and slowdown on the clientside as far as loadtime goes.
Depending on your host database connections are also a shared and/or limited commodity.
Lastly, nothing beats the speed at which an HTTP server will serve static content, even if - assume best case - it's only a few percentage points slower you have to consider dozens of concurrent accesses and the fact that you're sharing the server with another few dozen people.

[Danzig]

Thanks for the help. :)

[Canuckkev]

Keep in mind linking to images is a little harder to do if you store them in a DB...on the flipside, it's very easy to hotlink protect them.

[keith]

in response to carebear: yes, you should always keep a backup, which i do. but what happens when you need to make a change in hosts or something similar?

like i'd mentioned earlier, in my brother's case with his artwork site: like any typical caricature site, he posts caricatures he's drawn [athletes, actors, musicians, etc], his artwork from six-flags where he works, plus a gallery where caricature artists from all over the world can submit caricatures of him from photos he makes available on the site. now if he ever needs to change hosts, he can either re-upload hundreds of images to a single cluttered "/images/" directory [remember, php runs in safe mode on most hosts, so he can't create multiple writable directories], or he can upload one 5-or-so meg gzipped tarball and restore it from there with phpmybackup.

there are always good and bad things about each method. yes, http is of course fastest... but on a human reaction level is it really all that noticeable? it all depends on need and personal preference... if you've only got like 50 images, give or take, then there's really no point in putting them in a database.

and hotlinking protection is another good use for storing them in a database that i hadn't even though of till canuckkev mentioned it, with no need for .htaccess protection.

[Danzig]

Well its several thousand images that will be there.

Never thought about the automatic hotlinking protection that I'd get if I stored it in the db either. Thanks for that info canuckkev.

Will it slow down the query speed to the database somehow, with that many MBs of images in it?

Thanks,

Danzig

[keith]

the hotlink protection wouldn't be automatic, you'd have to add it into the script... like compare the http_host with the http_referer, like so:


<?php

$image = $_GET['image'];

$host = getenv("HTTP_HOST");
$refer = parse_url(getenv("HTTP_REFERER"));

// Check that the domain in the referring url is identical to your domain name:
if ($host == $refer[host]) {

@mysql_connect($mysqlhost,$mysqluser,$mysqlpassword) or die("Unable to connect to database");
@mysql_select_db("$mysqldb") or die("Unable to select database $mysqldb");

$query = "SELECT * FROM images WHERE id = ".$image."";
$result = mysql_query($query);

while ($rows = mysql_fetch_array($result)) {
$id = $rows['id'];
$file = $rows['file'];
$filename = $rows['filename'];
$filesize = $rows['filesize'];
$filetype = $rows['filetype'];

header("Last-Modified: " . gmdate("D, d M Y H:i:s",mktime (0,0,0,1,1,2000)) . " GMT");
header("Expires: Mon, 26 Jul 2040 05:00:00 GMT");
header("Cache-Control: max-age=10000000, s-maxage=1000000, proxy-revalidate, must-revalidate");

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

echo $file;
}


mysql_close();
exit;

}
else {
echo "Shame on you!";
exit;
}

?>that's a stripped down version of what i use [plus i added the hotlink protection]. just assign each file an auto-incrementing id and call the file by it's id images.php?image=1

i don't think you can slow down a mysql database if you use the "where" command, and the three headers "last-modified", "expired", and "cache-control" force the browser to cache the image to save on transfer.

[Danzig]

Thanks Keith! :)

[keith]

no problem, hopefully you're able to understand that code. if you need the code to add and remove images from the database, let me know.