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.
data not inserted in database
Sometimes there is the problem when storing the base64 value of large image file in MySQL database table.
Open my.ini (xampp/mysql/bin/my.ini) and modify the following variables values –
max_allowed_packet = 128M
innodb_log_file_size = 128M
Download the example code from the tutorial.
Notice: Undefined variable: image in C:\xampp\htdocs\refer\electro\upload\index.php on line 26
i Got This error
Anyway how to store mp3 file in MySQL to streamable through android apps?
I need to know this,
thanks
How can i insert muti image in 64base
While uploading multiple files convert them to base64 format and store it in your table.
Hi, Thanks for shared, I really need it.
It really helped me, thank you!
Just for further reference, it would be better check if the image already exists before to post another one, here a simple solution with an OOP approach:
$result = $conn->query(“select * from images where name=’$name'”);
if( !$result->num_rows ) $conn->query(“insert into images(name) values(‘”.$name.”‘)”);
Thanks for reading.
hello
I’ve tried to inert but data doesn’t inserted. how can I come over it? please help
Best work I have seen ever and the best answers very good job thanks so much.
Hi,
I am able to upload images succesfully .
but unable to retrieve..its just shows a blank broken image icon..please help me out ..
And than you sooo much for the post..
Hi Waled,
Check the image path is correct or not in the page view source.
Works for me uploading an mp3 file. What about appending a date and time to the file name to prevent overwriting? Or another method?
Hi Jason,
Yes, you can add date and time to avoid overwriting.
You can also use table insert id to create the file name.
Thank u bro…
base64 image retrieval code is not working. i am new to this so please help me in image retrieving
Got it… done sir. Thank you so much for your post
thanku so much…..it help me alot ,i was suffering from this problem from 2 weeks…..you make it happen thanx alot
Hi Mr. Yogesh ,
I am new to php . I am trying to fetch the image stored in database by using procedural mysqli. I am not able to fetch the image. Instead of image some code is showing there. Can somebody please help me out with this error…? Any help is appreciate
Hi Rajendra,
Can you mail me your files and database at makitweb@gmail.com. So I can check and resolve your issue.
Hi Mr. Yogesh ,
As u said earlier, instead of storing an image in MySQL database, it’s better to store in the server and save the reference in the database table to keep track of the location..would u please teach me how to do it..
Hi zali,
I already covered file location storing in the database table in this tutorial “Save path or name” section. If you are unable to implement in your file you can contact me.
Hello there this is my first time to come here . You are really creating a quality code here. But I’m suffering in very common but problem in PHP. I have a website for different user’s portfolio. Now what I’m saying is if a single user upload images to their portfolio so if any one will check their portfolio just that user’s images will appear on that user portfolio page.
When storing an image in the database also store userid. You need to create separate userid column in the database table.
If any user checks a user portfolio then fetch the images from the database table on the basis of userid and display it.
With this only selected user uploaded images will display.
Hello could you please explain to me why the name in php code is just ‘upload’ but not ‘but_upload’ meanwhile in your input form the name is ‘but_upload’.
if(isset($_POST[‘upload’])){
Thank you.
Hi Prince,
It is if(isset($_POST[‘but_upload’])).
Thanks for asking and I have corrected it.
Hi, thank you very much for uploading demo and codes, works perfectly well. However Retrieve code prints only first record, please advise how can I get full list of all of uploaded images,
Saj
Hi Sajjad,
If you want to select all uploaded images then remove WHERE clause from the SELECT query and loop on the fetched records.
“select image from images”
i tried this but it is not working for me
Waaaauzzzz !!!!! thank you so much this code actually might save my job
I’m trying to upload the path to a locally created database using phpmyadmin. The php file doesn’t show any errors, but the name won’t go to the database.
Thanks for the help. Through talking in the mail I found out that I set my database fields to not null so it couldn’t insert the reference. Also my upload path was outside of htdocs. Once again thanks for the help.
You’re very welcome!
hii can you please send multiple images insert in php by using mysql
Hi! Sir I am new to PHP. How can i store regno, name, photo in mysql table, and also view the records. i have done except image. please send me code thanking you
how to video uplode code
thanks
i have to upload two img then how should i write code plz help
Hi Gaurav,
You can create two file elements and on form submit upload the file and store the path in the MySQL.
Best Work!! Easy to understand and Implementation too Easy..!!
Glad you found this helpful.
I want a code for documents upload and retrive from database in php
How to interact with R using php and retrive database
Hi Sujeet,
You need to modify the $extensions_arr Array to enable document files to upload and retrieve process is similar to the tutorial example.
My image is not displayed. I have tried you way. It has not uploaded. While uploading images, name of image must upload or pathname of image. Which one is better?
Hi Prameet,
I generally prefer saving file name.
want to know what is uploads/
i am not getting it
and image is not storing in the database
Hi Almeera,
upload is a directory name where the file is been store and what method you are using – saving file path or base64 in the database?
Please help, it say, Notice: Undefined index: file for $name = $_FILES[‘file’][‘name’];
and the same thing for $target_file = $target_dir . basename($_FILES[“file”][“name”]);
It say that the name “file” is undefined.
Hi nungkhual,
Try to print $_FILES e.g. print_r($_FILES); die;. What it is displaying?
I did and it show Array(). There is nothing wrong with your code, I tested it alone and it work fine, but when I try to add it to my contact form, it’s not working. It’s saying that the index file is not defined.
Hi, thank you very much for uploading demo and codes, works perfectly well.
You’re welcome.
Bro…ur genius bro
Thanks.
hi
this code is giving me an error of Undefined index: file
hello i got a few problem.. i want to upload by image into the system but i also want its name to change automatically into its id that im already set on the database..
“MySQL server has gone away”
I am getting this message after uploading the image and it is not uploading in database.
How do I display multiple images at the same time? Not just one at a time
Hi Frank,
You can do this looping and create the img element.
Hi, thanks for your code. But I am facing some problem. The image is uploaded to the intended folder but its name is not being inserted into the database. What might be the possible cause and solution?
Hi Tanvir,
Have you checked the INSERT query and tried to debug it?
At first I was trying to perform two insert queries (one is for the image name and other is for others of the same form) from two different php blocks to the same table simultaneously. It was causing problem. Then I used two different tables (one for the image name and another for others) and the problem was solved.
Hi,
I’m trying to use prepared statement (procedural). However, I need to replace the ? with ‘”.$image.”‘, but this would normally be $image. Why is $image wrapped in ‘””‘ and do you know how to resolve this?
Thanks a million!
Carl
Hi Carl,
Because $image contains string type value. If you are using a prepared statement then you don’t need to specify it with quotes.
hi thankyou sir soo much …but can i have that readfile.php file
Hi Mayank,
I updated the download zip file.
hiiii…….
how do i upload multiple images
i want to diplay all the images stored in the database
how can i change same code upload multiple images at once and store paths in table row?
Hi Thushi,
You can view the following tutorial for reference.
How can I retrieve the images? Can you give full code? Thank you
Sorry, I want to rephrase my question. How can I retrieve one image at a time? I don’t want to retrieve all images I have uploaded.
Hi Katherine,
Have you tried the retrieved image example code of tutorial?
Yes I did, it retrieved the latest image I have uploaded to the database. Your code works perfectly. Very easy to understand. I have only this concerned that I can’t solve. I want to retrieved a certain image per page. I am planning to connect my table to the image table and add foreign key to add relationship but didn’t work. Could you help me with this concern? Thank you for your response by the way. Really appreciated it.
am very new to php and i created a php script and created a database with phpmyadmin but my search form is displaying all the records in my database without any one actually searching for a particular content or info in my database how to solve this please….
Hi Chinwa,
Can you share your code at makitweb@gmail.com so I can check it?
I can successfully insert multiple image file selected at once to database.But, what if a user uploads multiple images one after another(eg: clicked on choose file and uploaded one picture, then again choose on file and selected 2nd picture. Now I have 2 pictures), how to insert both images to database.? as in my case, only latest picture is getting inserted.
I can`t view my images in the table that i store in the upload file i use this code
}
$sql=”SELECT * FROM images”;
$result=$con-> query($sql);
if ($result-> num_rows >0){
while ($row =$result-> fetch_assoc()){
$image_src2 = $row[‘image’];
echo “”;
echo “<img src='’/>”;
echo “”.$row[‘prod_code’].””;
echo “”.$row[‘prod_name’].””;
echo “”.$row[‘prod_price’].””;
echo “”;
echo “”;
}
};
while ($row =$result-> fetch_assoc , should be- while ($row =$result-> fetch_array.
point: fetch_array not fetch_assoc
Perfect.exactly what i was looking for.
hello,
i managed to get the filename & image uploaded to database & server by following your example.
but how to insert the filename & image file to database & server if i have 3 input type file in one form? the example show single input type file ony.
appreciate your help.
thank you.
Wonderful explanation
if(move_uploaded_file($_FILES[‘file’][‘tmp_name’],’upload/’.$name)){
i am not able to move the image
this if condition is not working
Hi Joel,
Have you checked the ‘upload’ folder permission?
thanks youu
You’re welcome.