You can save your uploading images in the database table for later use e.g. display user profile or product image, create an image gallery, etc.
There are two ways of doing this –
- Save the path or name of an image
- Encode image into a base64 format
In this tutorial, I show you both of the methods for storing and retrieving an image from the database table.
Contents
1. Table structure
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.
CREATE TABLE `images` ( `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, `name` varchar(200) NOT NULL, `image` longtext NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
2. Database Configuration
Create a config.php file for database configuration.
Completed Code
<?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 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.
Completed Code
<?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_dir.$name)){
// 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
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. 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.
Completed Code
<?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_dir.$name)){
// Convert to base64
$image_base64 = base64_encode(file_get_contents('upload/'.$name) );
$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>
Retrieve
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. Conclusion
In my opinion, instead of storing an image in the MySQL database in the base64 format, it’s better to store it in the server and save the reference in the database table to keep track of the location.
It is fast and consumes less space in the database table compare to base64.
You can 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.
