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.

mysql_blob

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,

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

frmImageUpload.png

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

mysql_blob_output

 

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 '’;
    }?>

Leave a Reply

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

↑ Back to Top

Share this Article