MySQL BLOB using PHP

by Vincy. Last modified on October 11th, 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.

mysql php blob

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,

  1. Upload the image file.
  2. Get image properties (image data, image type, and more.)
  3. 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
    }
}
?>

Download

Comments to “MySQL BLOB using PHP”

  • George Peck III says:

    not working for me, where does if(isset($_GET[‘image_id’])) get set

    • Vincy says:

      Hi George,

      image_id might be empty while requesting page

      imageview.php?image_id=

      Check the db if there is required blob entry.

  • santhosh says:

    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… :))

  • Rahman Surya Praja says:

    How to multiple upload images and store to database in one field, and display it ?

  • manoj says:

    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

  • Oussama says:

    thank you very mutch you help me to discover all of my default in my script… thank again?:)

  • James says:

    Thanks very much for this info, it was just what I needed!

  • betelhem andarge says:

    this was good code to insert image in database but i found error in display time

  • betelhem andarge says:

    wow it’s work thanks

  • isakki thangam says:

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

  • Harry Witriyono says:

    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

  • Karar says:

    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)

  • Mark says:

    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.

  • Tom says:

    I would like to add a 5 file fields undersame record is it possible?

  • Jishnu Murali says:

    Can you please make one with PDO and sqlite ?

  • nibi says:

    <?php while($row=mysqli_fetch_array($res)){
    header('content-type: image/jpeg');

    echo $a=$row['image'];

    echo base64_decode($a);

    // echo '’;
    }?>

  • Mohammad says:

    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 ✌

  • Binchuncha says:

    Thank you very much! It worked like a clock.

  • Diana says:

    Thank you very much!

  • Jürgen says:

    Can I use this tutorial as well when using Angular 12?

  • Jürgen says:

    Hello, I have tried this but did not succeed. Is there a tutorial of yours explaining how to do this with Angular?

    • Vincy says:

      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.

Leave a Reply to James Cancel reply

Your email address will not be published. Required fields are marked *

↑ Back to Top

Share this page