MySQL BLOB using PHP
Last modified on May 8th, 2018 by Vincy.
BLOB is a kind of MySQL datatype referred as Binary Large Objects. As its name, it is used to store huge volume of data as binary strings as similar as 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 and the SQL script is,
output_images.sql
CREATE TABLE IF NOT EXISTS `output_images` ( `imageId` tinyint(3) NOT NULL AUTO_INCREMENT, `imageType` varchar(25) NOT NULL DEFAULT '', `imageData` mediumblob NOT NULL, PRIMARY KEY (`imageId`) )
Insert Image as MySQL BLOB
To insert an image into MySQL BLOB column the steps are,
- Upload image file.
- Get image properties (image data, image type and more.)
- Insert image file into BLOB.
PHP script to insert BLOB data is,
index.php
<?php
if (count($_FILES) > 0) {
if (is_uploaded_file($_FILES['userImage']['tmp_name'])) {
require_once "db.php";
$imgData = addslashes(file_get_contents($_FILES['userImage']['tmp_name']));
$imageProperties = getimageSize($_FILES['userImage']['tmp_name']);
$sql = "INSERT INTO output_images(imageType ,imageData)
VALUES('{$imageProperties['mime']}', '{$imgData}')";
$current_id = mysqli_query($conn, $sql) or die("<b>Error:</b> Problem on Image Insert<br/>" . mysqli_error($conn));
if (isset($current_id)) {
header("Location: listImages.php");
}
}
}
?>
<HTML>
<HEAD>
<TITLE>Upload Image to MySQL BLOB</TITLE>
<link href="imageStyles.css" rel="stylesheet" type="text/css" />
</HEAD>
<BODY>
<form name="frmImage" enctype="multipart/form-data" action=""
method="post" class="frmImageUpload">
<label>Upload Image File:</label><br /> <input name="userImage"
type="file" class="inputFile" /> <input type="submit"
value="Submit" class="btnSubmit" />
</form>
</div>
</BODY>
</HTML>
After executing this script image upload form will be shown,
On form submit, 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, we have to create a PHP file to do to following.
- get image data and type from MySQL BLOB
- Set the content-type as image (image/jpg, image/gif, …) using PHP header().
- print image content.
imageView.php
<?php
require_once "db.php";
if(isset($_GET['image_id'])) {
$sql = "SELECT imageType,imageData FROM output_images WHERE imageId=" . $_GET['image_id'];
$result = mysqli_query($conn, $sql) or die("<b>Error:</b> Problem on Retrieving Image BLOB<br/>" . mysqli_error($conn));
$row = mysqli_fetch_array($result);
header("Content-type: " . $row["imageType"]);
echo $row["imageData"];
}
mysqli_close($conn);
?>
This PHP code will display MySQL image BLOB data. From HTML image tag we can refer this PHP file with the corresponding image_id as an argument. For example,
<img src="imageView.php?image_id=<?php echo $row["imageId"]; ?>" />
listImages.php
<?php
require_once "db.php";
$sql = "SELECT imageId FROM output_images ORDER BY imageId DESC";
$result = mysqli_query($conn, $sql);
?>
<HTML>
<HEAD>
<TITLE>List BLOB Images</TITLE>
<link href="imageStyles.css" rel="stylesheet" type="text/css" />
</HEAD>
<BODY>
<?php
while($row = mysqli_fetch_array($result)) {
?>
<img src="imageView.php?image_id=<?php echo $row["imageId"]; ?>" /><br/>
<?php
}
mysqli_close($conn);
?>
</BODY>
</HTML>
Output
17 Comments to “MySQL BLOB using PHP”
Leave a Reply
Hi, I’m Vincy, PHP developer. If you have a project that you want to get started, launch quickly, 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.