Upload File Into Database With PHP MYSQL (Simple Example)

Welcome to a tutorial on how to upload a file into a database with PHP and MYSQL. Need to upload an entire file into the database for some security reasons? Well, it is possible to do so, but there are some things to consider – Read on for the example!

ⓘ I have included a zip file with all the source code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.

 

 

TABLE OF CONTENTS

 

DOWNLOAD & NOTES

Firstly, here is the download link to the example code as promised.

 

QUICK NOTES

  • Create a database and import 1-database.sql.
  • Change the database settings in 2-lib-store.php to your own.
  • Launch 3-upload.php and upload a file.
If you spot a bug, feel free to comment below. I try to answer short questions too, but it is one person versus the entire world… If you need answers urgently, please check out my list of websites to get help with programming.

 

SCREENSHOT

 

EXAMPLE CODE DOWNLOAD

Click here to download all the example source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

 

UPLOAD FILE INTO DATABASE

All right, let us now get into the example of uploading a file into the database with PHP and MYSQL.

 

PART 1) THE DATABASE

1-database.sql
CREATE TABLE `storage` (
  `file_name` varchar(255) NOT NULL,
  `file_mime` varchar(255) NOT NULL,
  `file_data` longblob NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
ALTER TABLE `storage`
  ADD PRIMARY KEY (`file_name`);

First, to address the elephant in the room. We need a database table to store the files.

  • file_name The file name, primary key.
  • file_mime MIME type. You can index this for “easy search by file type” if you want.
  • file_data The file data itself. Take note that longblob has a limit of 4GB.

 

 

PART 2) PHP LIBRARY

2-lib-store.php
<?php
class Storage {
  // (A) CONSTRUCTOR - CONNECT TO DATABASE
  private $pdo = null;
  private $stmt = null;
  public $error = null;
  function __construct () { try {
    $this->pdo = new PDO(
      "mysql:host=".DB_HOST.";dbname=".DB_NAME.";charset=".DB_CHARSET,
      DB_USER, DB_PASSWORD, [
      PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
      PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    ]);
  } catch (Exception $ex) { exit($ex->getMessage()); }}

  // (B) DESTRUCTOR - CLOSE DATABASE CONNECTION
  function __destruct () {
    if ($this->stmt !== null) { $this->stmt = null; }
    if ($this->pdo !== null) { $this->pdo = null; }
  }

  // (C) HELPER FUNCTION - RUN SQL QUERY
  function query ($sql, $data=null) {
    $this->stmt = $this->pdo->prepare($sql);
    $this->stmt->execute($data);
  }

  // (D) SAVE UPLOADED FILE
  function save () {
    $this->query(
      "INSERT INTO `storage` (`file_name`, `file_mime`, `file_data`) VALUES (?,?,?)",
      [
        $_FILES["upload"]["name"],
        mime_content_type($_FILES["upload"]["tmp_name"]),
        file_get_contents($_FILES["upload"]["tmp_name"])
      ]
    );
    return true;
  }

  // (E) LOAD FILE FROM DATABASE
  function load ($name) {
    // (E1) GET FILE
    $this->query(
      "SELECT `file_mime`, `file_data` FROM `storage` WHERE `file_name`=?", [$name]
    );
    $file = $this->stmt->fetch();

    // (E2) FILE NOT FOUND
    if ($file===false) {
      $this->error = "$name not found";
      return false;
    }

    // (E3) OUTPUT FILE
    // header("Content-type: " . $file["file_mime"]);
    header("Content-Type: application/octet-stream");
    header("Content-Transfer-Encoding: Binary"); 
    header("Content-disposition: attachment; filename=\"". $name ."\""); 
    echo $file["file_data"];
  }

  // (F) GET ALL FILES
  function get () {
    $this->query("SELECT `file_name` FROM `storage`");
    $files = [];
    while ($r = $this->stmt->fetchColumn()) { $files[] = $r; }
    return $files;
  }
}

// (G) DATABASE SETTINGS - CHANGE TO YOUR OWN!
define("DB_HOST", "localhost");
define("DB_NAME", "test");
define("DB_CHARSET", "utf8");
define("DB_USER", "root");
define("DB_PASSWORD", "");

// (H) NEW STORAGE OBJECT
$_STORE = new Storage();

Next, we have a PHP library to work with the database. Looks complicated, but keep calm and look closely.

  • (A, B, H) When $_STORE = new Storage() is created, the constructor connects to the database. The destructor closes the connection.
  • (C) query() is but a simple helper function to run an SQL query.
  • (D, E, F) The “actual” library functions.
    • save() Save an uploaded file into the database.
    • load() Load the specified file (force download).
    • get() Get a list of all files in the database.
  • (G) Self-explanatory. Change the settings to your own.

 

 

PART 3) HTML UPLOAD PAGE

3-upload.php
<?php
// (A) SAVE IMAGE INTO DATABASE
if (isset($_FILES["upload"])) {
  require "2-lib-store.php";
  echo "<div class='note'>". 
    ($_STORE->save() ? "OK" : $_STORE->error)
  ."</div>";
}
?>
 
<!-- (B) HTML FILE UPLOAD FORM -->
<form method="post" enctype="multipart/form-data">
  <input type="file" name="upload" required>
  <input type="submit" name="submit" value="Upload File">
</form>

It is easier to read this page “upside down”.

  • (B) An HTML file upload form.
  • (A) When the form is submitted, use the PHP library to save the uploaded file into the database.

 

PART 4) LOADING THE FILE

4A) SELECT A FILE

4a-retrieve.php
<?php
// (A) GET FILES
require "2-lib-store.php";
$files = $_STORE->get();
?>
 
<form method="post" action="4b-retrieve.php" target="_blank">
  <select name="file"><?php
  foreach ($files as $f) {
    echo "<option>$f</option>";
  }
  ?></select>
  <input type="submit" value="Download">
</form>

To retrieve a file from the database – Here is a dummy page to pick a file first.

 

 

4B) DOWNLOAD THE SELECTED FILE

4b-retrieve.php
// (A) GET FILE & FORCE DOWNLOAD
require "2-lib-store.php";
if (isset($_POST["file"])) {
  if (!$_STORE->load($_POST["file"])) { echo $_STORE->error; }
}

Lastly, just fetch the file from the database and output it.

 

EXTRA BITS & LINKS

That’s all for the tutorial, and here is a small section on some extras and links that may be useful to you.

 

IT WORKS. BUT NOT QUITE AS YOU WOULD EXPECT.

Of course, it is possible to store files in a database as raw binary data. But there is a bigger problem here, MYSQL is not really made to handle large files and massive amounts of data. That is:

  • Try uploading a massive file in one session, it times out. Although there are funky ways to do chunking with the database, it is going to be a painful process.
  • Try fetching a massive file from the database into the memory at once, you will run into performance issues.
  • At the time of writing, there is no such thing as “fetch as a data stream from the database”; There is no way to read a massive file in the database “chunk by chunk”.

So yeah. Uploading files into the database is not really a smart move unless you really need it for some reason.

 

LINKS & REFERENCES

 

THE END

Thank you for reading, and we have come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!

Leave a Comment

Your email address will not be published. Required fields are marked *