To BLOB or not to BLOB

I am writting a document storage app and i have started off with simply by putting a link to the file in a database table along with some keywords and other bits and bobs.

Then i saw the storing BLOB data in a database article.

Which method is best?

I can see advantages of the BLOB method. You will never lose file links, when you delete the record you clear the file at the same time.

Are there any disadvantages to storing large files like this?
What about database corruption?
If the DB get corrupted I guess i'll lose my file.

Anyone got any comments on this subject?

BLOBs tend to be slower to access, I hear. I usually store files in the file system with references to them in the database.

How do we do add a link to the mysql database?

Say for example, I would like to upload a gif file for product catalog. Once file uploaded, how do I add the link and be able to call when the viewer want to view it.?

Presumably, in order to allow the file to be uploaded, you know where it's being uploaded. You'd just stick that path in your database. If it's a path within your web tree, then you just output the path and filename in order to allow the user to view it. If the path is outside your web tree, you'll have to do some other stuff like printing headers for the file, etc.

Perhaps how you would execute the mysql insertion script?

Show me an example, would be really helpful! Thanks. Best wishes!

In order to show you a meaningful example, I'd need to know something about your database structure, where you're putting files, etc.

Okay,

basically I wish to display out my images as part of my product catalog.. Well, a picture paints a 1000 words.

I have the following database setup.

CREATE TABLE product_table (
Product_ID int(11) NOT NULL auto_increment,
name varchar(30) NOT NULL default '',
price float(4,2) NOT NULL default '0.00',
Cat_Name varchar(30) NOT NULL default '',
Cat_ID int(11) NOT NULL default '0',
description varchar(255) NOT NULL default '',
image blob NOT NULL,
PRIMARY KEY (Product_ID),
UNIQUE KEY Prod_Name (name)
) TYPE=MyISAM COMMENT='eDoggies'' Product Table';

The bold part is what I need to change to a link. I think putting the image onto the database is plain irresponsible. Linkages make more sense but I really not sure how to get it done.

I am developing my shopping cart over a win2k machine. And not really sure what do you mean by pat..pls advise. Thanks

Ok, start by changing the type of the image field to char or varchar. Into this field you'll insert the name given to the uploaded image. If you're storing all of the images in the same folder, then in your code, you'd print out the folder name and then the image name selected from the database. If they're stored in different folders, then you'll probably want to store the folder name and the image name in the image field in your database. When printing these out, just do a regular <img> tag with the appropriate database value filled in for the src attribute. A path is something like C:\Web\images.

So back to BLOBS
If linking is faster then what are the advantages in BLOBing

BTW BrokenDreams if you would like to see the functions i am working on then i'll post them up when they are working. It basically allows you to upload a file to a server and create a database record partly based on user input and partly based on information gathered on the file

I don't really know of any great advantages for blobs. My guess is that providing the ability to store binary data seemed appropriate. I've never seen the feature actually used, though.

This thread went off the question a little... which is the BEST method... I've looked at some large sites (newspapers, Yahoo etc and they all seem to link direct to an image (image.jpg etc)

Sooooooooooo, does this suggest that the BLOB isn't really the best method? I'm wanting to administer some of the images on my site directly through a browser, what should I do?

function display_dvds($dvd_array)
{
//display all dvd in the array passed in
if (!is_array($dvd_array))
{
echo "<br>No DVD's currently available in this category<br>";
}
else
{
//create table
echo "<table width = \"100%\" border = 0>";

//create a table row for each dvd
foreach ($dvd_array as $row)
{
$url = "show_dvd.php?barcode=".($row["barcode"]);
echo "<tr><td>";
if (@file_exists("images/".$row["barcode"].".jpg"))
{
$title = "<img src=\"images/".($row["barcode"]).".jpg\" border=0>";
do_html_url($url, $title);
}
else
{
echo "&nbsp;";
}
echo "</td><td>";
$title = $row["title"]." by ".$row["director"];
do_html_url($url, $title);
echo "</td></tr>";
}
echo "</table>";
}
echo "<hr>";
}

The function is passed an array ("$dvd_array"), d'uh. Containing all the details of the DVD (no images) and then the barcode is used to create an image tag. Hopefully this is some help.

Ernie