Saving Files To Database

[SalientAnimal]

Hi All,

 

I have two questions, and I am looking for some guidance. I have a form where I would like users to add an attachment.

 

The question I have is:

1. What would you recommend (Adding the attachments to the MySQL database / to a file system) and what is the benefits of each?

2. If uploading the files to the database how do I accept multiple file formats i.e. image, wav, etc.

 

Thanks.

[maxxd]

Upload the files to the server (you can check file type in a couple different ways for safety), then store the link to the uploaded file in the database. http://www.php.net/manual/en/features.file-upload.php should get you started on the upload process if you're having trouble with that part - I believe there's a decent explanation of file type checking in there.

[DavidAM]

1. Files belong in the Filesystem. Data belongs in the Database.

 

Store the files in the filesystem, and store the path in the database.

 

 

2. Databases are designed to store, retrieve and manipulate data; and they are very good at doing that. While most (all?) databases store "BLOB" fields, they are not designed to be query-able. I mean, how many times are you going to want to find all files that contain the hex value 0xDEADBEEF, and how efficient is that query going to be?

 

Storing files in the database does give you a single repository of all of your "data". So backups and transfers are simpler processes. However, your database backups will be huge with more potential for problems. If you dump to a set of INSERT statements, and then modify the dump file, you run the risk of damaging the file contents. Since binary files are "type" specific, a change to a single byte of an image file data, can render the entire "file" (image) invalid and unviewable.

 

The recommended approach is to define a base path in your application specifying where these files are stored. Then include a sub-directory path in the table relative to the base path. This will permit you to move the data and files to a different server, and you only have to change the application's base path.

 

If you are in the habit of using SELECT *, then your queries will return every column in the table. If you have files stored in the database, you will be retrieving the entire file (1K, 100K, 5MB?) with every query. If you store the path, then you get the path (VARCHAR(255)?). Note: Using SELECT * is not on the list of "best practices", and I always advocate against; but if you use it, you must be aware of the issues.