MySQL BLOB using PHP
by Vincy. Last modified on September 27th, 2022.
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,
structure.sql
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,
index.php
<?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="phppot-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.
imageView.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.
listImages.php
<?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
}
}
?>
Comments to “MySQL BLOB using PHP”
Leave a Reply
Hi, I’m Vincy. I help build websites, grow businesses, big and small. If that is what you want, contact me.
I’m currently available for freelance work.
not working for me, where does if(isset($_GET[‘image_id’])) get set
Hi George,
image_id might be empty while requesting page
imageview.php?image_id=
Check the db if there is required blob entry.
in “Read Image BLOB to Display”
instead of showing the image there it self, I would like to just display the name of the file, and when clicked it has to be downloaded.
Please give steps for the same… :))
How to multiple upload images and store to database in one field, and display it ?
when i retriving image from database, it show me too many samples, what is the problem
Thanks for this tutorial
i have a different problem
my database consist blob record of pics and i want to transfer pics to a folder (specified by a field in table)
regards
manoj bisht
thank you very mutch you help me to discover all of my default in my script… thank again?:)
Thanks very much for this info, it was just what I needed!
this was good code to insert image in database but i found error in display time
wow it’s work thanks
if i want to do the insert operation from a outer file called insert.php then how do i change this code mam? please let me know when you notice my query….
Dear, Vincy, thank you for your tutorial, it’s help me to improve my php programming skill, i hope we could discuss about php in this forum.
I have test the aplication with hijacked image file that contents php code, and it could not hijacked the web. This is the way that i hope by using the blob database than upload and save the file in a folder.
Bye the way,thank you and God bless you Vincy.
Your new friends from Indonesia,
Harry Witriyono
Hello,
Please Help me for my project, i am student of CS, so i am working on a project. My project title is “Daily K2” ePaper, (online news paper, using jpg in body). so first of all i need interface which i have designed using html and css, now i need Database (wamp server) and php code for inserting news paper image with date, and also display that jpg with date.
i have four pages of news paper with jpg imag in main body which must insert is database in every day by day, and must display with date…..
I also need to facility to display previous papers with date……
Please help me in my project sir
Am waiting for your Message
Karar Barcha
(kararbarcha@gmail.com)
I’ve been searching for a couple days to find an example that shows how to update a longblob. If I use the exact same data to UPDATE in a MySQL statement, I don’t get any results. INSERT has been simple. UPDATE seems impossible.
I would like to add a 5 file fields undersame record is it possible?
Can you please make one with PDO and sqlite ?
Sure Jishnu, I will write an article on PDO and Sqlite soon. Thanks.
<?php while($row=mysqli_fetch_array($res)){
header('content-type: image/jpeg');
echo $a=$row['image'];
echo base64_decode($a);
// echo '’;
}?>
Thank You So much,
With some tweaks here and there I was able to complete my school assignment. This is the best tutorial I’ve ever seen for ‘Inserting Images in PHP’
Would definitely recommend it to others
Thank You Vincy ✌
Wow, thank you Mohammad.
Thank you very much! It worked like a clock.
Welcome Binchuncha.
Thank you very much!
Welcome Diana.
Can I use this tutorial as well when using Angular 12?
Hi Jürgen,
Sure you can use it but needs to be adapted to Angular.
Hello, I have tried this but did not succeed. Is there a tutorial of yours explaining how to do this with Angular?
Hi Jürgen,
Let me know what issue you are facing and I will try to help you out. I will try to write the same on Angular soon.