Store and Retrieve Image from MYSQL Database using PHP

Generally, once we upload image enter PHP, the uploaded image is stored during a directory of the server and therefore the respective image name is stored into the database. At the time of display, the file is retrieved from the server and therefore the image is rendered on the online page. But, if you don’t want to consume the space of the server, the file is stored within the database only. You’ll upload a picture without storing it on the server using the MYSQL database. It’s very easy to store and retrieve images from the database using PHP and MYSQL.

If you’re concerned about the server space and wish to free space on your server, you’ll insert a picture enter the database without upload it to the directory of the server. This procedure helps to optimize the server space because the image file content is stored within the database instead of the server. During this tutorial, we’ll show you ways to store image file into the MYSQL database and retrieve images from the database using PHP.

Insert Image file in MYSQL

MYSQL features a BLOB (binary large object) data type which will hold an outsized amount of binary data. The BLOB data type is ideal for storing image data. In MYSQL, four BLOB types are available – TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB.

Create Database Table

To store the file content, a table is required within the database. The following SQL creates an images table with the LONGBLOB data type field within the MYSQL database.

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

Database Configuration (dbconfig.php)

The dbconfig.php file is employed to attach and select the database. Specify the database host ($dbhost), username ($dbusername), password ($dbpassword), and name ($dbname) as per your MYSQL database credentials.

<?Php  
// Database configuration
$dbhost = "localhost";
$dbusername = "root";
$dbpassword = "root";
$dbname = "gpkumardb";

// Create database connection
$db = new mysqli($dbhost, $dbusername, $dbpassword, $dbname);

// Check connection
If ($db->connect_error) {
Die("Connection failed: " . $db->connect_error);
}

Image Upload Form

Create an HTML form with a file input field to pick a picture file for upload. Confirm the <form> tag contains the following attributes.

Method=”post”

Enctype=”multipart/form-data”

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

Tore Image enter Database (upload.php)

The upload.php files handles the image uploads and database insertion process.

  • Check whether the user selects a picture file to upload.
  • Retrieve the content of image file by the tmp_name using PHP file_get_contents() function.
  • Insert the binary content of the image within the database using PHP and MYSQL.
  • Show the image uploading status to the user.
<?Php 
// Include the database configuration file
Require_once 'dbconfig.php';

// If file upload form is submitted
$status = $statusmsg = '';
If(isset($_POST["submit"])){
$status = 'error';
If(!Empty($_FILES["image"]["name"])) {
// Get file info
$filename = basename($_FILES["image"]["name"]);
$filetype = pathinfo($filename, PATHINFO_EXTENSION);

// Allow certain file formats
$allowtypes = array('jpg','png','jpeg','gif');
If(in_array($filetype, $allowtypes)){
$image = $_FILES['image']['tmp_name'];
$imgcontent = addslashes(file_get_contents($image));

// Insert image content into database
$insert = $db->query("INSERT into images (image, uploaded) VALUES ('$imgcontent', NOW())");

If($insert){
$status = 'success';
$statusmsg = "File uploaded successfully.";
}else{
$statusmsg = "File upload failed, please try again.";
}
}else{
$statusmsg = 'Sorry, only JPG, JPEG, PNG, & GIF files are allowed to upload.';
}
}else{
$statusmsg = 'Please select an image file to upload.';
}
}

// Display status message
Echo $statusmsg;
?>

Retrieve image from database (view.php)

In the view.php file, we’ll retrieve the image content from the MYSQL database and list them on the online page.

The data, charset, and base64 parameters within the SRC attribute are wont to display image BLOB from MYSQL database.

<?Php 
// Include the database configuration file
Require_once 'dbconfig.php';

// Get image data from database
$result = $db->query("SELECT image FROM images ORDER BY uploaded DESC");
?>

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

Praveen Kumar

I am a fullstack web developer with a passion for SEO, creating stunning websites, and organic marketing. I am a perfectionist.

Post navigation