Uploading files into a MySQL database using PHP

Atli · November 23rd, 2007, 05:21 AM

Introduction
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.

There are, however, some special circumstances that do require a little more complex tactics.
For example, when handling sensitive data, putting the files into a database gives you a little more control over how the data is handled and who has access to it.

This article shows a simple way of putting files into a MySQL table, using PHP.

Before you start
To get through this smoothly, you should be familiar with the following:

The battle plan
As with all programs, before we start writing we need to plan a little ahead. Just so we know what we are going to write before we write it.

Before we start on the program, we need to design the database. This is not a complex design, as we are not talking about creating some complex filing system. We only need a single table, containing a BLOB field for our file and various other fields to store information on our file, such as name, size, type.

Now then. The first phase of the program is getting the file from our users onto the server where our PHP can interact with it. This is the simplest part of the process, requiring only a basic HTML form.

The second phase involves reading the uploaded file, making sure it was uploaded successfully and adding it to the database. This is a similar process as the one used when uploading a file to the file-system, but using the MySQL functions rather than the file-system functions.

The third phase is to list all files that have been uploaded and saved on the database, with a link so it can be downloaded. The only problem here would be the fact that the file does not exists on the server, so how do we create a link to it? That is a problem handled by phase 4, all we need to do in phase 3 is create a link with the ID of the file to be downloaded embedded in the URL.

The fourth, and final, part is the one that is most confusing about this process. The part where we fetch the file and send it to the client's browser.
We start by using the MySQL functions, and the ID sent by phase 3, to fetch the file data from the database. Then we set a few headers, letting the browser know what to expect, before finally sending the contents of the file.

Now, using this summary as a guide, lets start writing our program.

Phase 0: Building a database
The database is simple. One table with a BLOB field for the file data and a few fields for various pieces of information relating to the file:

Expand|Select|Wrap|Line Numbers

 
CREATE TABLE FileStorage (

  FileID Int Unsigned Not Null Auto_Increment,

  FileName VarChar(255) Not Null Default 'Untitled.txt',

  FileMime VarChar(50) Not Null Default 'text/plain',

  FileSize BigInt Unsigned Not Null Default 0,

  FileData MediumBlob Not Null,

  Created DateTime Not Null,

  PRIMARY KEY (FileID)

)

As you see, we store the file name, including the extension.
We have the mime type, which we use to let the browser know what kind of file we are dealing with.
The size of the file in bytes.
And finally the data itself, in a MediumBlob field.

Phase 1: Uploading the file
Now, we need to get the file from the user. The table we designed does not require any additional information from the user, so we will make this simple and create a HTML form with only a single "file" input field and a submit button:

Expand|Select|Wrap|Line Numbers

 
<form action="add_file.php" method="post" enctype="multipart/form-data">

    <input type="file" name="uploaded_file" /><br />

    <input type="submit" value="Upload file" />

</form>
 
<p>

    <a href="list_files.php">See all files</a>

</p>

Note the third attribute of the <form> element, "enctype". This tells the browser how to send the form data to the server. As it is, when sending files, this must be set to "multipart/form-data".
If it is set any other way, or not set at all, your file is probably not going to be transmitted correctly.

At the bottom, we have a link to the list we will create in phase 3.

Phase 2: Add the file to the database
In the form we built in phase 1, we set the action property to "add_file.php". This is the file we are going to build it this phase of the process.

This file needs to check if a file has been uploaded, make sure it was uploaded without errors, and add it to the database:

Expand|Select|Wrap|Line Numbers

 
<?php

# Check if a file has been uploaded

if(isset($_FILES['uploaded_file'])) 

{

    # Make sure the file was sent without errors

    if($_FILES['uploaded_file']['error'] == 0) 

    {

        # Connect to the database

        $dbLink = mysqli_connect("127.0.0.1", "user", "pwd", "dbName");

        if(mysqli_connect_errno()) {

            die("MySQL connection failed: ". mysqli_connect_error());

        }
 
        # Gather all required data

        $name = mysqli_real_escape_string($dbLink, $_FILES['uploaded_file']['name']);

        $mime = mysqli_real_escape_string($dbLink, $_FILES['uploaded_file']['type']);

        $size = $_FILES['uploaded_file']['size'];

        $data = mysqli_real_escape_string($dbLink, file_get_contents($_FILES  ['uploaded_file']['tmp_name']));
 
        # Create the SQL query

        $query = "

            INSERT INTO FileStorage (

                FileName, FileMime, FileSize, FileData, Created

            )

            VALUES (

                '{$name}', '{$mime}', {$size}, '{$data}', NOW()

            )";
 
        # Execute the query

        $result = mysqli_query($dbLink, $query);
 
        # Check if it was successfull

        if($result) 

        {

            echo "Success! Your file was successfully added!";

        }

        else 

        {

            echo "Error! Failed to insert the file";

            echo "<pre>". mysqli_error($dbLink) ."</pre>";

        }

    }

    else 

    {

        echo "Error! 

                An error accured while the file was being uploaded. 

                Error code: ". $_FILES['uploaded_file']['error'];

    }
 
    # Close the mysql connection

    mysqli_close($dbLink);

}

else 

{

    echo "Error! A file was not sent!";

}
 
# Echo a link back to the mail page

echo '<p>Click <a href="index.html">here</a> to go back</p>';

?>

Phase 3: Listing all existing files
So, now that we have a couple of files in our database, we need to create a list of files and link them so they can be downloaded:

Expand|Select|Wrap|Line Numbers

 
<?php

# Connect to the database

$dbLink = mysqli_connect("127.0.0.1", "user", "pwd", "dbName");

if(mysqli_connect_errno()) {

    die("MySQL connection failed: ". mysqli_connect_error());

}
 
# Query for a list of all existing files

$result = mysqli_query($dbLink, "SELECT FileID, FileName, FileMime, FileSize, Created FROM FileStorage");
 
# Check if it was successfull

if($result) 

{
 
    # Make sure there are some files in there

    if(mysqli_num_rows($result) == 0) {

        echo "<p>There are no files in the database</p>";

    }

    else

    {

        # Print the top of a table

        echo "<table width='100%'><tr>";

        echo "<td><b>Name</b></td>";

        echo "<td><b>Mime</b></td>";

        echo "<td><b>Size (bytes)</b></td>";

        echo "<td><b>Created</b></td>";

        echo "<td><b>&nbsp;</b></td>";

        echo "</tr>";
 
        # Print each file

        while($row = mysqli_fetch_assoc($result))

        {

            # Print file info

            echo "<tr><td>". $row['FileName']. "</td>";

            echo "<td>". $row['FileMime']. "</td>";

            echo "<td>". $row['FileSize']. "</td>";

            echo "<td>". $row['Created']. "</td>";
 
            # Print download link

            echo "<td><a href='get_file.php?id=". $row['FileID'] ."'>Download</a></td>";

            echo "</tr>";

        }
 
        # Close table

        echo "</table>";

    }
 
    # Free the result

    mysqli_free_result($result);

}

else 

{

    echo "Error! SQL query failed:";

    echo "<pre>". $dbLink->error ."</pre>";

}
 
# Close the mysql connection

mysqli_close($dbLink);

?>

Phase 4: Downloading a file
This part is the one that usually causes the most confusion.

To really understand how this works, you must understand how your browser downloads files. When a browser requests a file from a HTTP server, the server response will include information on what exactly it contains. These bits of information are called headers. The headers usually include information on the type of data being sent, the size of the response, and in the case of files, the name of the file.

There are of course a lot of other headers, which I will not cover here, but it is worth looking into!

Now, this code. We start simply by reading the ID sent by the link in phase 3. If the ID is valid, we fetch the information on the file who's ID we received, send the headers, and finally send the file data:

Expand|Select|Wrap|Line Numbers

 
<?php

# Make sure an ID was passed

if(isset($_GET['id']))

{

    # Get the ID

    $id = $_GET['id'];
 
    # Make sure the ID is in fact a valid ID

    if(!is_numeric($id) || ($id <= 0)) {

        die("The ID is invalid!");

    }
 
    # Connect to the database

    $dbLink = mysqli_connect("127.0.0.1", "user", "pwd", "dbName");

    if(mysqli_connect_errno()) {

        die("MySQL connection failed: ". mysqli_connect_error());

    }
 
    # Fetch the file information

    $query = "

        SELECT FileMime, FileName, FileSize, FileData 

        FROM fileStorage 

        WHERE FileID = {$id}";
 
    $result = @mysqli_query($dbLink, $query)

        or die("Error! Query failed: <pre>". mysqli_error($dbLink) ."</pre>");
 
    # Make sure the result is valid

    if(mysqli_num_rows($result) == 1)

    {

        # Get the row

        $row = mysqli_fetch_assoc($result);
 
        # Print headers

        header("Content-Type: ". $row['FileMime']);

        header("Content-Length: ". $row['FileSize']);

        header("Content-Disposition: attachment; filename=". $row['FileName']);
 
        # Print data

        echo $row['FileData'];

    }

    else

    {

        echo "Error! No image exists with that ID.";

    }
 
    # Free the mysqli resources

    @mysqli_free_result($result);

    @mysqli_close($dbLink);
 
}

else

{

    echo "Error! No ID was passed.";

}

?>

Any decent browser should be able to read the headers and understand what type of file this is, and that it is to be downloaded, not opened.

The finish line
So, as you see, this is not as complex as one might think.

This code is of course only written for demonstration purposes and I would not recommend using it without adding a little extra security. Un-edited, this code would basically allow anybody to upload anything to your server, which is not a good idea!

I hope this has been helpful, and I wish you all the best.

See you around,
- Atli Þór

Edits

August 20th, 2008 - Replaced the old mysql functions with the improved mysqli functions.
In order to keep the code as easy to understand as possible, I still retain the procedural style of the old mysql functions, even tho the mysqli extension provides an OOP version.

roseple · #51 June 19th, 2009, 11:42 AM

Ok, and thank you very much on all your help.
Ooops, I just want to ask... Is this uploading codes can support mp4 and mp3 files? I already increase my upload and post_max_size.

Thanks..

Atli · #52 June 19th, 2009, 11:51 AM

This code can upload any file, regardless of size, type or extension.

If your PHP and MySQL setup is set up to handle large file uploads, then this code can process them.

apple08 · #53 June 22nd, 2009, 04:22 AM

Hello..

Can anyone know why this error is present:

Quote:

Fatal error: Call to undefined function mysqli_connect() in c:\Inetpub\wwwroot\uploading\connect.php on line 20

Code in line 20:

Expand|Select|Wrap|Line Numbers

$dbLink = mysqli_connect('localhost', 'root', 'ai3', 'filestorage');

Thanks..

apple08 · #54 June 22nd, 2009, 07:14 AM

Hello..

Why does the error "Error, Failed to insert the File."

Thank you..

Atli · #55 June 22nd, 2009, 11:36 AM

Hi, apple.

Quote:

Originally Posted by apple08

Hello..

Can anyone know why this error is present:

Quote:

Fatal error: Call to undefined function mysqli_connect() in c:\Inetpub\wwwroot\uploading\connect.php on line 20

Code in line 20:

Expand|Select|Wrap|Line Numbers

$dbLink = mysqli_connect('localhost', 'root', 'ai3', 'filestorage');

Thanks..

This error occurs when you try to use a function belonging to an extension that isn't loaded (or doesn't exist).

In this case, your PHP installation is missing the Improved MySQL extension
See the Installation page in the manual of instructions on how to enable the extension.

You can also rewrite the code to use the old MySQL extension, if that is available in your installation.
You would just have to rewrite the connection part and remove the "i" from the other functions.

Quote:

Originally Posted by apple08

Hello..

Why does the error "Error, Failed to insert the File."

Thank you..

That is the error I wrote into my example code in case the SQL query that is supposed to insert the file failed.
It should have printed additional information that would be good to have if you want our help debugging this.

apple08 · #56 June 23rd, 2009, 08:28 AM

Hello,

To be specific, the error I encountered was " Error, failed to insert the File. MySQL server has gone away." There are also times that the error was " Error, failed to insert the File. Got a packet larger than the max_allowed_packet bytes."

That said errors only occur when I tried to upload a file larger than 2Mb.

Thank you.

Atli · #57 June 23rd, 2009, 10:51 AM

That would indicate that you are sending more data than MySQL is willing to accept.

You need to alter the MySQL configuration so that it can accept more data.
The "max_allowed_packet" directive is a good place to start.

The MySQL configuration file is named "my.cnf" (or "my.ini", on Windows).
It is usually just under the MySQL installation dir, although that can vary based on the installation type.

apple08 · #58 June 25th, 2009, 07:27 AM

Hello, do you guys know how I can make the list of my uploaded files become a thumbmails instead of table list?

Thanks.

roseple · #59 June 25th, 2009, 07:37 AM

Hi...

I just want to ask why does my server is too slow whenever I upload a quite large fie. Is it because I save the file to the database.

Thank you....

msei · #60 June 30th, 2009, 05:47 PM

Hi,

I have a question about the list files, Is there a way to list only the field TYPE ='FYR' instead list all the record. Any help will be appreciated.

Thank you in advance.

msei

Atli · #61 June 30th, 2009, 09:19 PM

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.

Expand|Select|Wrap|Line Numbers

SELECT ... FROM `FileStorage` WHERE `TYPE` = 'FYR'

xaralee · #62 August 9th, 2009, 02:36 PM

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 · #63 August 12th, 2009, 06:06 AM

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 · #64 August 12th, 2009, 09:13 AM

Quote:

Originally Posted by 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.

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

Frinavale · #65 September 2nd, 2009, 08:31 PM

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 · #66 September 11th, 2009, 04:58 AM

Quote:

Originally Posted by 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 :)

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 · #67 September 13th, 2009, 06:38 PM

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 · #68 September 15th, 2009, 01:12 AM

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 · #69 October 19th, 2009, 06:53 PM

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

Atli · #70 October 19th, 2009, 08:24 PM

Hey.

To upload more than one file, you add more <input> elements, each with a different name.

Expand|Select|Wrap|Line Numbers

 
<input type="file" name="uploaded_file_1" /><br />

<input type="file" name="uploaded_file_2" /><br />

<input type="file" name="uploaded_file_N" /><br />

And then you grab each file from the $_FILES array using it's name:

Expand|Select|Wrap|Line Numbers

 
$file1 = $_FILES["uploaded_file_1"];

$file2 = $_FILES["uploaded_file_2"];

$fileN = $_FILES["uploaded_file_N"];

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.)

Uploading files into a MySQL database using PHP

re: Uploading files into a MySQL database using PHP

re: Uploading files into a MySQL database using PHP

re: Uploading files into a MySQL database using PHP

re: Uploading files into a MySQL database using PHP

re: Uploading files into a MySQL database using PHP

re: Uploading files into a MySQL database using PHP

re: Uploading files into a MySQL database using PHP

re: Uploading files into a MySQL database using PHP

re: Uploading files into a MySQL database using PHP

re: Uploading files into a MySQL database using PHP

re: Uploading files into a MySQL database using PHP

re: Uploading files into a MySQL database using PHP

re: Uploading files into a MySQL database using PHP

re: Uploading files into a MySQL database using PHP

re: Uploading files into a MySQL database using PHP

re: Uploading files into a MySQL database using PHP

re: Uploading files into a MySQL database using PHP

re: Uploading files into a MySQL database using PHP

re: Uploading files into a MySQL database using PHP

re: Uploading files into a MySQL database using PHP

/bytes/development

/bytes/it

/bytes/about