4 Steps To Store & Retrieve Images In Database With 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 some images that just have to be stored in the database. Yes, it is possible to do so, and actually relatively easy.

To store and retrieve images in the database with PHP and MySQL:

  1. Create a database table with a BLOB field to store the image.
  2. Create a file upload script, directly insert the raw image data into the database.
  3. Finally, retrieve the image data from the table and output as-it-is.

Just how is this done exactly? 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.

 

 

QUICK SLIDES

 

TABLE OF CONTENTS

Download & Notes Store & Retrieve Useful Bits & Links
Tutorial Video The End

 

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-connect-db.php to your own.
  • Launch 3-upload.php and upload the test potato.jpg image file.
  • Launch 4a-retrieve.php and 4b-retrieve 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.

 

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 IMAGE IN DATABASE

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

 

STEP 1) CREATE A DATABASE TABLE

1-database.sql
CREATE TABLE `images` (
  `img_name` varchar(255) NOT NULL,
  `img_data` longblob NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
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) PHP CONNECT TO DATABASE

2-connect-db.php
<?php
// (A) 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", "");

// (B) CONNECT TO DATABASE
try {
  $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()); }

Next, we have a simple PHP script that connects to the database using PDO. I am doing this to simplify things, but it is actually a pretty bad practice for big projects… You may want to keep this in a proper class library in your own project.

P.S. Remember to change the database settings to your own.

 

 

STEP 3) UPLOAD IMAGE TO DATABASE

3-upload.php
<!-- (A) UPLOAD FORM -->
<form method="post" enctype="multipart/form-data">
  <input type="file" name="upload" accept=".png,.gif,.jpg,.webp" required>
  <input type="submit" name="submit" value="Upload Image">
</form>

<?php
// (B) SAVE IMAGE INTO DATABASE
if (isset($_FILES["upload"])) {
  try {
    // (B1) CONNECT To DATABASE
    require "2-connect-db.php";

    // (B2) READ IMAGE FILE & INSERT
    $stmt = $pdo->prepare("INSERT INTO `images` (`img_name`, `img_data`) VALUES (?,?)");
    $stmt->execute([$_FILES["upload"]["name"], file_get_contents($_FILES["upload"]["tmp_name"])]);
    echo "OK";
  } catch (Exception $ex) { echo $ex->getMessage(); }
}
?>

This should be pretty straightforward.

  1. Just a regular HTML file upload form.
  2. On image file upload, we connect to the database and do a direct SQL INSERT. Yes, take note, we directly read the uploaded image and insert the raw data – file_get_contents($_FILES['upload']['tmp_name'])

 

 

STEP 4) RETRIEVE & SHOW THE IMAGE

DIRECT IMAGE OUTPUT

4a-retrieve.php
<?php
// (A) CONNECT TO DATABASE
require "2-connect-db.php";
 
// (B) GET IMAGE FROM DATABASE
$name = "potato.jpg";
$stmt = $pdo->prepare("SELECT `img_data` FROM `images` WHERE `img_name`=?");
$stmt->execute([$name]);
$img = $stmt->fetch();
$img = $img["img_data"];
 
// (C) OUTPUT IMAGE
$ext = pathinfo($name, PATHINFO_EXTENSION);
if ($ext=="jpg") { $ext = "jpeg"; }
header("Content-type: image/" . $ext);
echo $img;

To retrieve the image, we do the usual SQL SELECT. But take note, since this is the Internet, we output an HTTP mime-type header Content-type: image/TYPE before the image data.

 

OUTPUT TO HTML IMAGE TAG

4a-retrieve.php
<?php
// (A) CONNECT TO DATABASE
require "2-connect-db.php";
 
// (B) GET IMAGE FROM DATABASE
$name = "potato.jpg";
$stmt = $pdo->prepare("SELECT `img_data` FROM `images` WHERE `img_name`=?");
$stmt->execute([$name]);
$img = $stmt->fetch();
$img = $img["img_data"];

// (C) BASE 64 ENCODE TO OUTPUT TO <IMG> TAG
$img = base64_encode($img);
$ext = pathinfo($name, PATHINFO_EXTENSION);
echo "<img src='data:image/".$ext.";base64,".$img."'/>";
?>

Finally, we can also directly output the image data into an <img> tag. Just do a base64_encode() on the image data, and insert it into src="data:image/TYPE;base64,DATA".

 

 

USEFUL 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";
$ext = pathinfo($file, PATHINFO_EXTENSION);
$img = base64_encode(file_get_contents($file));
echo "<img src='data:image/jpg;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

Looking for a more elegant way to upload the images? Here are a few plugins you can check out:

 

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!

8 thoughts on “4 Steps To Store & Retrieve Images In Database With PHP MySQL”

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

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

  3. 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!

  4. 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 *