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;

95 comments:

  1. It is really nice!
    Can I use another type of input? e.g.: date, datetime, select, checkbox

    ReplyDelete
  2. for me it doesnt work I dont know why

    ReplyDelete
  3. Thx for very nice tutorial. How can i use "onkeydown with keycode=40" to setfocus to one row down, to the same field when pressing the "down-arrow" on keyboard.
    I have tried with no success

    $(document).on('keydown','#user_data > tbody tr .num',function (e) {
    if (e.which === 40) {
    $(this).parents("tr").next("tr").find('.num').focus();
    }
    });

    Please be so kind to assist as the inline edit blocks me

    ReplyDelete
  4. Very nice tutorial. How can one adapt it to navigate with arrow keys down ie keycode=40 Pls be so kind to assist

    ReplyDelete
  5. Great and very smooth. Best inline editor I have found and you also inc add/delete records with a search to boot. Thanks!!!

    ReplyDelete
  6. Hi, this script is fine, but it does not work.
    Thank you very much

    ReplyDelete
  7. I have a question, what if someone submits a form like this using script
    f o r m action="{ YPUR URL}delete.php" method="POST" id="myForm">
    i n p u t type="text" name="id" value="45" />
    /f o r m>

    Is there any way to secure these CRUD operations using AJAX

    ReplyDelete
  8. i have asked in previous post also, now if i run this script then i can perform the CRUD on ur site. how to make it Secure, do you have any suggestions, here is the code which i used to add.

    form action="http://www.itidabhoi.org/demo/live-datatables-insert-update-delete/insert.php" method="POST" id="myForm">
    input type="text" name="first_name" value="BHANU" >
    input type="text" name="last_name" value="DHUNGANA" >
    input type="submit" value="submit" name="">
    f o r m>

    ReplyDelete
  9. i tried ur code... but the insertion is not working

    ReplyDelete
  10. Thank you, code was helpful. But one problem, am not getting the: js,ajax,jq to work locally, only when connected online. Plz help.

    ReplyDelete
  11. the live add function is not working

    ReplyDelete
  12. Thank you sir for your great help

    ReplyDelete
  13. The update button is missing. I guess that is homework!

    ReplyDelete
  14. Cordial regards, I need a field to be date type, you could help me with this information. Thank you

    ReplyDelete
  15. how i can create row number?

    ReplyDelete
  16. Thanks love the code... trying to customize for my needs. Anyway to have 2 columns in the database be divided and turned into a percent? I know how to do it with a mySQL query but implementing it into this code is not something I've figured out yet.

    ReplyDelete
  17. Realy nice example. thank you!!

    ReplyDelete

  18. Hello good afternoon. Could you please help me with your post? As I would do to register images. I will be very grateful to myself on this matter

    ReplyDelete

  19. Hello good afternoon. Could you please help me with your post? As I would do to register images. I will be very grateful to me in this question and a button next to the "delete" that can be visualized the id of the clicked line? Please helpe-me

    ReplyDelete
  20. How to add PDF/Excel export button for print purposes?

    ReplyDelete
  21. thnk u guys for these amazing topis are very intuitive
    but cant u try to use real human voice in tutorials cause they are awesome and help alot of people but that reader sometime misspell word and i like to full understand thing and how to are used.

    Thanks anyways great material great topics awesome coding style
    #perfect

    ReplyDelete
  22. what if you do want to leave fields blank

    ReplyDelete
  23. This article is very useful for newbies web developers. Can you add some option into this code. such as - data table excel/pdf export button and manipulate drop down data one or multiple rows from another table.

    ReplyDelete
  24. How can I stop the table from refreshing after updating I.E {Show 100 entries goes back to show 10entries}...

    ReplyDelete
  25. Great instruction, but one problem, with a table with more than one page display of data, it would be nice upon update, insert, or delete to return to the page of which the update, insert, or delete was performed. The function fetch_data redisplays the table always only the first page of date records, not the page you were working on in this case.

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

    ReplyDelete
  27. you can enter a field checkbox?

    ReplyDelete
  28. how to get edit cell on double click .. please help

    ReplyDelete
  29. how to get edit cell on double click instead of single click .. please do help

    ReplyDelete
  30. $('#user_data').on( 'dbclick', 'tbody td', function () {
    dataTable.cell( this ).edit();
    } );

    tried this for double click but not working..

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

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

    ReplyDelete
  33. hello there,

    nice tutorial!
    i was able to create the datatable and it works with my database.
    i have a question: how could i add a kind of validation form on the line for update the database? they are not input field? thx

    $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';


    ginzu

    ReplyDelete
  34. nice tut,

    i was able to create the datatable and interact with my db (insert, update, delete)..
    but how could i add a validation form when i want to update the fields?
    these are not input field. do you have a tips?

    $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';

    thanks in advance

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

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

    ReplyDelete
    Replies
    1. i am also facing the same problem,could u solve?

      Delete
  37. can i use select box in this code???

    ReplyDelete
  38. everything works just fine but i have a problem with delete button..when i delete, nothing happend..help me please .. my email = blazeartz@gmail.com

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

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

    ReplyDelete
  41. contenteditable is not working

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

    ReplyDelete
  43. DataTables warning: table id=user_data - Invalid JSON response. ....what's the issue...pls explain

    ReplyDelete
  44. DataTables warning: table id=user_data - Invalid JSON response. ....what's the issue...pls explain


    ReplyDelete
  45. contenteditable class="update" style="min-height:15px"

    https://stackoverflow.com/questions/24793551/firefox-does-not-allow-blank-contenteditable-area

    ReplyDelete
  46. i dont know how to add more columns i tried and it ended up crashing the whole thing i wanted to have at least 13 columns but didnt know what to do

    ReplyDelete
  47. Please suggest same thing with laravel .

    ReplyDelete
  48. this same thig how can use in codeigniter with serch and pdf

    ReplyDelete
  49. undefined index length and start

    ReplyDelete
  50. Wonderful work, congratulation!

    Please it is possible show how create in this a combobox column?

    ReplyDelete
  51. contenteditabel is not working .can some one please help

    ReplyDelete
  52. bro your demo site is hijacked

    ReplyDelete
  53. bro your demo site is hijacked

    ReplyDelete
  54. Hello, it is possible to give this source code with connection « new PDO » ???

    ReplyDelete
  55. how to insert date picker in every column in this code? i try many times but error appears.

    ReplyDelete
  56. 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
  57. thank you !! it's working fine

    ReplyDelete
  58. 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
  59. 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
  60. 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
  61. Nice work, Everything doing fine. Thanks a lot my brother

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

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

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

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

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


    plese

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

    ReplyDelete
  68. 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
  69. merci beaucoup pour ce tuto formidable

    ReplyDelete
  70. 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
  71. 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
  72. error
    Frist Name

    frist=first

    ReplyDelete
  73. the code runs for me, except the update part. i already checked several times, i think there's no missing letters and incorrect words....but why not updating?

    ReplyDelete
  74. is the the column_name different from column at update section?

    ReplyDelete
  75. why update feature not working? the update.php is looking for defined id...every time i clicked to update the data.

    ReplyDelete
  76. i am very happy this example is sussessfully run

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

    ReplyDelete
  78. Excellent! It works for me perfectly.
    Excelente! Me funciona a la perfección.

    ReplyDelete
  79. excellent work.
    Is there any way out that I can add class or id attribute to eac table row that is fetched through databse.

    ReplyDelete
  80. how to fetch more than 500 rows and display it on the datatabe using php and mysql

    ReplyDelete