Things to remember when saving files into the database

In this quick post I’m going to tell you about some of the things that you need to remember when saving files into a database to make it work flawlessly.

Saving files into the database is actually a bad idea, have a quick look at the answers to this question in Stackoverflow:

Storing images in DB – Yea or Nay?

If you’re still on it then continue reading.

 

Configure

There are things that you need to configure in order to make uploading files to database flawless. First is the my.ini file or the mysql configuration. Yes, this post is actually mysql-centric so if you’re using another database then I suggest that you Google some more you’ll eventually find what you’re looking for.
If you’re on XAMPP my.ini is located at:

C:\xampp\mysql\bin

And if you’re on WAMP:

D:\wamp\bin\mysql\mysql5.5.8

All you have to do is to look for this line:

max_allowed_packet = 15M

Yeah, I don’t know if it’s 15M by default but what it means is 15Mb. Be sure to change that  to whatever max file size you wish to upload on your database.

Next open up the php.ini file, if you’re on XAMPP its in:

C:\xampp\php

And if you’re on WAMP:

D:\wamp\bin\php\php5.3.5

And there’s actually a second php.ini file which is located under:

D:\wamp\bin\apache\Apache2.2.17\bin

It’s only in WAMP, the one that is in the Apache folder is the one used for the php that is being run on the browser. And the php.ini in the php folder is for the php that runs on the command-line. Just edit both of them so that you won’t need to worry about anything.

Okay so here’s the line that you need to edit. It’s not actually 50M by default. Just modify it to whatever value you like. Just be sure not to forget the big M after the numeric value.

post_max_size = 50M

Next you also have to change the maximum execution time. It’s 30 seconds in my file but I don’t know if it’s the default, be sure to change it if you think the file that your users are going to upload will take more than 30 seconds to complete.

max_execution_time = 30 

You might also want to consider changing the value for the upload_max_filesize if you’re also planning to upload the files on the filesystem as a form of backup. This is 2Mb by default.

upload_max_filesize = 2M

 

Field

For the database you might know it already that’s why I decided to place it on the latter part of this post. If you’re storing files into the database you should use the BLOB data type. If you will allow your users to upload videos and other media files use LONGBLOB it’s the safest possible bet.

 

Conclusion

I guess that’s it for this quick post. Remember that storing files into the database is not actually a good practice, awesome people at Stackoverflow are discouraging it since you’ll lose a lot of performance just by saving and accessing those files from the database.  The file system is actually the best place to be storing files. Another bad thing about saving files into the database is that your database will rapidly grow in size which means that it will consume much more space on your hard drive and it will also take some time just to backup. Lastly, you will have to set the header types on every page where you plan to output your files. The only advantage of storing files into the database is that it’s more secure and it’s really portable. Portability is really useful in cases wherein you need to migrate your system from one operating system to another since you don’t need to take care of the file paths when migrating.  In the end it’s still up to you to decide which path you want to go, is it the file system path or the database path.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s