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 security reasons? Well, it is possible to do so, but there are some things to consider – Read on for the example!

 

 

TABLE OF CONTENTS

 

DOWNLOAD & NOTES

Here is the download link to the example code, so you don’t have to copy-paste everything.

 

EXAMPLE CODE DOWNLOAD

Source code on GitHub Gist

Just click on “download zip” or do a git clone. I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

SORRY FOR THE ADS...

But someone has to pay the bills, and sponsors are paying for it. I insist on not turning Code Boxx into a "paid scripts" business, and I don't "block people with Adblock". Every little bit of support helps.

Buy Me A Coffee Code Boxx eBooks

 

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 () {
    $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
    ]);
  }

  // (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) : void {
    $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", "utf8mb4");
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.

 

EXTRAS

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!

2 thoughts on “Upload File Into Database With PHP MYSQL (Simple Example)”

Comments are closed.