Upload and store an image in the Database with PHP

You can save your uploading images in the database table for later use e.g. display user profile or product image, create the image gallery, etc.

There are two ways of doing this –

  • Save path or name of an image
  • Encode image into base64 format

In this tutorial, I show you both of the methods for storing and retrieving an image from the database table.

Upload and store an image in the database with PHP


Contents

  1. Table structure
  2. Configuration
  3. Save path or name
  4. base64_encode()
  5. Conclusion

 


1. Table structure

I am using images table for storing data.

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. Configuration

Create a new config.php file.

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 full path or name of an image in your MySQL database table. Retrieve the image name or path from the MySQL database and use to make image source.

Here, I am storing file name in 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) ){
 
  // Insert record
  $query = "insert into images(name) values('".$name."')";
  mysqli_query($con,$query);
  
  // Upload file
  move_uploaded_file($_FILES['file']['tmp_name'],$target_dir.$name);

 }
 
}
?>

<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 name or path of the image which you have stored in the database table and use it in 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 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 need to display the image just fetch the value and use it as an image source.

Note – In the example, I upload the image to directory. 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) ){
 
  // Convert to base64 
  $image_base64 = base64_encode(file_get_contents($_FILES['file']['tmp_name']) );
  $image = 'data:image/'.$imageFileType.';base64,'.$image_base64;
  // Insert record
  $query = "insert into images(image) values('".$image."')";
  mysqli_query($con,$query);
  
  // Upload file
  move_uploaded_file($_FILES['file']['tmp_name'],$target_dir.$name);
 }
 
}
?>

<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 using it in image source.

Example

<?php

 $sql = "select image from images where id=1";
 $result = mysqli_query($con,$sql);
 $row = mysqli_fetch_array($result);

 $image_src2 = $row['image'];
 
?>
<img src='<?php echo $image_src; ?>' >

5. Conclusion

In my opinion, instead of storing an image in MySQL database in the base64 format, it’s better to store 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.

If you found this tutorial helpful then don't forget to share.

Related Post

Spread the love
  • 12
  •  
  •  
  •  
  •  
  •  

37 Comments

  1. alpesh navadiya said:

    data not inserted in database

    June 9, 2017
    Reply
    • Yogesh Singh said:

      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.

      June 9, 2017
      Reply
  2. alpesh said:

    thanks
    How can i insert muti image in 64base

    June 12, 2017
    Reply
  3. Asif said:

    Hi, Thanks for shared, I really need it.

    August 21, 2017
    Reply
  4. Rei said:

    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.”‘)”);

    September 3, 2017
    Reply
  5. Motiur Rahman said:

    hello
    I’ve tried to inert but data doesn’t inserted. how can I come over it? please help

    September 12, 2017
    Reply
  6. ankit said:

    Best work I have seen ever and the best answers very good job thanks so much.

    December 4, 2017
    Reply
  7. Waled Mahfooz said:

    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..

    December 5, 2017
    Reply
    • Yogesh Singh said:

      Hi Waled,
      Check the image path is correct or not in the page view source.

      December 5, 2017
      Reply
  8. Jason said:

    Works for me uploading an mp3 file. What about appending a date and time to the file name to prevent overwriting? Or another method?

    December 5, 2017
    Reply
    • Yogesh Singh said:

      Hi Jason,
      Yes, you can add date and time to avoid overwriting.
      You can also use table insert id to create the file name.

      December 6, 2017
      Reply
  9. Shoaib said:

    Thank u bro…

    December 9, 2017
    Reply
  10. vishal said:

    base64 image retrieval code is not working. i am new to this so please help me in image retrieving

    December 20, 2017
    Reply
    • vishal said:

      Got it… done sir. Thank you so much for your post

      December 20, 2017
      Reply
  11. laiba ahmed said:

    thanku so much…..it help me alot ,i was suffering from this problem from 2 weeks…..you make it happen thanx alot

    December 31, 2017
    Reply
  12. Rajendra K said:

    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

    January 2, 2018
    Reply
  13. zali said:

    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..

    January 3, 2018
    Reply
    • Yogesh Singh said:

      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.

      January 3, 2018
      Reply
  14. sami said:

    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.

    February 13, 2018
    Reply
    • Yogesh Singh said:

      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.

      February 13, 2018
      Reply
  15. Prince said:

    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.

    February 19, 2018
    Reply
    • Yogesh Singh said:

      Hi Prince,
      It is if(isset($_POST[‘but_upload’])).

      Thanks for asking and I have corrected it.

      February 19, 2018
      Reply
  16. sajjad said:

    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

    March 14, 2018
    Reply
    • Yogesh Singh said:

      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”

      March 15, 2018
      Reply
  17. marvin said:

    Waaaauzzzz !!!!! thank you so much this code actually might save my job

    March 31, 2018
    Reply
  18. Nicholas said:

    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.

    April 4, 2018
    Reply
    • Nicholas said:

      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.

      April 8, 2018
      Reply
  19. bindu said:

    hii can you please send multiple images insert in php by using mysql

    April 6, 2018
    Reply
  20. Venugopal said:

    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

    April 24, 2018
    Reply
  21. megha said:

    how to video uplode code

    May 3, 2018
    Reply
  22. Gaurav Pathak said:

    i have to upload two img then how should i write code plz help

    December 2, 2018
    Reply
    • Yogesh Singh said:

      Hi Gaurav,
      You can create two file elements and on form submit upload the file and store the path in the MySQL.

      December 2, 2018
      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *