Saving a File Directly Into a MySQL Database

October 15th, 2008 in Programming & IT

by: Matthew Griffin

In cases where file security isn't an issue, it's easy to save web form file uploads into a public directory on your server where they can be accessed by all. You can do this with just a few lines of PHP code, and it works great for public photo galleries, or avatar uploaders, etc. But what if you need to limit access to the files your users are uploading? Let's say, for example, you have a client that needs to be able to upload sensitive company files that can only be accessed by certain employees. This is where the MySQL longblob field comes in handy. In this tutorial, I'll show how to get a file from a web form into a MySQL database and out again.

Preparing the MySQL Database

If you don't already know how to create a new MySQL database on your server, you'll need to get acquainted with that process before any of this will be helpful. Most LINUX hosting packages come with a visual MySQL manager such as PHP MyAdmin by default. With a tool like that It shouldn't take long to figure out what's what. In this example, I'm going to assume you have a database called "MyDatabase" with a table called "Files". The Files table will have a number of fields, the most important of which is the File_Content field. The File_Content field will be a longblob field. The longblob field type isn't one you use every day. It can house large amounts of binary information that make up a file. You can run this SQL code on your database to create the new table automatically.


  `ID` int(25) NOT NULL auto_increment,
  `Title` varchar(250) NOT NULL default '',
  `File_Name` varchar(250) NOT NULL default '',
  `File_Type` varchar(15) NOT NULL default '',
  `File_Size` varchar(45) NOT NULL default '',
  `File_Content` longblob NOT NULL,
  `File_Extension` varchar(10) NOT NULL default '',

Setting Up Your HTML Form

Now that the database is ready to receive file uploads, the next step is to build an HTML form that presents the user with all of the necessary fields and options. The code below will get you up and running. Pay special attention to the enctype attribute in the opening <form> tag. Notice that it's set to "multipart/form-data". Without this attribute, your file will never make it to the server.

<form action="<?=$PHP_SELF;?>" method="POST" enctype="multipart/form-data" name="upload_form" id="upload_form">
    <label>Title:</label><input name="title" type="text" id="title" value="<?=htmlentities(stripslashes($_POST['title']));?>" size="35">
    <label>File:</label<input type="file" name="file">   
    <input name="action" type="hidden" id="action" value="add_document">

Writing Some PHP to Handle the File

Now, normally it's best to write your PHP code in an object oriented system. For our purposes, though, I'm going to combine all the code into one script so you can more easily see how it all works. The code below has detailed comments to help keep you from getting lost. First the script will check that a file has been uploaded. Next it will break the file apart, connect to the MySQL database and insert the file.


// Connect to the database
// replace "user_name" and "password" with your real login info

$dbh = mysql_connect("localhost","user_name","password") or die("There was a problem with the database connection.");
    $dbs = mysql_select_db("MyDatabase", $dbh) or die("There was a problem selecting the categories.");

// Set up a list of acceptable file extensions.
// This keeps out malicious files

$acceptable_extensions[0] = "pdf";
$acceptable_extensions[1] = "jpg";
$acceptable_extensions[2] = "gif";
$acceptable_extensions[3] = "doc";
$acceptable_extensions[4] = "ppt";
$acceptable_extensions[5] = "xls";
$acceptable_extensions[6] = "xsl";
$acceptable_extensions[7] = "PDF";
$acceptable_extensions[8] = "JPG";
$acceptable_extensions[9] = "GIF";
$acceptable_extensions[10] = "DOC";
$acceptable_extensions[11] = "PPT";
$acceptable_extensions[12] = "XLS";
$acceptable_extensions[13] = "XSL";
$acceptable_extensions[14] = "txt";
$acceptable_extensions[15] = "TXT";
$acceptable_extensions[16] = "csv";
$acceptable_extensions[17] = "CSV";
$acceptable_extensions[18] = "docx";
$acceptable_extensions[19] = "DOCX";

// Check the uploaded file to make sure it's a valid file

$validated = 1;

if($_FILES && $_FILES['file']['name']){
    //make sure the file has a valid file extension
    $file_info = pathinfo($_FILES['file']['name']);
    $acceptable_ext = 0;
    for($x = 0; $x < count($acceptable_extensions); $x++){
        if($file_info['extension'] == $acceptable_extensions[$x]){
            $acceptable_ext = 1;
        $validated = 0;
    $validated = 0;

//Now that we're sure we have a valid file,
//we'll add it into the database


    // Get important information about the file and put it into variables

    $fileName = $_FILES['file']['name'];
    $tmpName  = $_FILES['file']['tmp_name'];
    $fileSize = $_FILES['file']['size'];
    $fileType = $_FILES['file']['type'];

    // Slurp the content of the file into a variable
    $fp = fopen($tmpName, 'r');
    $content = fread($fp, filesize($tmpName));
    $content = addslashes($content);

        $fileName = addslashes($fileName);
    $file_info = pathinfo($_FILES['file']['name']);

    $sql = "INSERT INTO Files SET
                Title = "".htmlentities(stripslashes($_POST['title']))."",
                File_Name = '".$fileName."',
                File_Type = '".$fileType."',
                File_Size = '".$fileSize."',
                File_Content = '".$content."',
                File_Extension = '".$file_info['extension']."'";
    $result = mysql_query($sql);
    // If the query was successful, give success message

        echo "Could not add this file.";
        echo  "New file successfully added.";

    echo "Invalid file.";

Make sure to test this script with a small file. If you're having trouble getting this to work, check out the "Important Notes and Pitfalls" section at the end of this tutorial. More than likely you'll find your answer there.

Getting Your File Back Out of the MySQL Database

Okay, we've successfully uploaded a file directly into a longblob field in a MySQL database. But that's not the end. Now we need to pull it back out. This process is actually quite simple. Unfortunately, I didn't have a comprehensive tutorial like this when I was figuring it out so it took a lot of trial and error. Essentially, we are going to create a PHP script that accepts a file ID through a $_GET variable called id, pulls the corresponding file out of the database and sends it to the browser with the correct headers so the user can open it. The script should look something like this:

// We'll start out again by connecting to the database
// replace "user_name" and "password" with your real login info

$dbh = mysql_connect("localhost","user_name","password") or die("There was a problem with the database connection.");
    $dbs = mysql_select_db("MyDatabase", $dbh) or die("There was a problem selecting the categories.");

// Now, We'll check to make sure our $_GET variable is a number

    echo "Invalid file chosen.";

// Next, we'll run a query on the database to get the file out

$sql = "SELECT * FROM Files
            WHERE Files.ID = ".$_GET['id'];
$result = mysql_query($sql);

// If the query was invalid or failed to return a result, an error is thrown

if(!$result || !mysql_num_rows($result)){
    echo "Invalid file chosen.";

// Finally, we will send the file to the browser

$curr_file = mysql_fetch_assoc($result);

$size = $curr_file['File_Size'];
$type = $curr_file['File_Type'];
$name = $curr_file['File_Name'];
$content = $curr_file['File_Content'];

header("Content-length: ".$size."");
header("Content-type: ".$type."");
header('Content-Disposition: attachment; filename="'.$name.'"');
echo $content;

// That's it. We're finished.

Important Notes and Pitfalls

To start, there are two very important server settings that can completely ruin your upload script. The first is the "upload_max_filesize" variable in your PHP.ini file. If this is set too low, large files just flat out won't make it up onto the server. The second is the MySQL server variable called "max allowed packet". Most of the time, hosts like to set this pretty low by default. If you don't have direct access to this setting, you may need to contact your host to have them increase it. If you upload a file that's larger than the "max allowed packet" setting, it won't make it into the database.

Also, I didn't include a password protection script in this tutorial but you should be able to find a free one floating around the internet somewhere. It's important that you have good security on pages that allow visitors to upload files. I won't insult you by listing the reasons why.



Posted By: PHP Intern on 10/16/08

While I wont argue about if this is a good idea or not overall (I am of the thought that storing files in the DB is always a bad idea) but lets assume we're fine with that. In your $acceptable_extensions array you have some capitalization variations, but not all. It would be simpler to only have lower case extensions in your array and then in the loop strtolower($file_info['extension']) and that should take care of all the possible case variations.

Posted By: Matthew Grffin on 10/16/08

Good suggestion, PHP intern.

Posted By: alex on 10/17/08

what are the disadvantages of uploading to a folder and using .htaccess to block directory listing and direct file access (by rewriting the URI or something)? great idea though.

Posted By: John on 10/17/08

Never heard of in_array()?. Also,if extension is found, there is no break used.

Posted By: Matthew Griffin on 10/17/08

Alex, the disadvantage of using a .htaccess protected directory is that it limits the amount of automation you can do. For example, each user would have to be set up with a new directory with user name and password. With the method above, you can specify who has access and who doesn't using a user table and a table connecting allowed users to files.

Posted By: Matthew Grffin on 10/17/08

John, the break is in an if condition just below that.

Posted By: Heartsjava on 10/17/08

Storing files in the DB is almost always a horrible idea, databases are almost always optimized for small quick reads. Write a controller that proxies your file if the user has the proper permissions.

Posted By: Zac on 10/28/08

I dont know, I cant get any of this to work. Im burned out. Guess I need to take a class. If anyone can suggest where I can learn how to upload an image and some text, store it, and have it put onto another page, please let me know. I thought this one was it.

Posted By: Matthew Grffin on 10/28/08

Zac, this is probably a little confusing for you if this is your first attempt to upload a file. If all you want to do is upload a file into a directory, do a search for another tutorial. This one is way more than you need.

Posted By: Web Design Adelaide on 11/10/08

I'm not sure if this is something that I'd use, but was an interesting read anyway. Good to read things that look outside the square, and who knows if it may be something that may be absolutely necessary to use as some stage. thanks.

Posted By: IrishaVesta on 01/01/09

Really seful article. Especially pitfalls. I spend to find why Mysql is not uploading file exceeeding 1M. Thanks a bunch, Irisha @ TEXT2TALK

Posted By: Matthew Griffin on 01/02/09

Absolutely. Glad it helped.

Posted By: ashish saxena(chandigarh,india) on 01/30/09

it is very useful and nice tutorials .a lot of thanks. but how to import file into mysql database.plz help me as soon as possible

Posted By: Andrews oberko on 09/20/09

I tried the code but did not give any error yet can not work. What is the problem?

Posted By: istambay on 10/05/09

storing files into DB is not a good idea for many but badly needed for some... There are webhost out there giving you unlimited disk space BUT limited file webhost is just 150K files including individual emails. Storing files into DB will save me from file count increase.

Posted By: Web Design Adelaide on 01/21/10

This is a good idea -- Ive often wondered whether its possible to do this with MySQL. Thanks

Post Your Comment

Comments are closed.