INTRODUCTION DATABASE IMAGES!? Welcome to a step-by-step tutorial on how to store and retrieve images using PHP and MYSQL. I am pretty sure that you have your own reasons to store images in a database, and it is actually a pretty simple 3 steps process: Create a database table with BLOB data field to store the image. Create an HTML file upload form and the respective PHP script to save the image into the database. Finally, a PHP script that will fetch the data from the database and output it as an image. But take note that conventional relational databases are not really optimized for file storage, and I will personally discourage people from storing images in a database. So if you just want somewhere to store images that users upload, just use the regular file system. If there is a concern for privacy, then put the image files into a restricted folder that cannot be accessed publically. Just exactly how does all of this work? Read on to find out how! ⓘ 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.

PREAMBLE DOWNLOAD & NOTES Firstly, here is the download link to the source code as promised.

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.

QUICK NOTES

Download and unzip into a folder. Create a dummy database and import 1-database.sql . Update the database settings in 2b-config.php . Launch 2a-upload.html in your web browser, and upload a test image file. Change the ?f= parameter in 3a-show.html to the file name of the image that you just uploaded. Launch 3a-show.html in the web browser and watch how it retrieves the image from the database.



If you spot a bug, please feel free to comment below. I try to answer 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.

STEP 1 DATABASE SETUP Before we go into the code, let us lay some foundations with the database first – Make sure that you have a table with a column that is set to the “BLOB” data type.

SIMPLE UPLOAD TABLE

1-database.sql

CREATE TABLE `upload` ( `name` varchar(255) NOT NULL, `data` longblob NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ALTER TABLE `upload` ADD UNIQUE KEY `name` (`name`);

Yep, that’s all we need – A name field for the file name, and the actual data itself. But just what is BLOB? It definitely is 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 that:

BLOB will only hold up to 64 KB of data.

will only hold up to 64 KB of data. MEDIUMBLOB holds up to 16 MB of data.

holds up to 16 MB of data. LONGBLOB up to 4 GB of data.

It will be a good idea to restrict the allowed image upload size later, and not break the system.

STEP 2 UPLOAD AND STORE IMAGES Now that we have established the database foundation, it is time to create the actual scripts that will handle the image file upload, then store it into the database.

THE HTML

2a-upload.html

<form action="2c-upload.php" method="post" enctype="multipart/form-data"> <input type="file" name="upFile" id="upFile" accept = ".png,.gif,.jpg,.webp" required> <input type="submit" name="submit" value="Upload Image"> </form>

Start by creating an HTML form to handle the uploading of images. The above is a very simple one, but I will leave some links to file upload plugins that you can use in the extras section below… Just-in-case some of you guys want to add a Ferrari upload engine to your project.

CONFIG FILE

2b-config.php

<?php // ERROR REPORTING error_reporting(E_ALL & ~E_NOTICE); // DATABASE SETTINGS // !! CHANGE THESE TO YOUR OWN !! define('DB_HOST', 'localhost'); define('DB_NAME', 'test'); define('DB_CHARSET', 'utf8'); define('DB_USER', 'root'); define('DB_PASSWORD', ''); // MAX UPLOAD SIZE define('MAX_UP', 16000000); // 16MB

This is just a simple config file, containing the database settings – Please remember to change these to your own.

SERVER-SIDE UPLOAD HANDLER

2c-upload.php

<?php /* [INIT] */ require "2b-config.php"; /* [ERROR CHECKING] */ if ($_FILES['upFile']['size']==0) { die("No file selected"); } if ($_FILES['upFile']['size']>MAX_UP) { die("Exceeded maximum allowed size"); } if (exif_imagetype($_FILES['upFile']['tmp_name'])===false) { die("Not an image"); } /* [CONNECT TO DB] */ $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, PDO::ATTR_EMULATE_PREPARES => false] ); /* [INSERT IMAGE] */ // DO YOUR ERROR CHECKING HERE IF YOU WANT try { // To always override existing, use REPLACE INTO $stmt = $pdo->prepare("INSERT INTO `upload` (`name`, `data`) VALUES (?, ?)"); $stmt->execute([$_FILES["upFile"]["name"], file_get_contents($_FILES['upFile']['tmp_name'])]); } catch (Exception $ex) { echo "ERROR - " . $ex->getMessage(); die(); } /* [ON COMPLETE] */ // DO SOMETHING, MAYBE REDIRECT THE USER TO ANOTHER PAGE // header("Location: http://example.com/abc.php"); echo "OK";

Finally, we shall create the corresponding image upload handler in PHP. This upload handler is pretty straightforward:

Checks for a properly uploaded image and prevent invalid file uploads.

Open a connection to the database. Feel free to use MYSQLI if you are uncomfortable with PDO.

Finally, read the uploaded image file as a BLOB and insert it into the database.

STEP 3 RETRIEVE IMAGES To complete the puzzle, we only have to retrieve the image from the database and use it in our HTML (or any document) in this final step.

THE HTML

3a-show.html

<!-- !! REMEMBER TO CHANGE ?f=IMAGE TO YOUR UPLOADED IMAGE FILE NAME !! --> <img src="3b-fetch.php?f=UPLOADED.jpg">

Retrieving the image from the database is just about as easy. Start with a “regular” <img> tag in your HTML, but point it to a PHP script with a $_GET['f'] parameter to specify which image you want to load.

SERVER-SIDE SCRIPT

3b-fetch.php

<?php /* [INIT] */ require "2b-config.php"; /* [CONNECT TO DB] */ $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, PDO::ATTR_EMULATE_PREPARES => false] ); /* [FETCH IMAGE] */ $stmt = $pdo->prepare("SELECT `data` FROM `upload` WHERE `name`=?"); $stmt->execute(array($_GET['f'])); $img = $stmt->fetch(); /* [OUTPUT] */ // DO MORE ERROR CHECKING & HANDLING HERE IF YOU WANT // NOT FOUND - SHOW BLANK IMAGE if ($img==false) { require "0-not-found.jpg"; } else { echo $img['data']; }

This PHP script simply fetches the BLOB data from the database and outputs it.

If security and privacy is your concern, please do beef up the above script.

For example, verify the user permissions and credentials before fetching the image.

ALTERNATE SOLUTION PRIVATE FOLDER That’s it for the database upload, but remember that we mentioned an alternate solution before? Yes, this is a much simpler version that stores the uploaded images into a private folder instead.

1) CREATE A PRIVATE FOLDER

In most web server installations, we will commonly have public_html or www folder. This is where we upload all the HTML, CSS, Javascript, PHP, images, videos, and publically accessible files. We do not want to create a private folder in here, but instead, place it somewhere that cannot be accessed via the HTTP web server.

2) UPLOAD

4a-upload.php

<?php /* [ERROR CHECKING] */ if ($_FILES['upFile']['size']==0) { die("No file selected"); } if ($_FILES['upFile']['size']>MAX_UP) { die("Exceeded maximum allowed size"); } if (exif_imagetype($_FILES['upFile']['tmp_name'])===false) { die("Not an image"); } // Alternate solution - save the uploaded file into a private folder // !! CHAGE "PATH/TO/PRIVATE/" !! $target = "PATH/TO/PRIVATE/" . basename($_FILES["upFile"]["name"]); if (move_uploaded_file($_FILES["fileToUpload"]["tmp_name"], $target)) { echo "ok"; } else{ echo "error"; }

Next, let us change the file upload script a little. This alternate version should be very straightforward, it simply moves the uploaded file to your specified private folder instead. No database involved.

P.S. Remember to change $target = "PATH/TO/PRIVATE/" to wherever your private folder is located.

3) RETRIEVAL

4b-fetch.php

<?php // Alternate solution - get image from private folder // !! CHAGE "PATH/TO/PRIVATE/" !! $target = "PATH/TO/PRIVATE/" . $_GET['f']; if (!file_exists($target)) { require "not-found.jpg"; } else { echo file_get_contents($target); }

Finally, we have to change the fetch image script as well. This one is also very straightforward – It simply reads the image from the private folder and outputs it.

P.S. Remember to change $target = "PATH/TO/PRIVATE/" again.

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

LINKS & REFERENCES

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

Plupload

Uploadify

Dropzone

Or maybe if you are interested to build your own, Drag-and-drop upload – Code Boxx

EXTRA VIDEO TUTORIAL For you guys who want more, here’s the video tutorial, and shameless self-promotion – Subscribe to the Code Boxx YouTube channel for more!

YOUTUBE TUTORIAL

INFOGRAPHIC CHEAT SHEET