Uploading files into a MySQL database using PHP

[Atli]

Hi.

If you added an additional field named TYPE, then you would just have to add a where clause to the SQL query specifying the values you want listed.
[code=sql]SELECT ... FROM `FileStorage` WHERE `TYPE` = 'FYR'[/code]

[xaralee]

Download file

Hi Atli. I tried pasting your codings to my final project and it worked just fine. However, how can i download the file and open them successfully especially with .doc files? Sorry i need to know the step by step procedures. I tried following the phase 4 but still couldn't understand. Thanks in advance.

[Atli]

Hi xaralee.

The code in phase #4 should successfully send *any* type of file back to the browser.
This is obviously dependent on whether or not the browser sent the correct mime-type and extension when you uploaded the file, but I doubt any decent browser would fail to do that with a Word document.

How the browser actually handles the downloaded file is not something this code can control. If you want the browser to open the .doc file in Word, then you will have to tell your browser to do that.

Most browsers give you a choice when downloading a file, whether to open it, and which application to use, or whether to download it.

[xaralee]

Hi xaralee.

The code in phase #4 should successfully send *any* type of file back to the browser.
This is obviously dependent on whether or not the browser sent the correct mime-type and extension when you uploaded the file, but I doubt any decent browser would fail to do that with a Word document.

How the browser actually handles the downloaded file is not something this code can control. If you want the browser to open the .doc file in Word, then you will have to tell your browser to do that.

Most browsers give you a choice when downloading a file, whether to open it, and which application to use, or whether to download it.

Hi Atli. I solved the problem already. Thank you for your advice. (:

[Frinavale]

Atli this is an awesome article :)
You may want to include a links to the resources that you're using (like the mysqli extension) though :)

[Atli]

Atli this is an awesome article :)
You may want to include a links to the resources that you're using (like the mysqli extension) though :)

Thanks ;-)
Good idea. I've added links to the "Before you start" part, to begin with.
Might be worth adding a separate reference part tho.

[msei]

Hi Atli,

I did everything following the steps provide to do the upload and download file and it was working fine, but for some reason I tried to download a file it keep giving me the following error message "Adobe Reader could not open file.pdf' because it is either not a support file type or because the file has been damaged." I did download the Adobe Reader 9.0 and still giving me the same error message as I open the file. But for some reason if I open the file without download from the fle list, it does open fine. Any idea what am I doing wrong? Any help will be appreciated.

Thank you in advance.

Xiou

[Atli]

Hey Xiou.

What browser are you using to test this?
Which versions of PHP, MySQL, and what OS is your server running?

I just tested this on my PC and there were no problems uploading and reading PDF files in any of the major browsers. I'm using Adobe Reader 9.1.

My server specs:
Win7 (x64), Apache 2.2, PHP 5.3, MySQL 5.1

It sounds like your file is being corrupted somewhere on the way tho.
Maybe some sort of charset conversion problem? I can't really say.

[xiou]

I need a help in how to upload 2 files instead 1? thank you in advance. Any help will be appreciated

[Atli]

Hey.

To upload more than one file, you add more <input> elements, each with a different name.
[code=html]
<input type="file" name="uploaded_ file_1" /><br />
<input type="file" name="uploaded_ file_2" /><br />
<input type="file" name="uploaded_ file_N" /><br />
[/code]
And then you grab each file from the $_FILES array using it's name:
[code=php]
$file1 = $_FILES["uploaded_file_ 1"];
$file2 = $_FILES["uploaded_file_ 2"];
$fileN = $_FILES["uploaded_file_ N"];
[/code]
And then run each of them through the code that inserts it into the database.
You can make this less repetitive by putting the files into an array and looping through it.

There is also a way to upload them as a array, by using the same name for all the <input> boxes, adding brackets to the end [], but that is not necessary for a static form. (More useful for forms that expand using client-side code.)

[kovik]

The server has complete control over the access of files in its filesystem. The only time that using the database provides "extra security" is if you have no control over the server (i.e. residing on a shared server) or your security layer is based solely on a third-party controller (i.e. a server-side user system).

However, in those cases, you could simply store a pointer to the file (i.e. the filename) in the database, block the files from regular access using .htaccess, and make the files only accessible via another script.

All in all, there's nothing that a BLOB can do that the file system and a file pointer cannot. I feel that if you are going to advocate a method that goes against the accepted methods of development that you should provide more justification for doing so, or at least a set of pros and cons.

[Atli]

Hey kovik.

The only time that using the database provides "extra security" is if you have no control over the server...

I don't know who you are quoting there, but is sure isn't me.

I never said this provided "extra security". I said it gave you more control, which it does. It makes you (or rather; MySQL) responsible for handling and storing the file data, instead of passing that responsibility on to the OS.

And for the record, if you have no control over the server, a database won't be any more secure than the file-system. They will both be just about equally insecure.

The server has complete control over the access of files in its filesystem.

Sure, but different types of servers have different types of control.
How OSs handle files varies somewhat. MySQL, on the other hand, handles the same across all the OSs (or at least handles predictably across OSs.). In some situations, not having to worry about the various OS/file-system quirks could be of great help.

All in all, there's nothing that a BLOB can do that the file system and a file pointer cannot.

And I never suggested otherwise. But even tho the same things can be achieved using both systems, how they are achieved is not the same, and sometimes one method may fit your needs better than the other.

For example; backing up the files when they are inside MySQL is more convinient then when they are on the file-system. You can just dump all the data, files included, into a single file, whereas if the files or on the file-system, the databases and the files have to be backed up separately.

I feel that if you are going to advocate a method that goes against the accepted methods of development that you should provide more justification for doing so...

Allow me to quote the very first paragraph of the article:

You may be asking yourself: "Why put files inside my database? Why not just put them on the file-system?".
In most cases, that is exactly what you should do. It's simple, effective, and requires very little effort on your part.

I am not advocating the use of this method. I'm only explaining it. Whether or not you want to use it is completely up to you.

And, by the way, there are no "accepted methods of development" written in stone anywhere.
Which method you should use depends on what you want to achieve and it just so happens that the file-system method fits better in most cases. But it is by no means superior in all situations.

[kovik]

I base what is "accepted" by what I typically hear respected members of web development communities like PHPDN and SitePoint say. So no, they are not set in stone, but they do exist. The file server handles files. The database handles data. That's pretty must the gist of it.

And I saw your disclaimer, but it doesn't say anything about the weaknesses of this method or the strengths of the most common method of file pointers in the database and actual files residing on the server. My main concern is that you, the author, have the "Moderator" tag associated with your name, which automatically gives you a large amount of credibility, meaning that newcomers may blindly follow this advice without knowing the implications. When pushing uncommon methodology, one should explain why they feel it is necessary. A broad statement of "special circumstances" and "a little more control" isn't exactly acceptable.

Also, a few other issues I have is the coding style of the example code and the security aspect of this method. The most used standards in open source development for PHP would have to the Zend PHP coding standards, which decrease the LOC, add more whitespace, and increase readability (though the last part is purely opinion). Conforming to the standards makes code easier to read as long as we all agree to conform. And, as for the security aspect, I'm not so sure about the integrity of the MIME type that is detected in the $_FILES array. I've had times when, for example, the $_FILES array determined that a file was an image/jpeg, but getimagesize() determined the MIME type to be image/pjpeg (special Photoshop image type).

[Atli]

Thanks for the pointers.
I do agree with what you are saying for the most part. I should of course have explained the downsides better, if only to be thorough. I wrote this article more than two years ago, and I've barely edited it since then. Maybe it's about time to do that :)

I don't necessarily agree that the code examples should be in Zend format, though. It should of course be properly formatted, but whether it is in Zend format, or even the standard .Net format, doesn't really matter, as long as it is readable.
And I, personally, don't like some of the rules the Zend standard defines, so I would be inclined not to include them in my examples.

And, as for the security aspect, I'm not so sure about the integrity of the MIME type that is detected in the $_FILES array. I've had times when, for example, the $_FILES array determined that a file was an image/jpeg, but getimagesize() determined the MIME type to be image/pjpeg (special Photoshop image type).

True, but this topic could be an article in itself, so I'm reluctant to clutter the code - which is only mean to demonstrate the file upload process - with stuff to fix this. Since there aren't any limitations on what you can upload in the examples anyways, verifying the mime type seems a bit pointless.
Should add a warning about this though.


But, anyhow. I've updated the article. Added a bit of detail to the intro and re-formatted the examples a little. Thanks for the input! Is very helpful :-)

[kovik]

Very nice. Those changes added a lot more professionalism to the article. ^.^

My only problem (is that the right word?) now is that you don't really warn the user about the usage of the "improved" mysqli extension usage. Every server that runs PHP and MySQL will undoubtedly have the mysql extension dll loaded, but the mysqli extension may be overlooked. I see that you give a link, but it's not exactly obvious. As a reader, I skipped over the introductory links, and I can picture other readers doing the same.

An improvement on this article could be embedding the reference links inside of the text. References to the mysqli extension information, maybe to W3C's definition of the "multipart/form-data" encoding type, etc.

Sorry if I'm being too demanding. :P