Upload files into MySQL using php

Posted under Article Database Development PHP on Sunday, 2 August 2009 by rueben

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:

* PHP Basics
* MySQL Basics
* How to interact with MySQL in PHP (using the mysqli extension).
* HTML Forms and how to handle POST data in PHP.

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:

 

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:


 <form action="http://blog.950buy.com/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="http://blog.950buy.com/list_files.php">See all files</a>
</p>

Note the third attribute of the

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:


<?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("www.cpworld2000.com", "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:


<?php
//Connect to the database
$dbLink = mysqli_connect("www.cpworld2000.com", "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:


<?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("www.cpworld2000.com", "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.

Tagged with: Database, php code, PHP Database, php upload

No Responses to “Upload files into MySQL using php”

Name:

Email:

Website:

Comment:

XHTML: You can use these tags:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>