Uploading and storing an image in a database using PHP is a common task in web development. By storing images directly in the database, you can easily manage, retrieve, and manipulate image data alongside other relevant information. This simplifies data backup and recovery processes and ensures data integrity.
In this article, I will show you 3 approaches to accomplish this –
1. Storing image files by saving their file path or name,
2. Encoding images as base64, and
3. Storing images as BLOB (Binary Large Object) data.
Additionally, we will delve into the retrieval process to display the uploaded images from the database.
By the end of this article, you will have a better understanding of various techniques to upload images to a database and retrieve them for display on your web application.
Table of Content
- Create a Table to store the image
- Database Configuration
- Save file path or name
- Storing image as base64_encode()
- Storing image as BLOB (Binary Large Object) data
- Conclusion
1. Create a Table to store the image
In the example, I am using images table for storing data.
- name – This field is used to store the image file name.
- image – This field is used to store the image base64 generated value.
- imagetype – Store image file type – jpeg, jpg, png, gif
- image_blob – Store file in Blob format.
CREATE TABLE `images` (
`id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
`name` varchar(200) DEFAULT NULL,
`image` longtext,
`imagetype` varchar(20) DEFAULT NULL,
`image_blob` longblob
);
2. Database Configuration
Create a config.php file for database configuration.
<?php
$host = "localhost"; /* Host name */
$user = "root"; /* User */
$password = ""; /* Password */
$dbname = "tutorial"; /* Database name */
$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
die("Connection failed: " . mysqli_connect_error());
}
3. Save file path or name
You can either save the full path or name of an image in your MySQL database table. Retrieve the image name or path from the MySQL database and use it to make an image source.
Here, I am storing the file name in the MySQL database.
<?php
include("config.php");
if(isset($_POST['but_upload'])){
$name = $_FILES['file']['name'];
$target_dir = "upload/";
$target_file = $target_dir . basename($_FILES["file"]["name"]);
// Select file type
$imageFileType = strtolower(pathinfo($target_file,PATHINFO_EXTENSION));
// Valid file extensions
$extensions_arr = array("jpg","jpeg","png","gif");
// Check extension
if( in_array($imageFileType,$extensions_arr) ){
// Upload file
if(move_uploaded_file($_FILES['file']['tmp_name'],$target_file)){
// Insert record
$query = "insert into images(name) values('".$name."')";
mysqli_query($con,$query);
}
}
}
?>
<form method="post" action="" enctype='multipart/form-data'>
<input type='file' name='file' />
<input type='submit' value='Save name' name='but_upload'>
</form>
Retrieve file by name
Select the name or path of the image which you have stored in the database table and use it in the image source.
Example
<?php $sql = "select name from images where id=1"; $result = mysqli_query($con,$sql); $row = mysqli_fetch_array($result); $image = $row['name']; $image_src = "upload/".$image; ?> <img src='<?php echo $image_src; ?>' >
4. Storing image as base64_encode()
You can store the full image in the Database table by converting it into the base64 format. You don’t need to store image reference in the Database table e.g. name, path, and not require to store the image on your server.
In PHP base64_encode() method is been used for base64 conversion. Before storing it in the database I append data:image/'.$imageFileType.';base64, text with base64 value.
Now when you need to display the image just fetch the value and use it as an image source.
Note – In the example, I upload the image file to a folder. You can remove the upload code if you only want the image will accessible through base64 stored values in the database.
<?php
include("config.php");
if(isset($_POST['but_upload'])){
$name = $_FILES['file']['name'];
$target_dir = "upload/";
$target_file = $target_dir . basename($_FILES["file"]["name"]);
// Select file type
$imageFileType = strtolower(pathinfo($target_file,PATHINFO_EXTENSION));
// Valid file extensions
$extensions_arr = array("jpg","jpeg","png","gif");
// Check extension
if( in_array($imageFileType,$extensions_arr) ){
// Upload file
if(move_uploaded_file($_FILES['file']['tmp_name'],$target_file)){
// Convert to base64
$image_base64 = base64_encode(file_get_contents($target_file) );
$image = 'data:image/'.$imageFileType.';base64,'.$image_base64;
// Insert record
$query = "insert into images(image) values('".$image."')";
mysqli_query($con,$query);
}
}
}
?>
<form method="post" action="" enctype='multipart/form-data'>
<input type='file' name='file' />
<input type='submit' value='Save name' name='but_upload'>
</form>
Display image stored as base64 in the database
Select the stored base64 value and use it in the image source.
Example
<?php $sql = "select image from images order by id desc limit 1"; $result = mysqli_query($con,$sql); $row = mysqli_fetch_array($result); $image_src = $row['image']; ?> <img src='<?php echo $image_src; ?>' >
5. Storing image as BLOB (Binary Large Object) data
Storing images as BLOB data involves saving the binary image representation directly in a database column. It simplifies image management but can increase database size and impact retrieval speed. Consider factors like image volume and performance before choosing this method.
In the below example, when file is uploaded successfully then reads the contents of the uploaded file using file_get_contents() and encodes it in base64 format using base64_encode().
Insert the file’s extension (imagetype) and base64-encoded image data (image_blob) into the images table.
<?php
include("config.php");
if(isset($_POST['but_upload'])){
$name = $_FILES['file']['name'];
$target_dir = "upload/";
$target_file = $target_dir . basename($_FILES["file"]["name"]);
// Select file type
$imageFileType = strtolower(pathinfo($target_file,PATHINFO_EXTENSION));
// Valid file extensions
$extensions_arr = array("jpg","jpeg","png","gif");
// Check extension
if( in_array($imageFileType,$extensions_arr) ){
// Upload file
if(move_uploaded_file($_FILES['file']['tmp_name'],$target_file)){
// Convert to base64
$image_base64 = base64_encode(file_get_contents($target_file));
// Insert record
$query = "insert into images(imagetype,image_blob) values('".$imageFileType."','".$image_base64."')";
mysqli_query($con,$query);
}
}
}
?>
<form method="post" action="" enctype='multipart/form-data'>
<input type='file' name='file' />
<input type='submit' value='Save name' name='but_upload'>
</form>
Display image stored as BLOB in the database
Read imagetype,image_blob field values. In the <img > tag src attribute set data URI, which includes the image type retrieved from the database ($imagetype), followed by the base64-encoded image data ($image_src).
<?php $sql = "select imagetype,image_blob from images order by id desc limit 1"; $result = mysqli_query($con,$sql); $row = mysqli_fetch_array($result); $imagetype = $row['imagetype']; $image_src = $row['image_blob']; ?> <img src='data:image/<?= $imagetype ?>;base64,<?= $image_src ?>' >
6. Conclusion
We have explored three approaches for uploading and storing images to database using PHP. Each method has its own advantages and considerations. By centralizing data management, ensuring data integrity, and simplifying backup processes, storing images in the database offers benefits.
However, factors such as image volume and performance should be considered. Armed with this knowledge, you can now make informed decisions when handling image data in PHP for your web applications.
You can also view this tutorial if you want to know how you can store a video file in the MySQL database.
If you found this tutorial helpful then don't forget to share.
