Sunday 23 October 2016

PHP Ajax Update MySQL Data Through Bootstrap Modal




Hello friends in this post we are going to discuss how can we update data into mysql table data with Bootstrap Modal by using php script with Ajax jquery without page refresh. In previous tutorial on Bootstrap Modal we have show how to insert data into Mysql table by using Bootstrap Modal with PHP script with Ajax jquery. The main use of Bootstrap Modal we can do any type of database operation like Insert, Update, fetch and delete or display any message without opening of new page but we can do this type things on page in the form of pop up alert box. We can called this Bootstrap Modal by click on anchor tag or any button by using jquery code or we can also show Bootstrap Modal window by defining data attribute in element. Here we have use Ajax Jquery and by jquery we have get employee id from edit button, we have create all employee separate edit button with different id and in this id we have store employee id and by using jquery code we have get employee id from button and by using ajax we have fetch that employee id data from database and then after we have display that data into Bootstrap Modal html form field and in html form we can change data and by clicking on update button we can update data into mysql database by using Ajax request method.


Source Code


index.php


 
 <?php  
 $connect = mysqli_connect("localhost", "root", "", "testing");  
 $query = "SELECT * FROM tbl_employee ORDER BY id DESC";  
 $result = mysqli_query($connect, $query);  
 ?>  
 <!DOCTYPE html>  
 <html>  
      <head>  
           <title>Webslesson Tutorial | PHP Ajax Update MySQL Data Through Bootstrap Modal</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:700px;">  
                <h3 align="center">PHP Ajax Update MySQL Data Through Bootstrap Modal</h3>  
                <br />  
                <div class="table-responsive">  
                     <div align="right">  
                          <button type="button" name="add" id="add" data-toggle="modal" data-target="#add_data_Modal" class="btn btn-warning">Add</button>  
                     </div>  
                     <br />  
                     <div id="employee_table">  
                          <table class="table table-bordered">  
                               <tr>  
                                    <th width="70%">Employee Name</th>  
                                    <th width="15%">Edit</th>  
                                    <th width="15%">View</th>  
                               </tr>  
                               <?php  
                               while($row = mysqli_fetch_array($result))  
                               {  
                               ?>  
                               <tr>  
                                    <td><?php echo $row["name"]; ?></td>  
                                    <td><input type="button" name="edit" value="Edit" id="<?php echo $row["id"]; ?>" class="btn btn-info btn-xs edit_data" /></td>  
                                    <td><input type="button" name="view" value="view" id="<?php echo $row["id"]; ?>" class="btn btn-info btn-xs view_data" /></td>  
                               </tr>  
                               <?php  
                               }  
                               ?>  
                          </table>  
                     </div>  
                </div>  
           </div>  
      </body>  
 </html>  
 <div id="dataModal" class="modal fade">  
      <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">Employee Details</h4>  
                </div>  
                <div class="modal-body" id="employee_detail">  
                </div>  
                <div class="modal-footer">  
                     <button type="button" class="btn btn-default" data-dismiss="modal">Close</button>  
                </div>  
           </div>  
      </div>  
 </div>  
 <div id="add_data_Modal" class="modal fade">  
      <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">PHP Ajax Update MySQL Data Through Bootstrap Modal</h4>  
                </div>  
                <div class="modal-body">  
                     <form method="post" id="insert_form">  
                          <label>Enter Employee Name</label>  
                          <input type="text" name="name" id="name" class="form-control" />  
                          <br />  
                          <label>Enter Employee Address</label>  
                          <textarea name="address" id="address" class="form-control"></textarea>  
                          <br />  
                          <label>Select Gender</label>  
                          <select name="gender" id="gender" class="form-control">  
                               <option value="Male">Male</option>  
                               <option value="Female">Female</option>  
                          </select>  
                          <br />  
                          <label>Enter Designation</label>  
                          <input type="text" name="designation" id="designation" class="form-control" />  
                          <br />  
                          <label>Enter Age</label>  
                          <input type="text" name="age" id="age" class="form-control" />  
                          <br />  
                          <input type="hidden" name="employee_id" id="employee_id" />  
                          <input type="submit" name="insert" id="insert" value="Insert" class="btn btn-success" />  
                     </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(){  
      $('#add').click(function(){  
           $('#insert').val("Insert");  
           $('#insert_form')[0].reset();  
      });  
      $(document).on('click', '.edit_data', function(){  
           var employee_id = $(this).attr("id");  
           $.ajax({  
                url:"fetch.php",  
                method:"POST",  
                data:{employee_id:employee_id},  
                dataType:"json",  
                success:function(data){  
                     $('#name').val(data.name);  
                     $('#address').val(data.address);  
                     $('#gender').val(data.gender);  
                     $('#designation').val(data.designation);  
                     $('#age').val(data.age);  
                     $('#employee_id').val(data.id);  
                     $('#insert').val("Update");  
                     $('#add_data_Modal').modal('show');  
                }  
           });  
      });  
      $('#insert_form').on("submit", function(event){  
           event.preventDefault();  
           if($('#name').val() == "")  
           {  
                alert("Name is required");  
           }  
           else if($('#address').val() == '')  
           {  
                alert("Address is required");  
           }  
           else if($('#designation').val() == '')  
           {  
                alert("Designation is required");  
           }  
           else if($('#age').val() == '')  
           {  
                alert("Age is required");  
           }  
           else  
           {  
                $.ajax({  
                     url:"insert.php",  
                     method:"POST",  
                     data:$('#insert_form').serialize(),  
                     beforeSend:function(){  
                          $('#insert').val("Inserting");  
                     },  
                     success:function(data){  
                          $('#insert_form')[0].reset();  
                          $('#add_data_Modal').modal('hide');  
                          $('#employee_table').html(data);  
                     }  
                });  
           }  
      });  
      $(document).on('click', '.view_data', function(){  
           var employee_id = $(this).attr("id");  
           if(employee_id != '')  
           {  
                $.ajax({  
                     url:"select.php",  
                     method:"POST",  
                     data:{employee_id:employee_id},  
                     success:function(data){  
                          $('#employee_detail').html(data);  
                          $('#dataModal').modal('show');  
                     }  
                });  
           }            
      });  
 });  
 </script>
 

fetch.php


 
  <?php  
 //fetch.php  
 $connect = mysqli_connect("localhost", "root", "", "testing");  
 if(isset($_POST["employee_id"]))  
 {  
      $query = "SELECT * FROM tbl_employee WHERE id = '".$_POST["employee_id"]."'";  
      $result = mysqli_query($connect, $query);  
      $row = mysqli_fetch_array($result);  
      echo json_encode($row);  
 }  
 ?>
 

insert.php


 
  <?php  
 $connect = mysqli_connect("localhost", "root", "", "testing");  
 if(!empty($_POST))  
 {  
      $output = '';  
      $message = '';  
      $name = mysqli_real_escape_string($connect, $_POST["name"]);  
      $address = mysqli_real_escape_string($connect, $_POST["address"]);  
      $gender = mysqli_real_escape_string($connect, $_POST["gender"]);  
      $designation = mysqli_real_escape_string($connect, $_POST["designation"]);  
      $age = mysqli_real_escape_string($connect, $_POST["age"]);  
      if($_POST["employee_id"] != '')  
      {  
           $query = "  
           UPDATE tbl_employee   
           SET name='$name',   
           address='$address',   
           gender='$gender',   
           designation = '$designation',   
           age = '$age'   
           WHERE id='".$_POST["employee_id"]."'";  
           $message = 'Data Updated';  
      }  
      else  
      {  
           $query = "  
           INSERT INTO tbl_employee(name, address, gender, designation, age)  
           VALUES('$name', '$address', '$gender', '$designation', '$age');  
           ";  
           $message = 'Data Inserted';  
      }  
      if(mysqli_query($connect, $query))  
      {  
           $output .= '<label class="text-success">' . $message . '</label>';  
           $select_query = "SELECT * FROM tbl_employee ORDER BY id DESC";  
           $result = mysqli_query($connect, $select_query);  
           $output .= '  
                <table class="table table-bordered">  
                     <tr>  
                          <th width="70%">Employee Name</th>  
                          <th width="15%">Edit</th>  
                          <th width="15%">View</th>  
                     </tr>  
           ';  
           while($row = mysqli_fetch_array($result))  
           {  
                $output .= '  
                     <tr>  
                          <td>' . $row["name"] . '</td>  
                          <td><input type="button" name="edit" value="Edit" id="'.$row["id"] .'" class="btn btn-info btn-xs edit_data" /></td>  
                          <td><input type="button" name="view" value="view" id="' . $row["id"] . '" class="btn btn-info btn-xs view_data" /></td>  
                     </tr>  
                ';  
           }  
           $output .= '</table>';  
      }  
      echo $output;  
 }  
 ?>
 

select.php


 
  <?php  
 if(isset($_POST["employee_id"]))  
 {  
      $output = '';  
      $connect = mysqli_connect("localhost", "root", "", "testing");  
      $query = "SELECT * FROM tbl_employee WHERE id = '".$_POST["employee_id"]."'";  
      $result = mysqli_query($connect, $query);  
      $output .= '  
      <div class="table-responsive">  
           <table class="table table-bordered">';  
      while($row = mysqli_fetch_array($result))  
      {  
           $output .= '  
                <tr>  
                     <td width="30%"><label>Name</label></td>  
                     <td width="70%">'.$row["name"].'</td>  
                </tr>  
                <tr>  
                     <td width="30%"><label>Address</label></td>  
                     <td width="70%">'.$row["address"].'</td>  
                </tr>  
                <tr>  
                     <td width="30%"><label>Gender</label></td>  
                     <td width="70%">'.$row["gender"].'</td>  
                </tr>  
                <tr>  
                     <td width="30%"><label>Designation</label></td>  
                     <td width="70%">'.$row["designation"].'</td>  
                </tr>  
                <tr>  
                     <td width="30%"><label>Age</label></td>  
                     <td width="70%">'.$row["age"].' Year</td>  
                </tr>  
           ';  
      }  
      $output .= '  
           </table>  
      </div>  
      ';  
      echo $output;  
 }  
 ?>
 

tbl_employee


 
  --  
 -- Table structure for table `tbl_employee`  
 --  
 CREATE TABLE IF NOT EXISTS `tbl_employee` (  
  `id` int(11) NOT NULL AUTO_INCREMENT,  
  `name` varchar(50) NOT NULL,  
  `address` text NOT NULL,  
  `gender` varchar(10) NOT NULL,  
  `designation` varchar(100) NOT NULL,  
  `age` int(11) NOT NULL,  
  `image` varchar(100) NOT NULL,  
  PRIMARY KEY (`id`)  
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=187 ;  
 --  
 -- Dumping data for table `tbl_employee`  
 --  
 INSERT INTO `tbl_employee` (`id`, `name`, `address`, `gender`, `designation`, `age`, `image`) VALUES  
 (1, 'Bruce Tom', '656 Edsel Road\r\nSherman Oaks, CA 91403', 'Male', 'Driver', 36, '1.jpg'),  
 (5, 'Clara Gilliam', '63 Woodridge Lane\r\nMemphis, TN 38138', 'Female', 'Programmer', 24, '2.jpg'),  
 (6, 'Barbra K. Hurley', '1241 Canis Heights Drive\r\nLos Angeles, CA 90017', 'Female', 'Service technician', 26, '3.jpg'),  
 (7, 'Antonio J. Forbes', '403 Snyder Avenue\r\nCharlotte, NC 28208', 'Male', 'Faller', 32, '4.jpg'),  
 (8, 'Charles D. Horst', '1636 Walnut Hill Drive\r\nCincinnati, OH 45202', 'Male', 'Financial investigator', 29, '5.jpg'),  
 (175, 'Ronald D. Colella', '1571 Bingamon Branch Road, Barrington, IL 60010', 'Male', 'Top executive', 32, '6.jpg'),  
 (174, 'Martha B. Tomlinson', '4005 Bird Spring Lane, Houston, TX 77002', 'Female', 'Systems programmer', 38, '7.jpg'),  
 (161, 'Glenda J. Stewart', '3482 Pursglove Court, Rossburg, OH 45362', 'Female', 'Cost consultant', 28, '8.jpg'),  
 (162, 'Jarrod D. Jones', '3827 Bingamon Road, Garfield Heights, OH 44125', 'Male', 'Manpower development advisor', 64, '9.jpg'),  
 (163, 'William C. Wright', '2653 Pyramid Valley Road, Cedar Rapids, IA 52404', 'Male', 'Political geographer', 33, '10.jpg'),  
 (178, 'Sara K. Ebert', '1197 Nelm Street\r\nMc Lean, VA 22102', 'Female', 'Billing machine operator', 50, ''),  
 (177, 'Patricia L. Scott', '1584 Dennison Street\r\nModesto, CA 95354', 'Female', 'Urban and regional planner', 54, ''),  
 (179, 'James K. Ridgway', '3462 Jody Road\r\nWayne, PA 19088', 'Female', 'Recreation leader', 41, ''),  
 (180, 'Stephen A. Crook', '448 Deercove Drive\r\nDallas, TX 75201', 'Male', 'Optical goods worker', 36, ''),  
 (181, 'Kimberly J. Ellis', '4905 Holt Street\r\nFort Lauderdale, FL 33301', 'Male', 'Dressing room attendant', 24, ''),  
 (182, 'Elizabeth N. Bradley', '1399 Randall Drive\r\nHonolulu, HI 96819', 'Female', ' Software quality assurance analyst', 25, ''),  
 (183, 'Steve John', '108, Vile Parle, CL', 'Male', 'Software Engineer', 29, ''),  
 (184, 'Marks Johnson', '021, Big street, NY', 'Male', 'Head of IT', 41, ''),  
 (185, 'Mak Pub', '1462 Juniper Drive\r\nBreckenridge, MI 48612', 'Male', 'Mental health counselor', 40, ''),  
 (186, 'Louis C. Charmis', '1462 Juniper Drive\r\nBreckenridge, MI 48612', 'Male', 'Mental health counselor', 40, '');  
 

74 comments:

  1. Buenos Dias solo se puede aplicar sobre una base de 4 columnas???

    ReplyDelete
  2. I need help, in my database I have a column named Names and accept special characters of the Spanish language, such as ñ, ü, é, á, í, ó, ú, however when I click on edit the form does not load. It seems that #Json does not accept special characters

    ReplyDelete
    Replies
    1. Puedes usar en las variables PHP el = utf8 encode o decode

      Por decir: $Nombre = utf8_incode($Nombe);

      Así cuando la variable llega decodificada por el carácter especial el utf8 lo codifica.

      Échate un clavado en esas funciones para que las aproveches al máximo.

      Delete
  3. can i get a softcopy for this codes

    ReplyDelete
  4. This code have a small error. First, you chosen and edit a field and submit. After that, you will add a new field and submit. Not data will be inserted. It will update value to the field be edit before.
    I fixed it by set
    $('#employee_id').val(''); after line $('#employee_table').html(data); of index file

    ReplyDelete
  5. how to add & update image in this???

    ReplyDelete
  6. Gracias, me ayudo mucho, ya que no me aceptaba el id y no asi la consulta

    ReplyDelete
  7. hey. this is a very good work. thanks a lot. but, can you show us how we can add a picture with all the employee database... and show all in the modal. im not able to do it... thanks

    ReplyDelete
  8. hey. this is a very good work. thanks a lot. but, can you show us how we can add a picture with all the employee database... and show all in the modal. im not able to do it... thanks

    ReplyDelete
  9. Hello,
    1st oficial aplicação great job and its has been very helpfull.
    But i have a question... How can i add a delete button to the tablet to delete a specific Record?

    Regards
    xmodpt

    ReplyDelete
  10. Thanks for this, it's a great help in our project :) God bless

    ReplyDelete
  11. $(document).on('click', '.edit_data', function(){
    var distribution_id = $(this).attr("id");
    $.ajax({
    url:"Dashboard/update_distribution",
    method:"POST",
    data:{distribution_id:distribution_id},
    dataType:"json",
    success:function(data){
    $('#name').val(data[0].distribution_name );
    $('#manager_name').val(data[0].distribution_manager);
    $('#email').val(data[0].distribution_email);
    $('#phone').val(data[0].distribution_phone);
    $('#district').val(data[0].distribution_district);
    $('#city').val(data[0].distribution_city);
    $('#distribution_id').val(data[0].distribution_id);
    $('#create').text("Update");
    $('#create').attr('id',"update");
    $('#createOrder').modal('show');
    }
    });
    });


    here id changing to update but when click on this it inserting new

    ReplyDelete
  12. If you edit an employee, then immediately try to add a new employee, it overwrites the previously edited employee with the new employees info. Other than that, I like your work. Thanks!

    ReplyDelete
  13. i want to update image, how can i do that?

    ReplyDelete
  14. Your program has a bug when I add data, the data appears, when I change the data the data changes but when I add new data the data doesn't increase but the previous data changes

    ReplyDelete
  15. New data can not be inserted correctly.. instead previously open/edited record was updated while inserting new record

    ReplyDelete
  16. Edit button not working, asides that its a Great work.

    ReplyDelete
  17. cant get hidden id value when updating to db

    ReplyDelete
  18. how to make half editable and half input uneditable form and also how to make editable label form

    ReplyDelete
  19. hola coloque un alert despues de esto
    var editar = $(this).attr("CodBien");

    imprimio indefinido a que se debe. le agradesco por la ayuda

    ReplyDelete
  20. Thanks a lot.

    ReplyDelete
  21. Thanks got me started on posting and calling via ajax

    ReplyDelete
  22. Any answer to this problem?
    ......If you edit an employee, then immediately try to add a new employee, it overwrites the previously edited employee with the new employees info.

    ......Your program has a bug when I add data, the data appears, when I change the data the data changes but when I add new data the data doesn't increase but the previous data changes

    ......New data can not be inserted correctly.. instead previously open/edited record was updated while inserting new record

    ReplyDelete
    Replies
    1. Way to state the same thing three times.. troubleshoot man. Everything you need to fix this is in the code.

      Delete
  23. Thanks very mutch for this great work

    ReplyDelete
  24. why when insert data then press update (but not yet update) then press insert and fill in the information but it update instead of inserting.

    ReplyDelete
  25. why when insert data then press update (but not yet update) then press insert and fill in the information but it update instead of inserting.

    ReplyDelete
  26. why when insert data then press update (but not yet update) then press insert and fill in the information but it update instead of inserting.

    ReplyDelete
  27. Webslesson always gives more than you ask for.

    ReplyDelete
  28. what about image?
    how can i insert and update image in this example

    ReplyDelete
  29. Thanks for the good work but i'm unable to load modal after edit button click. I don't know what's the problem

    ReplyDelete
  30. Thank you very much, you helped me too much, I had a good time in that.

    ReplyDelete
  31. i need to fix the update and add function same as the problem of others. When we first update then add it just updated the last data we updated. Please help ASAP.

    ReplyDelete
  32. Hi, I have a problem that editing data adds a new record instead of editing the selected one. What may be the problem?

    ReplyDelete
  33. Thanks for this helpful tutorial!

    There are some bugs...
    1. As mentioned before, Insert does not work properly because hidden input with employee_id value should be empty. To fix it I just added $('#employee_id').val("");

    $('#add').click(function(){
    $('#insert').val("Insert");
    $('#insert_form')[0].reset();
    $('#employee_id').val("");
    });

    2. There is unnecessary column in table called Image. Just remove it.
    3. Change encoding to utf8_general_ci

    I hope it helps!

    ReplyDelete
  34. When I add checkbox to this, how set checked attribute to chekcbox if in my database is value 1 or 0. 1 checked 0 not checked

    ReplyDelete
  35. Great work. Please fix the update and adding new record problem and also how do I use it to update related tables like STUDENT and COURSE tables

    ReplyDelete
  36. Enter a comment... This project has been great help to my project...


    Thank you... Thank you...

    ReplyDelete
  37. Hello dear, I don't know INSERT Data is not successful,
    by the ways, everything is going well but the only FUNCTION of ajax (Insert data) it doesn't work I was try to change api jQuery library but not success. Please help testing again

    ReplyDelete
  38. plz i want tutorial on this but how do i update when i have a multiple select field>

    ReplyDelete
  39. How can I add search functionality please?

    ReplyDelete
  40. when click on update form is not open

    ReplyDelete
  41. Code works almost perfectly (thanks!) except for "Update". When clicked, "Update" doesn't appear to be passing the employee_id to insert.php (the result being that the updated record is added as a new record). Seems like a simple fix yet the solution still evades me! Has anyone gotten "Update" to work?

    Thanks in advance for any-and-all assistance.

    ReplyDelete
    Replies
    1. Just add this code:
      $("#dataModal").on('hidden.bs.modal', function(){
      $('#employee_id').val('');
      });

      Delete
    2. Does that go in here somewhere?
      $(document).on('click', '.edit_data', function(){
      var employee_id = $(this).attr("id");
      $.ajax({
      url:"fetch.php",
      method:"POST",
      data:{employee_id:employee_id},
      dataType:"json",
      success:function(data){
      $('#name').val(data.name);
      $('#address').val(data.address);
      $('#gender').val(data.gender);
      $('#designation').val(data.designation);
      $('#age').val(data.age);
      $('#employee_id').val(data.id);
      $('#insert').val("Update");
      $('#add_data_Modal').modal('show');
      }
      });

      Delete
  42. This is a great start! Thanks so much!! I too, am having the issue of nothing happening when I hit the Edit button. Any help out there?

    ReplyDelete