Save and Download BLOB images – PHP and MySQL

In this article, we will show you, how you can save BLOB images in a MySQL database using PHP. You will also be able to download the BLOB images as well. To upload the file to the server and save its path in the MySQL database, you can follow our this tutorial.

Table structure:

Run the following code in your index.php file to create the required table that will hold all saved BLOB images:

<?php
	$conn = mysqli_connect("localhost", "root", "", "blob");
	$sql = "CREATE TABLE IF NOT EXISTS `images` (
		`id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
        `image` LONGBLOB NOT NULL,
		`name` TEXT NOT NULL,
		`type` VARCHAR (11) NOT NULL
	)";
	mysqli_query($conn, $sql);
?>

Laravel developers can do it in the following way:

use Illuminate\Support\Facades\DB;

Schema::create('images', function (Blueprint $table) {
    $table->id();
    $table->longText("name")->nullable();
    $table->longText("type")->nullable();
    // $table->binary("image");
    // OR
    // $table->longText("image")->charset('binary');
    $table->timestamps();
});

DB::statement("ALTER TABLE images ADD image LONGBLOB DEFAULT NULL AFTER id");

Run your file and you will see a new table created in your phpMyAdmin.

Save BLOB images in MySQL:

First, you need to create an input field from which you can upload an image.

<form method="POST" action="upload.php" enctype="multipart/form-data">
	<p>
		<label>Upload Image</label>
		<input type="file" name="image" accept="image/*" required />
	</p>
	<input type="submit" value="Upload" />
</form>

Create a file named upload.php and write the following code in it:

<?php
	// assume you have a database named 'blob'
	$conn = mysqli_connect("localhost", "root", "", "blob");
	$image = $_FILES["image"];
	$info = getimagesize($image["tmp_name"]);
    if(!$info)
	{
		die("File is not an image");
	}
	$name = $image["name"];
    $type = $image["type"];
	$blob = addslashes(file_get_contents($image["tmp_name"]));

    // if you are developing in Laravel
    // $blob = file_get_contents($file->getRealPath());

	$sql = "INSERT INTO `images` (`image`, `name`, `type`) VALUES ('" . $blob . "', '" . $name . "' , '" . $type . "')";
	mysqli_query($conn, $sql) or die(mysqli_error($conn));
    echo "File has been uploaded.";
?>

Run the code now and you will be displayed an input field and a submit button. Select an image file and hit the submit button, if all goes well, you will see a success message. Then you can check in your database, a new row will be created, the image will be saved as a blob file and the name of the image file will be saved in name column.

Show all Saved BLOB images:

To show all saved BLOB files, you need to run the following code:

<?php
	$conn = mysqli_connect("localhost", "root", "", "blob");
	$sql = "SELECT id, name FROM `images` ORDER BY `id` DESC";
	$result = mysqli_query($conn, $sql);
	
	while ($row = mysqli_fetch_object($result))
	{
?>
	<p>
		<a href="download.php?id=<?php echo $row->id; ?>" target="_blank">
		    <?php echo $row->name; ?>
        </a>
	</p>
<?php } ?>

This will display all saved files in descending order (latest to oldest) in paragraphs. It will also display an anchor tag which when clicked will download the file.

Download BLOB images from MySQL:

To download the Blob file, simply create a file named download.php and write the following code in it:

<?php
	$conn = mysqli_connect("localhost", "root", "", "blob");
	$sql = "SELECT * FROM `images` WHERE `id` = " . $_GET["id"];
	$result = mysqli_query($conn, $sql);
	if (mysqli_num_rows($result) == 0)
	{
		die("File does not exists.");
	}
	$row = mysqli_fetch_object($result);
    header("Content-type: " . $row->type);
	echo $row->image;
?>

To convert the BLOB to base64 in PHP, do the following:

$base64 = "data:" . $row->type . ";base64," . base64_encode($row->image);
// output = data:image/jpeg;base64,9j/4AAQSkZJRgABAQAAAQABA...

Go ahead and click the file name from the list, you will see that file opened in a browser in a new tab. You can simply press Ctrl + S to download the file in your system. Using this method, you can also save your files from direct access from the URL. Since the files are not physically saved anywhere on the server, they will not be directly accessible from the URL.

And since the file can only be accessed via PHP function, you can put additional conditions like allowing download files to premium users only, etc.

[wpdm_package id=’1541′]