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,

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

Download

This PHP code tutorial was published on December 30, 2013.

↑ Back to Top