3 Steps To Store & Retrieve Images In Database (PHP MySQL)

Welcome to a tutorial on how to store and retrieve images in an MYSQL database with PHP. So for some reason, you have to store some images in a database. Yes, it is possible to do so, and actually relatively easy. Let us walk through a simple example – Read on!

ⓘ 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.

 

TLDR – QUICK SLIDES

Fullscreen Mode – Click Here

 

TABLE OF CONTENTS

 

DOWNLOAD & NOTES

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

 

QUICK NOTES

  • Create a dummy database and import 1-database.sql.
  • Change the database settings in 2-lib.php to your own.
  • Launch 3a-upload.php and upload the test potato.jpg image file.
  • Launch 3b-retrieve.php and 3c-retrieve.php to retrieve the image from the database.
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 the source code in a zip file – I have released it under the MIT License, so feel free to build on top of it if you want to.

 

 

STORE & RETRIEVE IMAGES IN THE DATABASE

All right, let us now get into the example of storing and retrieving an image in the database.

 

PART 1) IMAGE DATABASE

1-database.sql
CREATE TABLE `images` (
  `img_name` varchar(255) NOT NULL,
  `img_data` longblob NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 
ALTER TABLE `images` ADD UNIQUE KEY `img_name` (`img_name`);

Yep, that’s all we need. The img_name field is for the file name and img_data holds the image data itself. But just what is longblob? It is definitely not some kind of sticky stuff even though it sounds like one. BLOB stands for “binary large object”, perfect for storing our uploaded image files. Take note of the file size restriction though:

  • BLOB will only hold up to 64 KB of data.
  • MEDIUMBLOB holds up to 16 MB of data.
  • LONGBLOB up to 4 GB of data.

 

 

STEP 2) DATABASE IMAGE LIBRARY

2-lib.php
<?php
class DBImg {
  // (A) CONSTRUCTOR - CONNECT TO DATABASE
  protected $pdo = null;
  protected $stmt = null;
  public $error = "";
  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 CONNECTION
  function __destruct() {
    if ($this->stmt !== null) { $this->stmt = null; }
    if ($this->pdo !== null) { $this->pdo = null; }
  }
 
  // (C) HELPER - RUN SQL QUERY
  function query ($sql, $data=null) {
    $this->stmt = $this->pdo->prepare($sql);
    $this->stmt->execute($data);
  }
 
  // (D) SAVE IMAGE TO DATABASE
  function save ($name, $data) {
    $this->query("REPLACE INTO `images` (`img_name`, `img_data`) VALUES (?,?)", [$name, $data]);
    return true;
  }
 
  // (E) LOAD IMAGE FROM DATABASE
  // mode 1 = direct output
  // mode 2 = force download
  // mode 3 = base64 encode
  function load ($name, $mode=1) {
    // (E1) GET IMAGE
    $this->query("SELECT `img_data` FROM `images` WHERE `img_name`=?", [$name]);
    $img = $this->stmt->fetchColumn();
    if ($img==false) { return false; }
 
    // (E2) OUTPUT IMAGE
    $mime = mime_content_type($name);
    if ($mode==3) {
      echo "data:$mime;base64," . base64_encode($img);
    } else if ($mode==2) {
      header("Content-Type: application/octet-stream");
      header("Content-Transfer-Encoding: Binary"); 
      header("Content-disposition: attachment; filename=\"$name\""); 
      echo $img;
    } else {
      header("Content-type: $mime");
      echo $img;
    }
  }
}
 
// (F) 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", "");
 
// (G) NEW DBIMG OBJECT
$_DBIMG = new DBImg();

This library may be intimidating to some beginners, but keep calm and look carefully.

  • (A, B, G) When $_DBIMG = new DBImg() is created, the constructor automatically connects to the database. The destructor closes the connection.
  • (C) query() is a helper function to run an SQL query.
  • (D & E) Pretty self-explanatory –
    • save() Save an image file into the database.
    • load() Retrieve an image file from the database.
  • (F) Remember to change the database settings to your own.

 

 

PART 3) UPLOAD & RETRIEVE IMAGE IN DATABASE

3A) UPLOAD INTO THE DATABASE

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

This should be pretty straightforward.

  1. Just a regular HTML file upload form.
  2. On submit, we simply use save() to save the uploaded image into the database. Yes, take note, we directly read the uploaded image file and insert the raw data – file_get_contents($_FILES["upload"]["tmp_name"])

 

 

3B) DIRECT IMAGE OUTPUT & FORCE DOWNLOAD

3b-retrieve.php
<?php
// (A) LOAD LIBRARY
require "2-lib.php";
 
// (B) GET & OUTPUT
$_DBIMG->load("potato.jpg", 1);
// $_DBIMG->load("potato.jpg", 2);

To retrieve the image, we use the load() function – Specify which image file and mode.

  • Mode 1 will directly output the image.
  • Mode 2 will force download the image.

 

3C) OUTPUT TO HTML IMAGE TAG

3c-retrieve.php
<?php require "2-lib.php"; ?>
<img class="myImg" src="<?=$_DBIMG->load("potato.jpg", 3)?>">

If you prefer to directly embed the image into an HTML <img> tag, use load(IMAGE, 3). This will output the image as a base 64 encoded string.

 

 

EXTRA BITS & LINKS

That’s all for this project, and here is a small section on some extra bits that may be useful to you.

 

ALTERNATIVE – UPLOAD TO PRIVATE FOLDER

upload-alt.php
<?php
$private = "PATH/TO/FOLDER/";
move_uploaded_file (
  $_FILES["upload"]["tmp_name"], $private . $_FILES["upload"]["name"
);
show-alt.php
<?php
$file = "PATH/FOLDER/IMAGE.jpg";
$mime = mime_content_type(basename($file));
$img = base64_encode(file_get_contents($file));
echo "<img src='data:$mime;base64,$img'>";

If security is a concern, consider saving the uploaded images to a private folder instead. For example, if the public HTTP folder is located at D:/http/, we save the uploaded image into D:/uploaded/. This way, the uploaded images are not publically accessible, but PHP can still read those image files.

 

LINKS & REFERENCES

 

TUTORIAL VIDEO

 

INFOGRAPHIC CHEAT SHEET

How to store & retrieve images using PHP MySQL (click to enlarge)

 

THE END

Thank you for reading, and we have come to the end of this quick guide. I hope you have found it to be useful, but please do take note that a lot of error checking and security still need to be added to the above skeleton scripts. If you want to share anything with the guide, please feel free to comment below. Good luck and happy coding!

11 thoughts on “3 Steps To Store & Retrieve Images In Database (PHP MySQL)”

  1. Well done. based on the domain-name I have expected usual badly written I* stuff and skipped it at first, but its rare for first tutorial on google to actually be a good one. Maybe it would be handy to add size-checking, but Thanks for quality tutorial. Well done

    EDIT BY WS: Let’s keep racial bias out. I have also written plenty of crappy stuff before getting to this stage… Will keep size checking as a possible future update.

  2. Nicholas Reseburg

    Hello W.S. Toh,
    Thank you for this tutorial. It is very helpful. I was wondering, what if i need to include a foreign key to another table with the potato image record upload?

    I tried modifying the upload code as seen below, but it is not working. Do you have any ideas where i am going wrong? (i did modify the images table to have a bigint field called foreignkey)

    $foreignkey = 55;
    $stmt = $pdo->prepare(“INSERT INTO `images` (`img_name`, `img_data`, ‘foreignkey’) VALUES (?,?,?)”);
    $stmt->execute([$_FILES[“upload”][“name”], file_get_contents($_FILES[“upload”][“tmp_name”]),$foreignkey]);

    thank you in advance

  3. Hi @TOH,
    thanks for showing a really nice and simple way to upload the image, however, i am building user signup and login system and i want users to upload there profile pic after login. searching for so many things I came to this tutorial, it is not only a simple way but also easy to understand as a beginner. now can you tell me like which will be the better way to accomplish the above task, store in database and retrieve img from table or store in a folder. and also as a beginner, i want to understand what is the standard way to achieve this task. i also came to some stack overflow questions that suggested to use a filesystem, or link the path of the image in database, please elaborate on this. thanks in advanced.

    1. There is no “standard way”, it really depends on what you are trying to achieve. If you are building a public forum, saving the profile picture to a folder will do just fine. If you are building a company portal where protecting staff information is important, uploading to the database will make more sense – We can encrypt a database, deploy them on another server behind another layer of firewall.

  4. François Beerten

    Sorry, but I can’t see any photo’s after executing 3-show.html!!
    I can see that the photo is uploaded to the ‘upload’ tabel in my database ‘test’!! How big can the picture be, because I get an error message with a bigger photo??
    I also tried to adjust in ‘1-uploed.html’ line 7 by changing from ‘2-upload.php’ to ‘2a-upload.php’
    and in file ‘3-show.html’ line 7 from ‘4-fetch.php’ to ‘4a-fetch.php’ because I prefer to save the pictures in a private_upload directory. Do I have to create this private-upload directory as follow:?
    e:/wamp64/www/private-upload
    or
    e:/wamp64/private-upload??
    I tried both without succes!! What am I doing wrong??

    grtz paco

    1. Hi!
      1) The image can be as large as the upload limit you set in php.ini – upload_max_filesize and post_max_size.
      2) UPDATE – BLOB in the database only allows 64KB max. Use MEDIUMBLOB (16MB) or LONGBLOB (4GB).
      3) Your error message is probably an “over allowed upload limit”.
      4) Change the ?f=image.jpg parameter in 3-show.html to the filename of your image.
      5) Ignore 2a and 4a. That is an alternative solution for storing into a private folder instead of the database.

      Will make some changes to the tutorial so that it becomes clearer.

  5. Hi, I was unable to access your source code through your download link as it is broken. Can I access it any other way? Please and Thank you!

  6. Hey thanks for this tutorial, i have downloaded and added some codes and make it working, though there are a lot of codes needed to be added, it was very helpful,

Leave a Comment

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