Uploading files into a MySQL database using PHP

[Atli]

You may be wondering why you would want to put your files “into” the database, rather than just onto the file-system. Well, most of the time, you wouldn’t.

In situations where your PHP application needs to store entire files, the preferred method is to save the file onto the server’s file-system, and store the physical location of the file in your database. This is generally considered to be the easiest and fastest way to store files.

However, you may find yourself in situations where you would want to keep the file itself with the other data in your database. This gives you - or rather: MySQL - complete control over the file data, rather than just the location of the file on the server.

There are some downsides to this method though, such as; decreased performance and added complexity to both your PHP code and your database structure. This is something you should carefully consider before using this in a real-life application.

Having said that, this article demonstrates how you can upload a file from a browser into MySQL, and how to send the files back to the browser.

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

• PHP Basics
• MySQL Basics
• Using MySQL in PHP (mysqli)
• 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 `file` (
    `id`        Int Unsigned Not Null Auto_Increment,
    `name`      VarChar(255) Not Null Default 'Untitled.txt',
    `mime`      VarChar(50) Not Null Default 'text/plain',
    `size`      BigInt Unsigned Not Null Default 0,
    `data`      MediumBlob Not Null,
    `created`   DateTime Not Null,
    PRIMARY KEY (`id`)
)
 

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:

<!DOCTYPE html>
<head>
    <title>MySQL file upload example</title>
    <meta http-equiv="content-type" content="text/html; charset=UTF-8">
</head>
<body>
    <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>
</body>
</html>

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:

<?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 = new mysqli('127.0.0.1', 'user', 'pwd', 'myTable');
        if(mysqli_connect_errno()) {
            die("MySQL connection failed: ". mysqli_connect_error());
        }
 
        // Gather all required data
        $name = $dbLink->real_escape_string($_FILES['uploaded_file']['name']);
        $mime = $dbLink->real_escape_string($_FILES['uploaded_file']['type']);
        $data = $dbLink->real_escape_string(file_get_contents($_FILES  ['uploaded_file']['tmp_name']));
        $size = intval($_FILES['uploaded_file']['size']);
 
        // Create the SQL query
        $query = "
            INSERT INTO `file` (
                `name`, `mime`, `size`, `data`, `created`
            )
            VALUES (
                '{$name}', '{$mime}', {$size}, '{$data}', NOW()
            )";
 
        // Execute the query
        $result = $dbLink->query($query);
 
        // Check if it was successfull
        if($result) {
            echo 'Success! Your file was successfully added!';
        }
        else {
            echo 'Error! Failed to insert the file'
               . "<pre>{$dbLink->error}</pre>";
        }
    }
    else {
        echo 'An error accured while the file was being uploaded. '
           . 'Error code: '. intval($_FILES['uploaded_file']['error']);
    }
 
    // Close the mysql connection
    $dbLink->close();
}
else {
    echo 'Error! A file was not sent!';
}
 
// Echo a link back to the main 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 = new mysqli('127.0.0.1', 'user', 'pwd', 'myTable');
if(mysqli_connect_errno()) {
    die("MySQL connection failed: ". mysqli_connect_error());
}
 
// Query for a list of all existing files
$sql = 'SELECT `id`, `name`, `mime`, `size`, `created` FROM `file`';
$result = $dbLink->query($sql);
 
// Check if it was successfull
if($result) {
    // Make sure there are some files in there
    if($result->num_rows == 0) {
        echo '<p>There are no files in the database</p>';
    }
    else {
        // Print the top of a table
        echo '<table width="100%">
                <tr>
                    <td><b>Name</b></td>
                    <td><b>Mime</b></td>
                    <td><b>Size (bytes)</b></td>
                    <td><b>Created</b></td>
                    <td><b>&nbsp;</b></td>
                </tr>';
 
        // Print each file
        while($row = $result->fetch_assoc()) {
            echo "
                <tr>
                    <td>{$row['name']}</td>
                    <td>{$row['mime']}</td>
                    <td>{$row['size']}</td>
                    <td>{$row['created']}</td>
                    <td><a href='get_file.php?id={$row['id']}'>Download</a></td>
                </tr>";
        }
 
        // Close table
        echo '</table>';
    }
 
    // Free the result
    $result->free();
}
else
{
    echo 'Error! SQL query failed:';
    echo "<pre>{$dbLink->error}</pre>";
}
 
// Close the mysql connection
$dbLink->close();
?>

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 = intval($_GET['id']);
 
    // Make sure the ID is in fact a valid ID
    if($id <= 0) {
        die('The ID is invalid!');
    }
    else {
        // Connect to the database
        $dbLink = new mysqli('127.0.0.1', 'user', 'pwd', 'myTable');
        if(mysqli_connect_errno()) {
            die("MySQL connection failed: ". mysqli_connect_error());
        }
 
        // Fetch the file information
        $query = "
            SELECT `mime`, `name`, `size`, `data`
            FROM `file`
            WHERE `id` = {$id}";
        $result = $dbLink->query($query);
 
        if($result) {
            // Make sure the result is valid
            if($result->num_rows == 1) {
            // Get the row
                $row = mysqli_fetch_assoc($result);
 
                // Print headers
                header("Content-Type: ". $row['mime']);
                header("Content-Length: ". $row['size']);
                header("Content-Disposition: attachment; filename=". $row['name']);
 
                // Print data
                echo $row['data'];
            }
            else {
                echo 'Error! No image exists with that ID.';
            }
 
            // Free the mysqli resources
            @mysqli_free_result($result);
        }
        else {
            echo "Error! Query failed: <pre>{$dbLink->error}</pre>";
        }
        @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

Revisions

• August 20th, 2008 - Replaced the old mysql functions with the improved mysqli functions.
• December 12th, 2009 - Updated the introduction to include a bit more detail on the pros and cons of this method. Also improved the code structure a bit. Replaced the mysqli procedural functions with their OOP counterparts. (Thanks to kovik for pointing out the need for these changes!)

[Atli]

@sachin8289
This is a configuration issue with MySQL. By default the largest "packet" you can send at one time is 1MB. You need to edit the server's configuration file to allow a bigger packet to be received.

You can find more details here, in the manual:
C.5.2.10. Packet too large

[Sushant29]

hey atli , i tried for downloading all types of files and it works also.
But the problem is that it does'nt shows the contents/data of that particular file.

 
<?php
 
include('config.php');
$filename=$_GET['fname'];
$ctype=$_GET['ctype'];
$size=$_GET['size'];
/* echo $filename;
echo $ctype;
echo $size; */
$tmp = explode(".",$filename); 
switch ($tmp[count($tmp)-1]) 
{ 
  case "pdf": $ctype="application/pdf"; break; 
  case "exe": $ctype="application/octet-stream"; break; 
  case "zip": $ctype="application/zip"; break; 
  case "docx": 
  case "doc": $ctype="application/msword"; break; 
  case "csv": 
  case "xls": 
  case "xlsx": $ctype="application/vnd.ms-excel"; break; 
  case "ppt": $ctype="application/vnd.ms-powerpoint"; break; 
  case "gif": $ctype="image/gif"; break; 
  case "png": $ctype="image/png"; break; 
  case "jpeg": 
  case "jpg": $ctype="image/jpg"; break; 
  case "tif": 
  case "tiff": $ctype="image/tiff"; break; 
  case "psd": $ctype="image/psd"; break; 
  case "bmp": $ctype="image/bmp"; break; 
  case "ico": $ctype="image/vnd.microsoft.icon"; break; 
  default: $ctype="application/force-download"; 
} 
 
$path=$filename;
 
header("Pragma: public"); // required 
header("Expires: 0"); 
header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); 
header("Cache-Control: private",false); // required for certain browsers 
header("Content-Type: $ctype"); 
header("Content-Disposition: attachment; filename=$path"); 
header("Content-Transfer-Encoding: binary"); 
header("Content-Length: ".$size); 
ob_clean(); 
flush(); 
readfile($path);
?>
 
 

can you sort with this error..............

[mysqlquery]

Hello,

I now this is old script, but this is what I really need.
Im beginner and I tried to edit this, I want mysql, and everything works fine except 'get_file.php'. It says 'Error! No image exists with that ID.'; I cant get ID
Please can you help me with this.

I will paste my code, if you have a time please look into that and help.

[mysqlquery]

<?php
 // Make sure an ID was passed
 if(isset($_GET['id'])) {
 // Get the ID
     $id = intval($_GET['id']);
 
     // Make sure the ID is in fact a valid ID
     if($id <= 0) {
         die('The ID is invalid!');
     }
     else {
         $dbLink =  $dbLink = mysql_connect("localhost", "root", "") or die (mysql_error());
 
mysql_select_db("databese1", $dbLink) or die ("Could not connect to database!");
 
         $query = "
             SELECT `mime`, `name`, `size`, `data`
             FROM `file`
             WHERE `id` = {$id}";
        // $result = $dbLink->query($query);
 
 $result = mysql_query ($query, $dbLink);
 
         if($result) {
             // Make sure the result is valid
             if($result == 1) {
                 $row = mysql_fetch_assoc($result);
 
                 header("Content-Type: ". $row['mime']);
                 header("Content-Length: ". $row['size']);
                 header("Content-Disposition: attachment; filename=". $row['name']);
 
                 // Print data
                 echo $row['data'];
             }
             else {
                 echo 'Error! No image exists with that ID.';
             }
 
             // Free the mysqli resources
             @mysql_free_result($result);
         }
         else {
             echo "Error! Query failed:";
         }
         mysql_close($dbLink);
     }
 }
 else {
     echo 'Error! No ID was passed.';
 }
 ?>

[Atli]

Hey.

Line #26 of your code. It should be comparing the number of rows in the result set to the number 1. As it is there, however, it's just comparing the result set reference to 1. Look into the mysql_num_rows function.

One question though. Why would you downgrade the code to use the old MySQL API, when the original code is already using the more modern MySQLi extension? (Granted, it's old enough to be using the procedural MySQLi functions, and without prepared statements, but still...)

[mysqlquery]

Thanks for replay, sorry for my poor english.
Can you suggest me any pagination script that will work on this mysqli script. I tried to use some pagination scripts but it wont work, and same pagination scripts work nice with other upload scripts.

[Ammu]

I have tried it and works properly
Thank you so much atli for this useful article.

[strelc]

Insert this

while (@ob_end_clean());

line before

 header("Content-Disposition: attachment; filename=". $row['name']);

and download will work like a charm ...

[sundyp krishna]

hi, wen i download my file, i cant able to open it.. its get corrupted.. may i knw wat coud be the error.. its wrking for text files.. but not for pdf/image/ppt/doc...

[Atli]

Likely some warning or notice in PHP is being printed into the output. Try opening the corrupt file in a text editor like Notepad (make sure it's not too big, or it'll freeze the editor!) and look for error messages in the binary stuff. The text editor should be able to see the text message in the binary and display it as text.

[hanugeek]

AWSOME! Thankyou so much for share! The code works..!!

[abdulrahim0704]

boss , i got this error , where is the problem ? .. for download script only (get_file.php)

Warning: Cannot modify header information - headers already sent by (output started at C:\xampp\htdocs\test2\get_file.php:2) in C:\xampp\htdocs\test2\get_file.php on line 33

Warning: Cannot modify header information - headers already sent by (output started at C:\xampp\htdocs\test2\get_file.php:2) in C:\xampp\htdocs\test2\get_file.php on line 34

Warning: Cannot modify header information - headers already sent by (output started at C:\xampp\htdocs\test2\get_file.php:2) in C:\xampp\htdocs\test2\get_file.php on line 35

<?php
// Make sure an ID was passed
if(isset($_GET['id'])) {
// Get the ID
    $id = intval($_GET['id']);
 
    // Make sure the ID is in fact a valid ID
    if($id <= 0) {
        die('The ID is invalid!');
    }
    else {
        // Connect to the database
        $dbLink = new mysqli('127.0.0.1', 'root', '', 'file');
        if(mysqli_connect_errno()) {
            die("MySQL connection failed: ". mysqli_connect_error());
        }
 
        // Fetch the file information
        $query = "
            SELECT `mime`, `name`, `size`, `data`
            FROM `file`
            WHERE `id` = {$id}";
        $result = $dbLink->query($query);
 
        if($result) {
            // Make sure the result is valid
            if($result->num_rows == 1) {
            // Get the row
                $row = mysqli_fetch_assoc($result);
 
                // Print headers
               header("Content-Type: ". $row['mime']);
               header("Content-Length: ". $row['size']);
                header("Content-Disposition: attachment; filename=". $row['name']);
 
                // Print data
                echo $row['data'];
            }
            else {
                echo 'Error! No image exists with that ID.';
            }
 
            // Free the mysqli resources
            @mysqli_free_result($result);
        }
        else {
            echo "Error! Query failed: <pre>{$dbLink->error}</pre>";
        }
        @mysqli_close($dbLink);
    }
}
else {
    echo 'Error! No ID was passed.';
}
?>
 

[abdulrahim0704]

hanugeek :

share with me the download code ?

[Atli]

abdulrahim0704. Those errors indicate that something is being printed before the code starts. Note that NOTHING can be placed before the opening <?php tag in my example code. If there is so much as a space, then what you posted will happen.

I've also seen this happen when people try to use Unicode files for their PHP code (UTF-8, specifically), including the BOM (Byte order mark) character. PHP doesn't handle Unicode, so the file must be saved as ASCII. If you are unsure on this point, try installing Notepad++, opening the file, and using the "Encoding" menu to convert the file to ASCII.

[abdulrahim0704]

Atlii thanks for the help :) now it work perfectly !!!!! awesome ! hahahaha...

[chikodi]

I need help after i used this code was still getting this errors

Warning: Cannot modify header information - headers already sent by (output started at /home/zivamaho/public_html/get_file.php:1) in /home/zivamaho/public_html/get_file.php on line 32

Warning: Cannot modify header information - headers already sent by (output started at /home/zivamaho/public_html/get_file.php:1) in /home/zivamaho/public_html/get_file.php on line 33

Warning: Cannot modify header information - headers already sent by (output started at /home/zivamaho/public_html/get_file.php:1) in /home/zivamaho/public_html/get_file.php on line 34
/tmp/phpR9kTog


this is my code: i used it on cpanel upload but got the above errors. Pls help me out

 <?php
    // Make sure an ID was passed
    if(isset($_GET['id'])) {
    // Get the ID
        $id = intval($_GET['id']);
 
        // Make sure the ID is in fact a valid ID
        if($id <= 0) {
            die('The ID is invalid!');
        }
        else {
            // Connect to the database
            $dbLink = mysqli_connect('localhost', 'zivamaho_data', 'hospitality', 'zivamaho_hotel');
            if(mysqli_connect_errno()) {
                die("MySQL connection failed: ". mysqli_connect_error());
            }
 
            // Fetch the file information
            $query = "
                SELECT first_name,last_name,email,address,phone,mime, name, size, data
                FROM file
                WHERE `id` = {$id}";
            $result = $dbLink->query($query);
 
            if($result) {
                // Make sure the result is valid
                if($result->num_rows == 1) {
                // Get the row
                    $row = mysqli_fetch_assoc($result);
 
                    // Print headers
                    header("Content-Type:".$row['mime']);
                    header("Content-Length:".$row['size']);
                    header("Content-Disposition:attachment;filename=".$row['name']);
 
 
                    // Print data
                    echo $row['data'];
                }
                else {
                    echo 'Error! No image exists with that ID.';
                }
 
                // Free the mysqli resources
                @mysqli_free_result($result);
            }
            else {
                echo "Error! Query failed: <pre>{$dbLink->error}</pre>";
            }
            @mysqli_close($dbLink);
        }
    }
    else {
        echo 'Error! No ID was passed.';
    }
    ?>

[Atli]

Hey, chikodi. This is the exact same problem abdulrahim0704 was having in the posts above yours. The same advice I gave him applies to you as well.

Also, use [code] tags when posting code. Otherwise the code is more or less unreadable.

[hadinetcat]

hi, im getting this error please help me when i upload file
Error! A file was not sent!

[Atli]

Well, clearly a file isn't being sent. Beyond that I can't say without knowing what you did. Make sure your form is written correctly, specifically the "enctype" attribute on the actual <form> element, and the name of the input elements.

[hadinetcat]

hi guys, i used the code and its work just fine, but when it come to download pdf file it just print in the screen garbage no errors. also checked no white space no bom characters, i don't know whats going on. im have been trying to solve this for days but couldn't please some help required here.
"û<؟أشX .VبUضà-ى6?N
<}oيز:5¸`@ô›‎ژHصoءµپ_ز=ح§ƒؤ،l*ô¸ƒo©#é{ ‡ّ_ںn ¶*'ًE‏–"ضçَùüê=،yO©§™ےWد>|x(U§ںً|‡çسzDA!‚²ـXé ¹"üےjآـ›ٌُ÷4B/ْA حڈûWش قكƒcôےoîJS¹7ز,ےہƒُژAْ‹}÷(S–PO6$وك_÷*7<ںإ¾–$¦2 }ƒث×ژsهہyôو–>]7x‹r,ھ.Mإ‏¶#‹¼ـً°÷ڈإpnO‹ ~؟ش›‹s‎üûxً ُا’
ü[ـ~yدم‰‚ہhE6‎F×'‹?[كگ9‏¶é¬g?لٍح~كِqص´p‏güلéüےc
ٹآهx· `>„ے^uِأ*>‡ـacآھéٌْp@â÷مپے°؛‹^êi <ءوےظ·à}@ے_“اضاڈpقTC‎ Sp.l$p/بµہ_n,کُ؟êےWّz،R?دزaس*AْZàR??[‎l'غlً*كُثq‏ٌ{ƒnx؟éاµ4°،نعكV$ط*Gْضْـ}~ œ@– :—’?گ9°±ے
ôçغèôب'ٍَے7*?oZ"¸é*ًءT
دسڈê-ُںد6ےy»Dkv
yâآ÷üڈ،½ے‏¾ض5q¢¾‘~lOم‎ê×؟ْك×ْpںھ§à¶ –&نـ8مübGّ
‏¯چ†@#¦،§§ْ؟/çز2ھMہْ?Q‎AـƒہoOِچ½Cع~¦3ْپnA$^ق‘ُ ¶üے¼ûYVGاسژ«¯إے*يا<~*سu+ثپù ‎O#ً@¹"كO§معش8ô¦?e:£ ڈثث‎YٍٍéZ،ƒXYژ£¦ہپoOô؟مè@¹·ُ؟´*g²ْt…S`>ں[sُ¨?Okٹب‰²‚
؟ُ¹¸½يحاوئ×½د´½U4ژLq«31V4ynGçM›شI ¨؟مپيtDSˆء¯س‡==xôرسڈ “‎_>گُٹ9 ?ط²H½¯ù½ى?¯زuê£êéْ‘ù؟زكںًوقئظ¯& ز`2§…ڑX é 1!§ٌ…م›‚x<*.[هêü×/گ*ئذ ¾،4آy‹’ أ¨\r,dbْ‏ش ë8?¶؛پ(r ‹PH§ “ا×?ië^خIXـƒè؟çüة#ü½عهQ›ْ~€‎snئؤںiJ¥$بyµح ڈ§écد?ï›Kطپا3€ع8Pا1¹”¨r„@b¦چعأR$•=د©Wذڑµ$¯°±û £¢z؛پ+w& ڈَ7ٹŒe²ً,•“‚=0ء#ئfkحè1%—Y]V7Naعرd"هdز2ecڑj&‚**#‹pذُ¦±œSRھjj)b|دددگَé®X5 ٌrاW
WV¢I‎Bاًط[ْ{ê—fn\ث7ًœ[#`Y‏زٹھp£‚ڑ4+oشo`
"

 <?php
 
 
$company =$_GET['company'];
 
 
// Make sure an ID was passed
if(isset($_GET['id'])) {
// Get the ID
    $id = intval($_GET['id']);
 
    // Make sure the ID is in fact a valid ID
    if($id <= 0) {
        die('The ID is invalid!');
    }
    else {
        // Connect to the database
        $dbLink = new mysqli('localhost', 'sqldata', 'sqldata', 'balhaf');
        if(mysqli_connect_errno()) {
            die("MySQL connection failed: ". mysqli_connect_error());
        }
 
        // Fetch the file information
        $query = "
            SELECT mime, name, size, data
            FROM $company
            WHERE id = $id";
        $result = $dbLink->query($query);
 
        if($result) {
            // Make sure the result is valid
            if($result->num_rows == 1) {
            // Get the row
                $row = mysqli_fetch_assoc($result);
 
                // Print headers
                header("Content-Type: ". $row['mime']);
                header("Content-Length: ". $row['size']);
                header("Content-Disposition: attachment; filename=". $row['name']);
                // Print data
                echo $row['data'];
            }
            else {
         echo 'Error! No image exists with that ID.';
            }
 
            // Free the mysqli resources
            @mysqli_free_result($result);
        }
        else {
            echo "Error! Query failed: <pre>{$dbLink->error}</pre>";
        }
        @mysqli_close($dbLink);
    }
}
else {
    echo 'Error! No ID was passed.';
}
?>
 

[Atli]

hadinetcat, what is the actual Content-Type sent with the request? Use a development tool like Firebug to examine the request and check out exactly what it is. - If it's printing stuff to the screen, it's not setting the headers correctly. Otherwise you'd just end up with a corrupt PDF download.

[hadinetcat]

Atli, the download work fine in firefox mozilla and google chrome but not in IE 10. i have tried everything i also tried

 if(strstr($_SERVER["HTTP_USER_AGENT"],"MSIE")==false) {
 

but not helping.. im still getting garbage when using IE 10.

[Atli]

Well, garbage begets garbage... :)

Seriously though, you have not yet answered the question I posed in my last post. What content type is the browser receiving?! That is the key piece of info needed here. - All the browsers, including IE10, have developer tools that can tell you that. Use them!

Also, I don't know what exactly you think that line of code is meant to do, but I can't see any reason why you'd use that in this context. In fact, browser sniffing code is generally a bad idea. It should be avoided at all costs!

[hadinetcat]

Atli, i used ieHTTPHeaders to examine the header but it show empty in IE 10, also i used Firebug in firefox mozilla it show Content-Type: application/pdf which is working fine, but not in IE 10.. what shell i do next ?? to fix my problem

[jdetmold]

Thanks for the code!
I am however getting the error "file was not sent" very often and only with specific files any idea why? I am currently testing with your code completely unedited with a server on my lan.

for example I get this error with any .cr2 file (canon raw image file) some .pdf's

I also get "An error accured while the file was being uploaded. Error code: 1" with many files but cant figure out what error code 1 means :(

any help would be greatly appreciated!

edit: it seems to be any file larger than about 2mb in size that throw one of the two errors so far I cant find any other pattern

[Atli]

The manual entry on uploads should be your first stop. (In fact it's pretty much the result returned by Google when asking it any query about PHP file uploads.) - The very first paragraph in the Common Pitfalls section explain the problem, as does the description of error code 1 in the Error Messages Explained section.

[tnshum]

I try the code, it works.
However, when I try to upload the file up to 1MB, it wil has an error as below:
Error! Failed to insert the file
MySQL server has gone away
I want to know how to upload more than 1MB file to mysql?

[shayar]

where upload file stored in the computer?

[Atli]

@shayar. To answer that, let me quote you the title of this article:

Uploading files into a MySQL database using PHP

If you are fuzzy on the above concept - versus the normal method of storing files on the file-system - then I strongly suggest you do not use this technique. It is not something you should do without understanding what it is doing and why it's being done that way.

[jp26198926]

hi.. thank you for posting this tutorial, in my case it works in almost all files except for fetching the image (jpg format) from database.

[granthought]

thanks! this is very helpful. =)

[ucman123]

hi,

when I download file, there is entire html code in my download file. My code is same as in article above so I don't know where the problem would be, so please help.

[rsoni]

Thank you so much...!!

[ahmad233]

pls i got dis error "Got a packet bigger than 'max_allowed_packet' bytes," wat can i do

[Atli]

MySQL limits the max size of packets it can receive, using the max_allowed_packet directive. Depending on your server version, it can be either 1MB or 4MB by default.

If you want to submit binary files larger than that, you need to change this to a higher value in your MySQL server's configuration file. How exactly you do that depends on your platform. I suggest you use Google to find that out; it's a common question that has already been answered a few thousand times out there.

[ahmad233]

i have change the default max_packet_allowed to 128mbbut still received same error

[jakai]

Sir, In get_file.php i got this error The ID is invalid!

[RiderRay2050]

hey atil , bro u helped a lot .... the files in the database are viewing i.e working well till #53 but error is coming in the download part....i.e after #56
it always show no ID was passed

 <?php
    // Connect to the database
    //mysql_connect(""localhost","root",""") = $dblink   
    $dbLink = new mysqli("localhost", "root", "", 'iitm');
    if(mysqli_connect_errno()) {
        die("MySQL connection failed: ". mysqli_connect_error());
    }
 
    // Query for a list of all existing files
    $sql = 'SELECT `file_id`, `file_name` FROM `files`';
    $result = $dbLink->query($sql);
 
    // Check if it was successfull
    if($result) {
        // Make sure there are some files in there
        if($result->num_rows == 0) {
            echo '<p>There are no files in the database</p>';
        }
        else {
            // Print the top of a table
            echo '<table width="100%">
                    <tr>
                        <td><b>Name</b></td>
 
 
                    </tr>';
 
            // Print each file
            while($row = $result->fetch_assoc()) {
                echo "
                    <tr>
                        <td>{$row['file_name']}</td>
 
                        <td><a href='get_file.php?id={$row['file_id']}'>Download</a></td>
                    </tr>";
            }
 
            // Close table
            echo '</table>';
        }
 
        // Free the result
        $result->free();
    }
    else
    {
        echo 'Error! SQL query failed:';
        echo "<pre>{$dbLink->error}</pre>";
    }
 
    // Close the mysql connection
    $dbLink->close();
    ?>
 
 
 
    <?php
    // Make sure an ID was passed
    if(isset($_GET['file_id'])) {
    // Get the ID
        $id = intval($_GET['file_id']);
 
        // Make sure the ID is in fact a valid ID
        if($id <= 0) {
            die('The ID is invalid!');
        }
        else {
            // Connect to the database
            $dbLink = new mysqli("localhost", "root", "", 'iitm');
            if(mysqli_connect_errno()) {
                die("MySQL connection failed: ". mysqli_connect_error());
            }
 
            // Fetch the file information
            $query = "
                SELECT `name`
                FROM `files`
                WHERE `file_id` = {$id}";
            $result = $dbLink->query($query);
 
            if($result) {
                // Make sure the result is valid
                if($result->num_rows == 1) {
                // Get the row
                    $row = mysqli_fetch_assoc($result);
 
                    // Print headers
                   while (@ob_end_clean());
                    header("Content-Disposition: attachment; filename=". $row['file_name']);
 
                    // Print data
                    echo $row['file'];
                }
                else {
                    echo 'Error! No file exists with that ID.';
                }
 
                // Free the mysqli resources
                @mysqli_free_result($result);
            }
            else {
                echo "Error! Query failed: <pre>{$dbLink->error}</pre>";
            }
            @mysqli_close($dbLink);
        }
    }
    else {
        echo 'Error! No ID was passed.';
    }
    ?>
 

[Ravi Yadav]

Finally Got after searching all websites in 2 days thank yu dude....

[thanhthupro]

hello everyone can ask themselves how the images from the database to the screen from the addition of database files his (her) Atli

[jumper]

<?php

// Make sure an ID was passed
if(isset($_GET['id'])) {
// Get the ID
$id = intval($_GET['id']);

// Make sure the ID is in fact a valid ID
if($id <= 0) {
die('The ID is invalid!');
}
else {
// Connect to the database
$dbLink = new mysqli("127.0.0.1", "", "", "");
if(mysqli_connect_errno()) {
die("MySQL connection failed: ". mysqli_connect_error());
}

// Fetch the file information
$query = "
SELECT `mime`, `name`, `size`, `data`
FROM `file`
WHERE `id` = {$id}";
$result = $dbLink->query($query);

if($result) {
// Make sure the result is valid
if($result->num_rows == 1) {

// Get the row
// $row = mysqli_num_rows($result);

// Print headers
header("Content-Type: ". $row['mime']);
header("Content-Length: ". $row['size']);
header("Content-Disposition: attachment; filename=". $row['name']);
echo $name;
// Print data
echo $row['data'];
}
else {
echo 'Error! No image exists with that ID.';
}

// Free the mysqli resources
@mysqli_free_result($result);
}
else {
echo "Error! Query failed: <pre>{$dbLink->error}</pre>";
}
@mysqli_close($dbLink);
}
}
else {
echo 'Error! No ID was passed.';
}
?>

Please help me to run this code GET FILES
I got an error

Error! No image exists with that ID.

[Atli]

Have you made sure that the image actually does exist in the database?

[delvin]

Hi Atli,

i'm using your code and it's working. but i have a probrem when i download the file and i open it, my file isn'n like before. now my file already convret with html tag.

how can i fix that?

[Djurres]

Dear Atli,

Im using your code aswell, but I got a few problems.

The first file I upload there is no problem with, Its pasted in the DB, and I can download and see it on the files page.

After I uploaded the Second file I do get this error,

Error! No image exists with that ID.

If you want I can paste the code's here, but i copied these one on one, and the only things I did change are the tablenames, and I customized the login settings!

Thanks in advance :)

[Djurres]

Just to be sure, Code's over here. I deleted my DB login files!

get_file.php

<?php
    // Make sure an ID was passed
    if(isset($_GET['id'])) {
    // Get the ID
        $id = intval($_GET['id']);
 
        // Make sure the ID is in fact a valid ID
        if($id <= 0) {
            die('The ID is invalid!');
        }
        else {
            // Connect to the database
            $dbLink = new mysqli('127.0.0.1', '', '', '');
            if(mysqli_connect_errno()) {
                die("MySQL connection failed: ". mysqli_connect_error());
            }
 
            // Fetch the file information
            $query = "
                SELECT `mime`, `name`, `size`, `data`
                FROM `filejtc`
                WHERE `id` = {$id}";
            $result = $dbLink->query($query);
 
            if($result) {
                // Make sure the result is valid
                if($result->num_rows == 1) {
                // Get the row
                    $row = mysqli_fetch_assoc($result);
 
                  //print headers
                    header("Content-Type: ". $row['mime']);
                    header("Content-Length: ". $row['size']);
                    header("Content-Disposition: attachment; filename=". $row['name']);
 
                    // Print data
                    echo $row['data'];
                }
                else {
                    echo 'Error! No image exists with that ID.';
                }
 
                // Free the mysqli resources
                @mysqli_free_result($result);
            }
            else {
                echo "Error! Query failed: <pre>{$dbLink->error}</pre>";
            }
            @mysqli_close($dbLink);
        }
    }
    else {
        echo 'Error! No ID was passed.';
    }
    ?>
 
 

files.php

<?php
    // Connect to the database
    $dbLink = new mysqli('127.0.0.1', '', '', '');
    if(mysqli_connect_errno()) {
        die("MySQL connection failed: ". mysqli_connect_error());
    }
 
    // Query for a list of all existing files
    $sql = 'SELECT `id`, `name`, `mime`, `size`, `created` FROM `filejtc`';
    $result = $dbLink->query($sql);
 
    // Check if it was successfull
    if($result) {
        // Make sure there are some files in there
        if($result->num_rows == 0) {
            echo '<p>Er zitten nog geen bestanden in de database!</p>';
        }
        else {
            // Print the top of a table
            echo '<table width="100%">
                    <tr>
                        <td><b>Naam:</b></td>
                        <td><b>Soort bestand:</b></td>
                        <td><b>Grootte (bytes)</b></td>
                        <td><b>Aangemaakt</b></td>
                        <td><b>&nbsp;</b></td>
                    </tr>';
 
            // Print each file
            while($row = $result->fetch_assoc()) {
                echo "
                    <tr>
                        <td>{$row['name']}</td>
                        <td>{$row['mime']}</td>
                        <td>{$row['size']}</td>
                        <td>{$row['created']}</td>
                        <td><a href='get_file.php?id={$row['id']}'>Download</a></td>
                    </tr>";
            }
 
            // Close table
            echo '</table>';
        }
 
        // Free the result
        $result->free();
    }
    else
    {
        echo 'Error! SQL query failed:';
        echo "<pre>{$dbLink->error}</pre>";
    }
 
    // Close the mysql connection
    $dbLink->close();
    ?>

add_file.php

    <?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 = new mysqli('', '', '', '');
            if(mysqli_connect_errno()) {
                die("MySQL connection failed: ". mysqli_connect_error());
            }
 
            // Gather all required data
            $name = $dbLink->real_escape_string($_FILES['uploaded_file']['name']);
            $mime = $dbLink->real_escape_string($_FILES['uploaded_file']['type']);
            $data = $dbLink->real_escape_string(file_get_contents($_FILES  ['uploaded_file']['tmp_name']));
            $size = intval($_FILES['uploaded_file']['size']);
 
            // Create the SQL query
            $query = "
                INSERT INTO `filejtc` (
                    `name`, `mime`, `size`, `data`, `created`
                )
                VALUES (
                    '{$name}', '{$mime}', {$size}, '{$data}', NOW()
                )";
 
            // Execute the query
            $result = $dbLink->query($query);
 
            // Check if it was successfull
            if($result) {
                echo 'Succes! Het bestand is toegevoegt aan de database!';
            }
            else {
                echo 'Error! Er is wat fout gegaan bij het invoegen aan de database! Probeer opnieuw!'
                   . "<pre>{$dbLink->error}</pre>";
            }
        }
        else {
            echo 'Er is wat fout gegaan tijdens het uploaden, probeer opnieuw! '
               . 'Error code: '. intval($_FILES['uploaded_file']['error']);
        }
 
        // Close the mysql connection
        $dbLink->close();
    }
    else {
        echo 'Error! Er is geen bestand geselecteerd!';
    }
 
    // Echo a link back to the main page
    echo '<p>Klik <a href="../jtc/files.php">HIER</a> om verder te gaan!</p>';
    ?>

[Atli]

Hey. So the second file is uploaded without error, is listed in the files list with a download link, but then fails to be downloaded saying the ID is invalid?

I see no reason why that would be happening with that code.
Are you sure the image is being uploaded correctly? Have you checked the database entry for it to see if everything is in order?

[Djurres]

I think there I found the problem. I am not uploading images, but .DOC / .ODT / .PDF files.. Is there a possibility to fix these?

I have checked the database after I stored some files, and thats working correctly, so there is no problem with the uploading, there only is a problem with get_file.php

By the way, I have got one table with multiple uploaded files, over 8 files or so, and I still get this error, so after the first upload it isnt working anymore.

[Atli]

There is nothing in the code that's specific to images, a part from the error message. In fact, the error message is wrong to specify it as an image. - I originally wrong this as an image upload script, but made it more general for the article. I seem to have overlooked rewording the error message.

It should work fine with any file type. I tested my original code with a bunch of non-image types. The mime type it provides during the download is the same type it gets from the browser during the upload.

Can you show us the URL of a download link that fails, and the corresponding row in the database?

[Djurres]

There we go, this is where it goes wrong, with 2 files in the database. As I see the ID is right, even as the ID of the database is right!

The template plus files.php
http://puu.sh/aVkEb/b9afc3edbb.png

http://revival.huizevisser.nl/rac/get_file.php?id=5 <-- download url
http://puu.sh/aVkGM/995b01ca53.png

phpmyadmin
http://puu.sh/aVkJL/cd69655364.png

__________________________

I think I found the problem.
Didnt fixed it yet as I don't know how, but when i have 3 .txt documents in a database, it keeps working, BUT, when i add a .doc/.docx file, its gonna mess up and stop working.

Preview:
files.php
http://puu.sh/aVlg7/2221694563.png

on click download
http://puu.sh/aVlh1/92b406d684.png

Add .DOCX file (Jaarverslag RAC 2014.docx)
http://puu.sh/aVliE/e69c645322.png

Try to download .DOCX or a txt file,
http://puu.sh/aVlsW/d8a1892f8b.png

database:
http://puu.sh/aVluw/072c4390dd.png

Hope this is enough information!
skype: gekke_dj

[Atli]

Might have something to do with the size of the files. The DOCX file in there is significantly larger than the text files.

Try calling $result->store_result(); right after the if ($result) in the get_file script.

I usually prefer PDO over MySQLi, so I'm not certain about this, but according to the MySQLi docs the num_rows function may not work correctly unless you store the result. If there is a size barrier on what it automatically stores and what it doesn't, the larger size of the DOC/DOCX/PDF files may be causing the num_rows function to return 0 even when there is data, thus causing this error.