PHP | Upload BLOB files in MySQL

This repository is an example of upload BLOB files in MySQL using PHP.

HTML form to upload image
Store image file in database as BLOB
Retrieve and display image BLOB from database

Deployed by jlammx

🚀 Live: View deployment

Generally, when we upload image file in PHP, the uploaded image is stored in a directory of the server and the respective image name is stored in the database. At the time of display, the file is retrieved from the server and the image is rendered on the web page. But, if you don’t want to consume the space of the server, the file can be stored in the database only. You can upload an image without storing the file physically on the server using the MySQL database.

If you’re concerned about the server space and need free space on your server, you can insert the image file directly in the database without uploading it to the directory of the server. This procedure helps to optimize the server space because the image file content is stored in the database rather than the server.

The BLOB is a kind of MySQL datatype referred to as Binary Large Objects. As its name, it is used to store a huge volume of data as binary strings similar to MYSQL BINARY and VARBINARY types.

Classification of MySQL BLOB

MySQL BLOB Types	Maximum Storage Length (in bytes)
TINYBLOB	((2^8)-1)
BLOB	((2^16)-1)
MEDIUMBLOB	((2^24)-1)
LONGBLOB	((2^32)-1)

Steps

Create database and table

CREATE DATABASE  IF NOT EXISTS `dev_test`;
USE `dev_test`;
--
-- Table structure for table `tbl_image`
--
DROP TABLE IF EXISTS `tbl_image`;
CREATE TABLE `tbl_image` (
  `imageId` int NOT NULL AUTO_INCREMENT,
  `imageData` longblob,
  `imageName` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `imageType` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `imageSize` varchar(45) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `imageTmpName` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `imageError` varchar(45) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `imageURL` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `upload_on` datetime NOT NULL,
  `status` enum('1','0') CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '1',
  PRIMARY KEY (`imageId`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;