How Store and Display Image from MySQL Database using PHP

In the tutorial, I will explain to you how to store and retrieve images from a database using PHP. Generally, when we upload an image, it is stored in a particular directory on the server, and the image name is stored in the database. At the time of display, we are retrieving a file from the server, and the image is rendered on the web page. But if you don’t want to store images and reduce the space on the server, the image file can be stored in the database only. You can upload an image without storing any image file physically on the server using the MySQL database. It’s very easy to store and display images from the database using PHP and MySQL.

If you’re worried about the server space and need free space on your server, you can store the image file directly in the database without uploading it to the server directory itself. This procedure helps to optimise the server space because the image file content is stored in the database rather than the server. In this example, I will show you how to store image files in the MySQL database and retrieve images from the database using PHP and MySQL.

Store Image File in MySQL

MySQL has a BLOB (binary large object) data type that can hold a large number of binary data. BLOB data type is a binary large object that can hold a large amount of data. A BLOB is typically used to store binary data:’

1. TINYBLOB
2. BLOB
3. MEDIUMBLOB
4. LONGBLOB

The main difference between all above types is the length of the respective data can be saved.

Create Database Table

To store the file data, a table is required in the database. The following SQL query creates an images table with the LONGBLOB data type field in the MySQL database.

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

Image Upload HTML Form

Create an HTML form with a file input field to select an image file from the device to upload.

• method="post"
• enctype="multipart/form-data"
• acti>

 <form action="save.php" method="post" enctype="multipart/form-data">
    <label>Upload Image File:</label>
    <input type="file" name="image">
    <input type="submit" name="submit" value="Save">
</form>

Save Image in Database Table

The save.php file handles the image upload and record insertion process.

Another way to insert an image in the MySQL database is to store it in the table itself. Images size can be quite bigger, sometimes bigger than 1 or 2MB. So, storing images in a database can put extra load on your database server and the network between your database and your web server if they're on separate hosts.

In this approach, the image files can be challenging to manage to store in the database. You have to first restore them from the database before other operations are to be performed.

There are some exceptions where the entire database is stored in RAM. This means that your database image files are converted to blobs, embedded into a database, and then kept on a disk spece. We can avoid a lot of problems by simply storing them on a disk space as mentioned in the first approach.

Now store the Image in the database. The image column stores the image in the table using the data type as LONGBLOB.

<?php 
// Include the database config file  
require_once 'dbConfig.php'; 
$message = "";
if(isset($_POST["submit"])){     
    if(!empty($_FILES["image"]["name"])) { 
        // Get file information
        $fileName = basename($_FILES["image"]["name"]); 
        $fileType = pathinfo($fileName, PATHINFO_EXTENSION); 
         
        // Allow file formats 
        $allowTypes = array('jpg','png','jpeg','gif'); 
        if(in_array($fileType, $allowTypes)){ 
            $image = $_FILES['image']['tmp_name']; 
            $imgData = addslashes(file_get_contents($image)); 
         
            // Store image content into database 
            $insert = $db->query("INSERT into images (image, created) VALUES ('$imgData', NOW())"); 
             
            if($insert){ 
                $message = "File has been uploaded successfully."; 
            }else{ 
                $message = "File upload failed, please try again."; 
            }  
        }else{ 
            $message = 'Sorry, Uploaded Image file formate is not allowed. Only JPG, JPEG, PNG, & GIF files are allowed to upload.'; 
        } 
    }else{ 
        $message = 'Please select a file to upload.'; 
    } 
} 
 
echo $message; 
?>

Display image from database 

In the index.php file, we will display the image from the MySQL database and list them on the web page

<?php 
// Include the database config file  
require_once 'dbConfig.php'; 
 
// Get data from database 
$result = $db->query("SELECT * FROM images"); 
?>

<?php if($result->num_rows > 0){ ?> 
    
 
        <?php while($row = $result->fetch_assoc()){ ?> 
            <img src="data:image/jpg;charset=utf8;base64,<?php echo base64_encode($row['image']); ?>" />
        <?php } ?> 
    
 
<?php }else{ ?> 
    <p class="status error">Data not found...</p> 
<?php } ?>