MySQL and Storing files

[organyx]

Hi people,
Now before you say that it is a bad idea.
I want to figure out a something before I ever trying to do such a thing for something serious.

I was arguing with my colleague about the matter. He's quite an old guy with a lot of php/MySQL experience. So he is on the side of storing the files and especially pictures in MySQL database.

I myself just store the reference to the file saving on database size and speed.

His main point is that the path to the file can get lost and in the result all you will have is a blank web-page on the output.

He gave me a piece of used up code for accomplishing this. Can be found at http://www.techcubetalk.com/2009/01/tutorial-on-how-to-store-images-in-mysql-blob-field/ and come other places. Same thing everywhere.

But this thing is as broken as it can be and the output is not an error or any other message but the half of the code on the main page. Something like this

0) { $fileName = $_FILES['imgfile']['name']; // image file name $tmpName = $_FILES['imgfile']['tmp_name']; 
// name of the temporary stored file name $fileSize = $_FILES['imgfile']['size']; // size of the uploaded file 
$fileType = $_FILES['imgfile']['type']; // file type $fp = fopen($tmpName, 'r'); // open a file handle of the 
temporary file $imgContent = addslashes(fread($fp, filesize($tmpName))); // read the temp file // $imgContent = 
fread($fp, filesize($tmpName)); // read the temp file fclose($fp); // close the file handle $query = "INSERT INTO 
img_up (`name`, `type`, `size`, `content` ) VALUES (’$fileName’, ‘$fileType’, ‘$fileSize’, ‘$imgContent’)"; 
mysql_query($query) or die('Error, query failed'); $imgid = mysql_insert_id(); // autoincrement id of the uploaded entry mysql_close($dbconn); echo "
Image successfully uploaded to database
"; echo "View Image"; }else die("You have not selected any image"); ?>

When he showed me one of the databases I've seen all the blobs filled with data. And it works all the time. But he refuses to show how and what he's done. Only a hint that he changed a few parts of that thing. How does he do that?
 

[Barand]

Impossible to tell from that code what is commented out and what isn't.

 

But as another old guy with a bit of PHP/SQL experience I would store the files in the file system (that's why they call it that) and store the reference to the file in the database.

[Psycho]

There are many pros and cons for storing files in the database vs. in the file system. You should do some research on what those are and then review what it is you are specifically trying to accomplish to determine which is appropriate for your needs. But, by and large, I store files in the file system. The number 1 reason would be performance. If the file is an image that will be displayed on a web page it would be much, much more efficient (and easier) to just pull the file path from the database and create an image tag. If the image content is stored in the DB you have to pull all that data and then construct an image resource to pass to the user.

 

But, if I had a requirement where the images contained sensitive data I might consider storing them in the DB with encryption.

[organyx]

Thank you for your answers.

 

It's just a small argument that managed to get half of the people in the office talking.

One old guy with our manager versus 10 other people.

Very interesting to watch.

[Psycho]

Microsoft even has a white paper on the subject that delves performance and functionality concerns regarding the two:

 

: http://research.microsoft.com/apps/pubs/?id=64525