Pb28 MySQL BLOB using PHP
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) |
In this MySQL tutorial lets us learn to insert and read MySQL BLOB using PHP.
To start with, we need to create a MySQL table with a BLOB column. The SQL script is,
CREATE TABLE `tbl_image` (
`imageId` int(11) NOT NULL,
`imageType` varchar(255) NOT NULL,
`imageData` longblob NOT NULL
);
--
-- Indexes for dumped tables
--
--
-- Indexes for table `tbl_image`
--
ALTER TABLE `tbl_image`
ADD PRIMARY KEY (`imageId`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `tbl_image`
--
ALTER TABLE `tbl_image`
MODIFY `imageId` int(11) NOT NULL AUTO_INCREMENT;
Insert Image as MySQL BLOB
To insert an image into the MySQL BLOB column the steps are,
- Upload the image file.
- Get image properties (image data, image type, and more.)
- Insert the image file into a BLOB.
PHP script to insert BLOB data is,
<?php
require_once __DIR__ . '/db.php';
if (count($_FILES) > 0) {
if (is_uploaded_file($_FILES['userImage']['tmp_name'])) {
$imgData = file_get_contents($_FILES['userImage']['tmp_name']);
$imgType = $_FILES['userImage']['type'];
$sql = "INSERT INTO tbl_image(imageType ,imageData) VALUES(?, ?)";
$statement = $conn->prepare($sql);
$statement->bind_param('ss', $imgType, $imgData);
$current_id = $statement->execute() or die("<b>Error:</b> Problem on Image Insert<br/>" . mysqli_connect_error());
}
}
?>
<HTML>
<HEAD>
<TITLE>Upload Image to MySQL BLOB</TITLE>
<link href="css/form.css" rel="stylesheet" type="text/css" />
<link href="css/style.css" rel="stylesheet" type="text/css" />
<style>
.image-gallery {
text-align:center;
}
.image-gallery img {
padding: 3px;
box-shadow: 0 1px 3px rgba(0, 0, 0, 0.5);
border: 1px solid #FFFFFF;
border-radius: 4px;
margin: 20px;
}
</style>
</HEAD>
<BODY>
<form name="frmImage" enctype="multipart/form-data" action=""
method="post">
<div class="Pb28 Master-container tile-container">
<label>Upload Image File:</label>
<div class="row">
<input name="userImage" type="file" class="full-width" />
</div>
<div class="row">
<input type="submit" value="Submit" />
</div>
</div>
<div class="image-gallery">
<?php require_once __DIR__ . '/listImages.php'; ?>
</div>
</form>
</BODY>
</HTML>
On form submission, this PHP code gets the content of the image file and stores it into the MySQL BLOB column as binary data.
Read Image BLOB to Display
For displaying BLOB images to the browser, create a PHP file and to do the following. This file will then be used as the <img> tag source to display the images into the browser.
- Get image data stored with the MySQL BLOB field in the database.
- Set the content-type as image (image/jpg, image/gif, …) using PHP header().
- Print image blob data in PHP.
<?php
require_once __DIR__ . '/db.php';
if (isset($_GET['image_id'])) {
$sql = "SELECT imageType,imageData FROM tbl_image WHERE imageId=?";
$statement = $conn->prepare($sql);
$statement->bind_param("i", $_GET['image_id']);
$statement->execute() or die("<b>Error:</b> Problem on Retrieving Image BLOB<br/>" . mysqli_connect_error());
$result = $statement->get_result();
$row = $result->fetch_assoc();
header("Content-type: " . $row["imageType"]);
echo $row["imageData"];
}
?>
This PHP code will display MySQL image BLOB data. From the HTML image tag we can refer to this PHP file with the corresponding image_id as an argument. For example,
<img src="imageView.php?image_id=<?php echo $row["imageId"]; ?>" />
The db.php file has the script to connect the database. It has the connection object variable which is commonly referred to in all pages performing database operations.
<?php
$conn = mysqli_connect("localhost", "root", "", "db_add_blob");
?>
Display uploaded blob images in a gallery
The llistImages.php file is included in the landing page below the uploading form UI.
It displays the image gallery from the database. The gallery image element refers the record id to a PHP file to get the blob data.
<?php
$sql = "SELECT imageId FROM tbl_image ORDER BY imageId DESC";
$stmt = $conn->prepare($sql);
$stmt->execute();
$result = $stmt->get_result();
?>
<?php
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
?>
<?php ?>
<img src="imageView.php?image_id=<?php echo $row["imageId"];?>">
<?php
}
}
?>