Wednesday, 5 July 2017

Live Add Edit Delete Datatables Records using PHP Ajax



In this post we have made discussion on Live JQuery Datatables records insert, update, delete by using Ajax with PHP. In one of my previous famous post in which we have already seen how to live add edit delete records from simple html table by using PHP with Ajax Mysql. But now we have use Jquery Datatables plugin for display records in tabular format on web page. So in this post we will learn how to make Datatables for Live Ajax PHP Crud operation like create, read, update and delete without refresh of web page. Because in Datatables add or editing of live table records is very complex process.



We can make html table editable by using contenteditable attribute putting in table data tag. But in Datatables we can't use contenteditable attribute directly into td tag. Because this plugin has generate table code automatically we just want to pass data to Datatable() method in json format. Other code it has been generated. So, therefore we can't directly use contenteditable attribute directly in table. By using this attribute can convert tag to become editable, so we can make edit data of that tag very easily. Now here we want to make editable table data tag content, so we have define one division tag for this for display data from database to web page in Datatables and then after we have put contenteditable attribute define in that div tag and that html pass to plugin in json format. So this way we have make this plugin table data editable and we can easily edit content of table. This way we can define this attribute for that table column in which we want edit live table data.

After make Datatables record editable, now we want to add blank table row at the start of table. So we have make html code for generate blank table row. In this code we have also is that attribute, so we can write something in that table column with insert button. By using jquery prepend() method we have append this blank table row at the start of table and for add new records we can type in that blank table and click on Insert button. So this way we can insert live records in this Datatables by using Ajax with PHP script. Here It has been get the help of different jQuery event which are generates Ajax request and send that AJAX call to the server script with crud database action. Here we have make simple single page application by using Jquery Datatables plugin with Ajax PHP and Mysql. In which we have perform all operation on live table without going to other page or in pop modal but we have insert, update and delete live records by using PHP with Ajax.







Source Code



<html>
 <head>
  <title>Live Add Edit Delete Datatables Records using PHP Ajax</title>
  <script src="https://code.jquery.com/jquery-1.12.4.js"></script>
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" />
  <script src="https://cdn.datatables.net/1.10.15/js/jquery.dataTables.min.js"></script>
  <script src="https://cdn.datatables.net/1.10.15/js/dataTables.bootstrap.min.js"></script>
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-datepicker/1.6.4/css/bootstrap-datepicker.css" />
  <script src="https://cdnjs.cloudflare.com/ajax/libs/bootstrap-datepicker/1.6.4/js/bootstrap-datepicker.js"></script>
  <style>
  body
  {
   margin:0;
   padding:0;
   background-color:#f1f1f1;
  }
  .box
  {
   width:1270px;
   padding:20px;
   background-color:#fff;
   border:1px solid #ccc;
   border-radius:5px;
   margin-top:25px;
   box-sizing:border-box;
  }
  </style>
 </head>
 <body>
  <div class="container box">
   <h1 align="center">Live Add Edit Delete Datatables Records using PHP Ajax</h1>
   <br />
   <div class="table-responsive">
   <br />
    <div align="right">
     <button type="button" name="add" id="add" class="btn btn-info">Add</button>
    </div>
    <br />
    <div id="alert_message"></div>
    <table id="user_data" class="table table-bordered table-striped">
     <thead>
      <tr>
       <th>Frist Name</th>
       <th>Last Name</th>
       <th></th>
      </tr>
     </thead>
    </table>
   </div>
  </div>
 </body>
</html>

<script type="text/javascript" language="javascript" >
 $(document).ready(function(){
  
  fetch_data();

  function fetch_data()
  {
   var dataTable = $('#user_data').DataTable({
    "processing" : true,
    "serverSide" : true,
    "order" : [],
    "ajax" : {
     url:"fetch.php",
     type:"POST"
    }
   });
  }
  
  function update_data(id, column_name, value)
  {
   $.ajax({
    url:"update.php",
    method:"POST",
    data:{id:id, column_name:column_name, value:value},
    success:function(data)
    {
     $('#alert_message').html('<div class="alert alert-success">'+data+'</div>');
     $('#user_data').DataTable().destroy();
     fetch_data();
    }
   });
   setInterval(function(){
    $('#alert_message').html('');
   }, 5000);
  }

  $(document).on('blur', '.update', function(){
   var id = $(this).data("id");
   var column_name = $(this).data("column");
   var value = $(this).text();
   update_data(id, column_name, value);
  });
  
  $('#add').click(function(){
   var html = '<tr>';
   html += '<td contenteditable id="data1"></td>';
   html += '<td contenteditable id="data2"></td>';
   html += '<td><button type="button" name="insert" id="insert" class="btn btn-success btn-xs">Insert</button></td>';
   html += '</tr>';
   $('#user_data tbody').prepend(html);
  });
  
  $(document).on('click', '#insert', function(){
   var first_name = $('#data1').text();
   var last_name = $('#data2').text();
   if(first_name != '' && last_name != '')
   {
    $.ajax({
     url:"insert.php",
     method:"POST",
     data:{first_name:first_name, last_name:last_name},
     success:function(data)
     {
      $('#alert_message').html('<div class="alert alert-success">'+data+'</div>');
      $('#user_data').DataTable().destroy();
      fetch_data();
     }
    });
    setInterval(function(){
     $('#alert_message').html('');
    }, 5000);
   }
   else
   {
    alert("Both Fields is required");
   }
  });
  
  $(document).on('click', '.delete', function(){
   var id = $(this).attr("id");
   if(confirm("Are you sure you want to remove this?"))
   {
    $.ajax({
     url:"delete.php",
     method:"POST",
     data:{id:id},
     success:function(data){
      $('#alert_message').html('<div class="alert alert-success">'+data+'</div>');
      $('#user_data').DataTable().destroy();
      fetch_data();
     }
    });
    setInterval(function(){
     $('#alert_message').html('');
    }, 5000);
   }
  });
 });
</script>


insert.php



<?php
$connect = mysqli_connect("localhost", "root", "", "testing");
if(isset($_POST["first_name"], $_POST["last_name"]))
{
 $first_name = mysqli_real_escape_string($connect, $_POST["first_name"]);
 $last_name = mysqli_real_escape_string($connect, $_POST["last_name"]);
 $query = "INSERT INTO user(first_name, last_name) VALUES('$first_name', '$last_name')";
 if(mysqli_query($connect, $query))
 {
  echo 'Data Inserted';
 }
}
?>


update.php



<?php
$connect = mysqli_connect("localhost", "root", "", "testing");
if(isset($_POST["id"]))
{
 $value = mysqli_real_escape_string($connect, $_POST["value"]);
 $query = "UPDATE user SET ".$_POST["column_name"]."='".$value."' WHERE id = '".$_POST["id"]."'";
 if(mysqli_query($connect, $query))
 {
  echo 'Data Updated';
 }
}
?>


delete.php



<?php
$connect = mysqli_connect("localhost", "root", "", "testing");
if(isset($_POST["id"]))
{
 $query = "DELETE FROM user WHERE id = '".$_POST["id"]."'";
 if(mysqli_query($connect, $query))
 {
  echo 'Data Deleted';
 }
}
?>


fetch.php



<?php
//fetch.php
$connect = mysqli_connect("localhost", "root", "", "testing");
$columns = array('first_name', 'last_name');

$query = "SELECT * FROM user ";

if(isset($_POST["search"]["value"]))
{
 $query .= '
 WHERE first_name LIKE "%'.$_POST["search"]["value"].'%" 
 OR last_name LIKE "%'.$_POST["search"]["value"].'%" 
 ';
}

if(isset($_POST["order"]))
{
 $query .= 'ORDER BY '.$columns[$_POST['order']['0']['column']].' '.$_POST['order']['0']['dir'].' 
 ';
}
else
{
 $query .= 'ORDER BY id DESC ';
}

$query1 = '';

if($_POST["length"] != -1)
{
 $query1 = 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}

$number_filter_row = mysqli_num_rows(mysqli_query($connect, $query));

$result = mysqli_query($connect, $query . $query1);

$data = array();

while($row = mysqli_fetch_array($result))
{
 $sub_array = array();
 $sub_array[] = '<div contenteditable class="update" data-id="'.$row["id"].'" data-column="first_name">' . $row["first_name"] . '</div>';
 $sub_array[] = '<div contenteditable class="update" data-id="'.$row["id"].'" data-column="last_name">' . $row["last_name"] . '</div>';
 $sub_array[] = '<button type="button" name="delete" class="btn btn-danger btn-xs delete" id="'.$row["id"].'">Delete</button>';
 $data[] = $sub_array;
}

function get_all_data($connect)
{
 $query = "SELECT * FROM user";
 $result = mysqli_query($connect, $query);
 return mysqli_num_rows($result);
}

$output = array(
 "draw"    => intval($_POST["draw"]),
 "recordsTotal"  =>  get_all_data($connect),
 "recordsFiltered" => $number_filter_row,
 "data"    => $data
);

echo json_encode($output);

?>


Database



--
-- Database: `testing`
--

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

--
-- Table structure for table `user`
--

CREATE TABLE IF NOT EXISTS `user` (
  `id` int(11) NOT NULL,
  `first_name` varchar(200) NOT NULL,
  `last_name` varchar(200) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `user`
--

INSERT INTO `user` (`id`, `first_name`, `last_name`) VALUES
(1, 'John', 'Smith'),
(5, 'Peterson', 'Parker'),
(7, 'Rock', 'Madison'),
(8, 'Titan', 'Edge');

--
-- Indexes for dumped tables
--

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

--
-- AUTO_INCREMENT for dumped tables
--

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

29 comments:

  1. Do you have this tutorial using laravel 5.5 ? ThankYou :)

    ReplyDelete
  2. Hola, como podria hacer una caja con opcion multiple o combobox??

    ReplyDelete
  3. Awesome bro,, Thanks so much..

    ReplyDelete
  4. This is great, the only question I have is how do you edit(update) a blank column

    ReplyDelete
  5. update, delete and insert is not working for me just shows // // in message place, please help me

    ReplyDelete
  6. can i use select box in this code???

    ReplyDelete
  7. Can we make auto refresh this table after every 10 or more seconds?

    ReplyDelete
  8. after add button the columns appearing are non editable

    ReplyDelete
  9. contenteditable is not working

    ReplyDelete
  10. when I add the fourth bar, an error appears. what could it be?

    ReplyDelete
  11. An example about working:
    Whe have a table with 10 fields (columns) for every record (rows). We add 200 rows. Script shows, for example, the 50 last indexed rows. So, it paginates 8 pages. Now I need update a row on page 7 and I need update 5 of his 10 fields. If I modify a field and then click on second field it updates database and go back to first paginated page. So I need to repeat 4 times more this process (go to page 7, locate register row and modify next field) to update my 5 fields of this row. It is not comfortable or operational. How may I modify code to allow me to change all fields of a register without going back to first paginate page every time? (for example, adding a button next delete button to edit all row, not only a field)
    Thanks

    ReplyDelete
  12. thank you !! it's working fine

    ReplyDelete
  13. DataTables warning: table id=user_data - Ajax error. For more information about this error, please see http://datatables.net/tn/7

    This is a problem how can i solve it , please?

    ReplyDelete
  14. you give great example but i want some changes if possible than help me correct my code
    i want add one more column in database like code (it have numeric value like 123,123,231)
    problem is there when i fetch all record my where condition was not work if possible than help me

    ReplyDelete
  15. Pretty cool I was inspired by this page and created a similar example here:
    http://www.abrandao.com/2019/12/saving-contenteditable-html-using-php/

    I decided just to write to an html file and parse the corresponding div. code is up on Gtihub

    ReplyDelete
  16. Nice work, Everything doing fine. Thanks a lot my brother

    ReplyDelete
  17. Your fetch.php code is a mess.

    ReplyDelete
  18. its not working in firefox and edge edit option textbox not displaying.cant predict its in an editmode. chrome it is fine

    ReplyDelete
  19. hey when i am add record in table redirect on
    https://www.songlyricst.com

    ReplyDelete
  20. Update Panel is not working please help me and it is urgent!!!

    ReplyDelete
  21. DataTables warning: table id=user_data - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1


    plese

    ReplyDelete
  22. Good Job... very helpful full function
    HWD GROUP OF SOFTWARE

    ReplyDelete
  23. Great work, but you have some really bad issues with your code.

    For example:
    $(document).on('click', '#insert', function(){
    var first_name = $('#data1').text();
    var last_name = $('#data2').text();

    This only works if you're adding one row at the time, because it will always get the text() of the first row, you're trying to add, since you're using ID.

    ReplyDelete
  24. merci beaucoup pour ce tuto formidable

    ReplyDelete
  25. i keep getting this message:
    DataTables warning: table id=user_data - Ajax error. For more information about this error, please see http://datatables.net/tn/7

    ReplyDelete
  26. Nice project!
    If I update a field on 3rd page of paginate then returns to first page. How can I remain on 3rd page after it has changed.
    For example, I use 10 fields for every register. I search a register and then I'd like to change 3 fields of this. If I change one of them, it returns to first page of paginate and I must search again to change the second field. This is a litte bit buggy.
    Thanks for your script.

    ReplyDelete