Wednesday 21 June 2017

Ajax Image Add Edit Remove from Mysql Database using PHP



In this blog we have made discussion on how to store and display image into Mysql database with change or edit mysql database image and remove or delete images from Mysql Database by using PHP script with Ajax. In one of my previous post we have already seen How to Insert and Fetch Image from Mysql Database by using pure PHP Script. But in this post we have not only store and retrieve image from Mysql Database but also update and delete images from Mysql Database by Ajax Jquery with PHP script without refresh of webpage.

If you have developed any web application and if you have looking for web tutorial on How to store images in Mysql Database by using PHP script then you can follow this tutorial in which we have store and retrieve image from database by using PHP script with Ajax Jquery. In many of the project required this type of feature in which images want be stored in database then there is one big question how can we store images in to Mysql database. In Mysql Database there is four data types are available for store images into Database. There is data type are Tiny blob, Blob, Medium Blob and Long Blog. Here Tiny Blob and Blob data type are used for store small size images, for medium size image we can use Medium Blob datatype and for store large size images we can use Long Blob datatype. So These data type are used to store images into Mysql Database.

Now question is in which type of project we can store images into Database, if you have developed any eCommerce site and we want to index site page on search engine then this type of website we have to follows to images in particular folder according to keyword name. Because images will also index on search engine also, but this type of images will not directly index on search engine. But if you have developed any enterprise level application then you have can store images into database because we can easily retrieve from database. So, in this post we have describe How to insert update delete and fetch images from database by using PHP with Ajax Jquery.








Source Code


index.php



<!DOCTYPE html>  
<html>  
 <head>  
  <title>Ajax Image Insert Update Delete in Mysql Database using PHP</title>  
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>  
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />  
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>  
 </head>  
 <body>  
  <br /><br />  
  <div class="container" style="width:900px;">  
   <h3 align="center">Ajax Image Insert Update Delete in Mysql Database using PHP</h3>  
   <br />
   <div align="right">
    <button type="button" name="add" id="add" class="btn btn-success">Add</button>
   </div>
   <br />
   <div id="image_data">

   </div>
  </div>  
 </body>  
</html>

<div id="imageModal" class="modal fade" role="dialog">
 <div class="modal-dialog">
  <div class="modal-content">
   <div class="modal-header">
    <button type="button" class="close" data-dismiss="modal">&times;</button>
    <h4 class="modal-title">Add Image</h4>
   </div>
   <div class="modal-body">
    <form id="image_form" method="post" enctype="multipart/form-data">
     <p><label>Select Image</label>
     <input type="file" name="image" id="image" /></p><br />
     <input type="hidden" name="action" id="action" value="insert" />
     <input type="hidden" name="image_id" id="image_id" />
     <input type="submit" name="insert" id="insert" value="Insert" class="btn btn-info" />
      
    </form>
   </div>
   <div class="modal-footer">
    <button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
   </div>
  </div>
 </div>
</div>
 
<script>  
$(document).ready(function(){
 
 fetch_data();

 function fetch_data()
 {
  var action = "fetch";
  $.ajax({
   url:"action.php",
   method:"POST",
   data:{action:action},
   success:function(data)
   {
    $('#image_data').html(data);
   }
  })
 }
 $('#add').click(function(){
  $('#imageModal').modal('show');
  $('#image_form')[0].reset();
  $('.modal-title').text("Add Image");
  $('#image_id').val('');
  $('#action').val('insert');
  $('#insert').val("Insert");
 });
 $('#image_form').submit(function(event){
  event.preventDefault();
  var image_name = $('#image').val();
  if(image_name == '')
  {
   alert("Please Select Image");
   return false;
  }
  else
  {
   var extension = $('#image').val().split('.').pop().toLowerCase();
   if(jQuery.inArray(extension, ['gif','png','jpg','jpeg']) == -1)
   {
    alert("Invalid Image File");
    $('#image').val('');
    return false;
   }
   else
   {
    $.ajax({
     url:"action.php",
     method:"POST",
     data:new FormData(this),
     contentType:false,
     processData:false,
     success:function(data)
     {
      alert(data);
      fetch_data();
      $('#image_form')[0].reset();
      $('#imageModal').modal('hide');
     }
    });
   }
  }
 });
 $(document).on('click', '.update', function(){
  $('#image_id').val($(this).attr("id"));
  $('#action').val("update");
  $('.modal-title').text("Update Image");
  $('#insert').val("Update");
  $('#imageModal').modal("show");
 });
 $(document).on('click', '.delete', function(){
  var image_id = $(this).attr("id");
  var action = "delete";
  if(confirm("Are you sure you want to remove this image from database?"))
  {
   $.ajax({
    url:"action.php",
    method:"POST",
    data:{image_id:image_id, action:action},
    success:function(data)
    {
     alert(data);
     fetch_data();
    }
   })
  }
  else
  {
   return false;
  }
 });
});  
</script>


action.php



<?php
//action.php
if(isset($_POST["action"]))
{
 $connect = mysqli_connect("localhost", "root", "", "testing");
 if($_POST["action"] == "fetch")
 {
  $query = "SELECT * FROM tbl_images ORDER BY id DESC";
  $result = mysqli_query($connect, $query);
  $output = '
   <table class="table table-bordered table-striped">  
    <tr>
     <th width="10%">ID</th>
     <th width="70%">Image</th>
     <th width="10%">Change</th>
     <th width="10%">Remove</th>
    </tr>
  ';
  while($row = mysqli_fetch_array($result))
  {
   $output .= '

    <tr>
     <td>'.$row["id"].'</td>
     <td>
      <img src="data:image/jpeg;base64,'.base64_encode($row['name'] ).'" height="60" width="75" class="img-thumbnail" />
     </td>
     <td><button type="button" name="update" class="btn btn-warning bt-xs update" id="'.$row["id"].'">Change</button></td>
     <td><button type="button" name="delete" class="btn btn-danger bt-xs delete" id="'.$row["id"].'">Remove</button></td>
    </tr>
   ';
  }
  $output .= '</table>';
  echo $output;
 }

 if($_POST["action"] == "insert")
 {
  $file = addslashes(file_get_contents($_FILES["image"]["tmp_name"]));
  $query = "INSERT INTO tbl_images(name) VALUES ('$file')";
  if(mysqli_query($connect, $query))
  {
   echo 'Image Inserted into Database';
  }
 }
 if($_POST["action"] == "update")
 {
  $file = addslashes(file_get_contents($_FILES["image"]["tmp_name"]));
  $query = "UPDATE tbl_images SET name = '$file' WHERE id = '".$_POST["image_id"]."'";
  if(mysqli_query($connect, $query))
  {
   echo 'Image Updated into Database';
  }
 }
 if($_POST["action"] == "delete")
 {
  $query = "DELETE FROM tbl_images WHERE id = '".$_POST["image_id"]."'";
  if(mysqli_query($connect, $query))
  {
   echo 'Image Deleted from Database';
  }
 }
}
?>


Database



--
-- Database: `testing`
--

-- --------------------------------------------------------

--
-- Table structure for table `tbl_images`
--

CREATE TABLE IF NOT EXISTS `tbl_images` (
  `id` int(11) NOT NULL,
  `name` longblob NOT NULL
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

--
-- Indexes for table `tbl_images`
--
ALTER TABLE `tbl_images`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tbl_images`
--
ALTER TABLE `tbl_images`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=1;

43 comments:

  1. can you create so the files end up in a folder and not the database?

    ReplyDelete
  2. can i use it in my website

    ReplyDelete
  3. How to add description of the image into the database?

    ReplyDelete
  4. thnks bro...

    can i use this method on professional level application?

    ReplyDelete
  5. how to pass variable for fetch record with condition

    ReplyDelete
  6. how to pass variable in fetch function to fetch data from action page for conditional output

    ReplyDelete
  7. I keep getting file_get_contents() empty

    ReplyDelete
  8. I keep getting file_get_contents() empty under action.php

    ReplyDelete
  9. where I modify the folder where the images are saved

    ReplyDelete
  10. can not work this code in pdo

    ReplyDelete
  11. Thanks, I don't know much about ajax, but your example is really simple and easy. It helped me lots.

    ReplyDelete
  12. Thank You It's really helpful

    ReplyDelete
  13. This comment has been removed by the author.

    ReplyDelete
  14. Can you provide any tutorial for "imaje upload using react and php pdo with json"

    ReplyDelete
  15. Hi bro i have a post query (update, delete, select) in mysql response into list/ arry

    ReplyDelete
  16. Hello guys I used this method but I got these problems .Could someone please help me ?
    Any kind of help would be much appreciated !!
    Thanks in advance.

    ReplyDelete
  17. Hello guys I used this method but I got these problems .Could someone please help me ?
    Any kind of help would be much appreciated !!
    Thanks in advance.

    ReplyDelete
  18. Help guys please how to fix this problem https://imgur.com/IVcI4nx https://imgur.com/1cV4NRt https://imgur.com/cqH8yjk

    ReplyDelete
  19. hello sir, how can i send another field in database. using this script.

    ReplyDelete
  20. You are the best! Excelent work. Thank you!

    ReplyDelete
  21. what is the image folder name in localhost ?

    ReplyDelete
  22. you are the greatest programmer

    ReplyDelete
  23. Thank you so much amazing for your contribution

    ReplyDelete
  24. Thank you so much amazing for your contribution

    ReplyDelete
  25. thank you so much for your contribution

    ReplyDelete
  26. hello, can you tell me how can I resize pictures while uploading?

    ReplyDelete
  27. THANKS A LOT THIS CODE IS SO USEFUL THANK YOU SO MUCH

    ReplyDelete
  28. Please sir ajax capacha refresh system

    ReplyDelete
  29. it is very helpful
    thank you so muck bro

    ReplyDelete
  30. can we use this commercial purpose

    ReplyDelete
  31. how to insert image and orther text fields at the same time, I have tried many times, however I still not success. the main reason is that I can't post the formdata and json data at same structure.

    ReplyDelete
  32. new FormData($("#formid")[0]);

    ReplyDelete