Store and Retrieve Image from Database using PHP MySQL

Hi! In this tutorial let's see how to store and retrieve image from mysql database using php. Have you ever tried to store images in a database? If not then we'll fix it now. Once upon a time it's been a developer's dream to save images in database just like any other numbers and texts. But thanks to the evolvement of relational databases, saving and fetching images to and from database is made possible these days. And the process is also less complicated than storing uploaded images to some folder on server.

Storing Image in MySQL Database

So, the question is how can you store images in mysql database? Well, using BLOB data type. BLOB stands for binary large data object and used to store large amount of binary data. Using this you can not only store pictures but also audio and other multimedia files in database.

Basically there are four types of BLOB available in mysql. They are:

TINYBLOB
BLOB
MEDIUMBLOB
LONGBLOB

All of these data types are similar except for the size of data they can hold.

Come let me show you how to store and display image from database using this BLOB type.

Read Also:

First let's create a database and a simple table for storing images.

MySQL Database Creation

CREATE DATABASE IF NOT EXISTS `db_demo`;
USE `db_demo`;
CREATE TABLE IF NOT EXISTS `tbl_gallery` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `imagedata` blob NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

How to Insert Image into MySQL Database using PHP?

So the process goes like this. User uploads image via an uploading form which is then submitted to server. Then the image is converted into binary and inserted into mysql database along with its details such as name and time of creation.

index.php

This file contains the upload form for user to upload images to database.

<!DOCTYPE html>
<html>
<head>
    <title>Insert Image into MySQL Database using PHP</title>
    <meta name="viewport" content="width=device-width, initial-scale=1.0" >
    <link href="css/bootstrap.css" rel="stylesheet" type="text/css" />
</head>
<body>
<br/>
<div class="container">
    <div class="col-xs-8 col-xs-offset-2 well" style="background:none;">
    <form action="upload.php" method="post" enctype="multipart/form-data">
        <legend>Please Choose Image to Upload</legend>
        <div class="form-group">
            <input type="file" name="glryimage" accept="image/*" />
        </div>
        <div class="form-group">
            <input type="submit" name="submit" value="Upload Image" class="btn btn-danger"/>
        </div>
        <?php if(isset($_GET['st'])) { ?>
            <div class="alert alert-danger text-center">
            <?php if ($_GET['st'] == 'success') {
                    echo "Image uploaded successfully!!!";
                }
                else
                {
                    echo 'Error uploading image...';
                } ?>
            </div>
        <?php } ?>
    </form>
    </div>
</div>
</body>
</html>

dbconnect.php

This php file contains the script for database connectivity. Here we establish the connection to mysql database via php code. Please make sure to change the connection settings as per yours.

<?php
//connect mysql database
$hostname = "localhost";
$username = "root";
$password = "";
$database = "db_demo";
$con = mysqli_connect($hostname, $username, $password, $database) or die("Error " . mysqli_error($con));
?>

upload.php

This is the actual uploading script. Here we receive the uploaded file, check if it is a valid image, convert image into binary data and insert image data and details into the database with php code.

<?php
include_once 'dbconnect.php';

//check if form submitted
if (isset($_POST['submit']))
{
    $img_name = $_FILES['glryimage']['name'];

    //upload file
    if ($img_name!='')
    {
        $ext = pathinfo($img_name, PATHINFO_EXTENSION);
        $allowed = ['png', 'gif', 'jpg', 'jpeg'];
    
        //check if it is valid image type
        if (in_array($ext, $allowed))
        {
            $created = @date('Y-m-d H:i:s');

            // read image data into a variable for inserting
            $img_data = addslashes(file_get_contents($_FILES['glryimage']['tmp_name']));
                    
            // insert image into mysql database
            $sql = "INSERT INTO tbl_gallery(name, imagedata, created) VALUES('$img_name', '$img_data', '$created')";
            mysqli_query($con, $sql) or die("Error " . mysqli_error($con));
            header("Location: index.php?st=success");
        }
        else
        {
            header("Location: index.php?st=error");
        }
    }
    else
        header("Location: index.php");
}
?>

Well! We have successfully stored image into database. But how to fetch this image from database and show it a page? We'll see it next.

Retrieve and Display Image from MySQL Database

You can retrieve image from mysql like you do with any other data. For example this script will fetch a specific image from the database and display it in a web page.

Displayimage.php

<?php
include_once 'dbconnect.php';
// fetch image from database
$sql = 'select * from tbl_gallery where id=1'; // change this id as per your need
$result = mysqli_query($con, $sql) or die('Error ' . mysqli_error($con));
$row = mysqli_fetch_array($result);
?>
<html>
<head>
    <title>Retrieve Image from MySQL Database in PHP</title>
    <meta content="width=device-width, initial-scale=1.0" name="viewport" >
    <link rel="stylesheet" href="css/bootstrap.min.css" type="text/css" />
</head>
<body>
    <div class="container text-center">
        <?php echo '<img src="data:image/jpeg;base64,'.base64_encode($row['imagedata']).'"/>'; ?>
    </div>
</body>
</html>

In order to display image stored in a DB to a web page you have to use image tag rather than printing it directly. Please note that we have stored image as binary data. So you must do proper encoding before displaying the picture, else it won't work.

Done! We have everything in place. Now run 'index.php' file and you will be shown an upload form like this,

upload image to mysql database using php

Now select an image and click 'Upload Image' button. If things go right the image will be inserted into database without trouble and you will be notified like this.

To display the stored image from database run the script 'Displayimage.php'. It will show the image you have uploaded previously.

display image from mysql database using php

Please note that so many things can go wrong in the middle. So always remember to do proper error handling in your code.

Read Also:

I'm sure now you have better understanding of uploading and displaying images from database using php and mysql. The entire process is uncomplicated and effective than storing images in some server folder. It also saves you from processing through files to retrieve the images. I hope you like this tutorial. Please don't forget to share it in social media. Meet you in another interesting post :)

Store and Retrieve Image from Database using PHP MySQL

2 comments:

Contact Form