Store and Retrieve Image from MySQL Database using PHP

Updated Date:
PHP
Demo

Generally, when we upload an image file in PHP, the uploaded image is stored in a directory of the server and the respective image name is inserted into the database. But if you want to upload an image without storing on the server, it can be done using MySQL database. If you’re concerned about the server space and need to free space on your server, you can insert an image file in the database without upload it to the directory. This procedure helps to optimize the server space because the image file content is stored in the database rather than the server.

In this tutorial, we will show you how to store the image file into MySQL database and retrieve image from database using PHP. It’s very easy to store and retrieve images from the database using PHP and MySQL.

Insert Image File in MySQL

MySQL has a BLOB (binary large object) data type that can hold a large amount of binary data. The BLOB data type is perfect for storing the image data. In MySQL, four BLOB types are available – TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB.

To store the image data a table needs to be created in the database. The following SQL creates images table with LONGBLOB data type field in the MySQL database. 

CREATE TABLE `images` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `image` longblob NOT NULL,
 `created` datetime NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Image Upload Form

The following HTML form allows users to choose the image file to upload.

<!DOCTYPE html>
<html lang="en">
<body>
    <form action="upload.php" method="post" enctype="multipart/form-data">
        Select image to upload:
        <input type="file" name="image"/>
        <input type="submit" name="submit" value="UPLOAD"/>
    </form>
</body>
</html>

Store Image in Database (upload.php)

The upload.php file contains the following functionalities.

  • Check whether the user selects an image file to upload.
  • Retrieve the content of image file by the tmp_name.
  • Create the connection to MySQL database and select the database.
  • Insert the binary content of the image in the images table.
  • Show the image uploading status to the user.
<?php
if(isset($_POST["submit"])){
    $check = getimagesize($_FILES["image"]["tmp_name"]);
    if($check !== false){
        $image = $_FILES['image']['tmp_name'];
        $imgContent = addslashes(file_get_contents($image));

        /*
         * Insert image data into database
         */
        
        //DB details
        $dbHost     = 'localhost';
        $dbUsername = 'root';
        $dbPassword = '*****';
        $dbName     = 'semicolonworld';
        
        //Create connection and select DB
        $db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);
        
        // Check connection
        if($db->connect_error){
            die("Connection failed: " . $db->connect_error);
        }
        
        $dataTime = date("Y-m-d H:i:s");
        
        //Insert image content into database
        $insert = $db->query("INSERT into images (image, created) VALUES ('$imgContent', '$dataTime')");
        if($insert){
            echo "File uploaded successfully.";
        }else{
            echo "File upload failed, please try again.";
        } 
    }else{
        echo "Please select an image file to upload.";
    }
}
?>

Retrieve image from database (view.php)

In this file, we will retrieve the image content from the MySQL database based on the ID and display on the web page. To render image file in the web page, the Content-type header is used.

<?php
if(!empty($_GET['id'])){
    //DB details
    $dbHost     = 'localhost';
    $dbUsername = 'root';
    $dbPassword = '*****';
    $dbName     = 'semicolonworld';
    
    //Create connection and select DB
    $db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);
    
    //Check connection
    if($db->connect_error){
       die("Connection failed: " . $db->connect_error);
    }
    
    //Get image data from database
    $result = $db->query("SELECT image FROM images WHERE id = {$_GET['id']}");
    
    if($result->num_rows > 0){
        $imgData = $result->fetch_assoc();
        
        //Render image
        header("Content-type: image/jpg"); 
        echo $imgData['image']; 
    }else{
        echo 'Image not found...';
    }
}
?>

Related Articles

Comments 0

Post Comment