to blob or not to blob

arelgee

I am about to have to decide whether to insert a lot of images in a file and store just the filename in a database or store the images as blobs.
I have read many that say NEVER to store the images, just the references.
The problem I see is in the php code. If I store the images as blobs. I can simply delete the row and be done with it.
But if I store the files, I will have to write another php script to delete the files, then either call it every time I make a deletion or set it up to run once or twice a week.
To complicate matters, I am planning on selling advertising for cars and real estate. Most that I have talked to about this want the option of having multiple photos, say of the front and side of a car and one of the interior, as well as a thumbnail.
Server space is cheap, but not free. I estimate at least 3/4 of my requirements will be for images.
I guess my question is this, which way is cheaper and which way is easier?
Any ideas will be appreciated.
arelgee

theeper

Which way is cheaper ? I don't know.

It seems to me that it is easier to load the images to the disk and just put the URL in the DB.

The reason is, when you load your images as BLOB, you need to have a script that builds the image from the BLOB in addition to all your other scripts. In other words, for linking an image, you need a link on your page like this ...

Then you need imagescript.php to look something like this ...

$result=mysql_query( "SELECT binary_data FROM images WHERE id=$id");
if ($row =mysql_fetch_array($result))
{
header("Content-type: image/pjpeg");
echo $row["binary_data"];
}

This script would need to be called for every image that was to be displayed from the DB.

On the other hand, if you just store the image URL in the DB, you don't need to have the extra script. As far as the delete goes, adding a line to your code for unlink()'ing the image is simple.

Also, storing your images in the DB is probably less reliable that storing them on the disk. If your DB crashes, and for some reason is irrepairable, you have just lost all your images. Likewise, a DB with a large amount of images could cause a performance hit to your machine.

arelgee

Thanx for the input. You pointed out a big flaw in my plan.
I was planning to have, for each item, an ItemID, a brief description(including price), a thumbnail, then have the full description, full image, a price field that I could query to check on how much merchandise I have to sell etc.
I was going to have one table with just 3 columns per row, ItemId, the thumbnail and brief description. if a user was interested, he would click and be shown a different page with the full image and full description, shipping terms etc.
For some reason , I thought I would hard code the first table, and only build the page with the full info with php.
That would be a mistake, because I would have to change the small table every time I made a sale.

My db would look like this:

create table Inventory( ItemId, int primary key, auto_increment,
thumbnail varchar(?),
briefdescrpt varchar(255) or maybe blob
largeimage, varchar(?),
fulldescript blob,
price currency,
ShipTerms varchar(25),
etc,
);
my first table would be populated by
select ItemID, thumbnail, briefdescript from Inventory

and my second table (full page) would be populated by:

select ItemId, largeimage, fulldescript, price, ShipTerms, etc.

Oh well, back to the drawing board.
arelgee

theeper

Use TEXT for your briefdescrpt field instead of BLOB or VARCHAR.

arelgee

I guess I need to spend some time on MYSQL. I had not heard of TEXT before.
One reason I wanted to make Price a separate field (besides getting a running total when needed) is if I happened to want to run a sale. In Interbase, I would run the following:
Update Inventory set Price = Price * .8 (20% off).
Does MYSQL support this?
arelgee