Saturday 13 May 2017

Live Table Data Edit Delete using Tabledit Plugin in PHP



In this blog We have discussion on one more Jquery Table plugin like Tabledit. We will learn how to live table data edit or update, delete or remove and restore by using Jquery Tabledit plugin with PHP script and Mysql database. In one of the previous post we have seen X-editable JQuery plugin for perform operation like update of table data live with PHP and Mysql. The use of this plugin same functionality like update of table data live without refresh of web page.

In this plugin there is some advance feature like delete or remove of table data, with this plugin we can also perform restore of data operation also. By using this plugin we can make table as simple inline editor that means we can edit table data without opening of other window but can edit table on same position. This plugin is easily integrate with Bootstrap framework, so If we have use Bootstrap framework for web development then we do not want to write any style sheet code for table, button etc. If you have use this plugin in your web application then do want to write any code for update, delete or restore button, but this plugin has made button code when we have called this plugin.

If you have developed any single page crud application then in that application you can use this plugin for update or delete data server side operation by using this plugin. This plugin has use Ajax request so you can perform all operation without refresh of web page. If you want to use this plugin application then you should have table, because this plugin has been called on table tag, on other tag this plugin has not been worked. So if you have table then in jquery code you have Tabledit() method for called this plugin. Under this method there are many options available, but url and columns options is required. In url option you can define server script file in which you can write update or delete data operation and columns option there two other option like identifier and editable. In identifier option we can define table cell number in which we have store id of table primary key data and in editable option we can define in which table cell you want to activate editable column. In this option we can define table cell number and table column name we want to define for activate editable column. There are many option are available here for read complete documentation, example and download plugin.






Source Code


index.php



<?php
$connect = mysqli_connect("localhost", "root", "", "testing");
$query = "SELECT * FROM tbl_user ORDER BY id DESC";
$result = mysqli_query($connect, $query);
?>
<html>  
 <head>  
          <title>Live Table Data Edit Delete using Tabledit Plugin in 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>            
    <script src="jquery.tabledit.min.js"></script>
    </head>  
    <body>  
  <div class="container">  
   <br />  
   <br />  
   <br />  
            <div class="table-responsive">  
    <h3 align="center">Live Table Data Edit Delete using Tabledit Plugin in PHP</h3><br />  
    <table id="editable_table" class="table table-bordered table-striped">
     <thead>
      <tr>
       <th>ID</th>
       <th>First Name</th>
       <th>Last Name</th>
      </tr>
     </thead>
     <tbody>
     <?php
     while($row = mysqli_fetch_array($result))
     {
      echo '
      <tr>
       <td>'.$row["id"].'</td>
       <td>'.$row["first_name"].'</td>
       <td>'.$row["last_name"].'</td>
      </tr>
      ';
     }
     ?>
     </tbody>
    </table>
   </div>  
  </div>  
 </body>  
</html>  
<script>  
$(document).ready(function(){  
     $('#editable_table').Tabledit({
      url:'action.php',
      columns:{
       identifier:[0, "id"],
       editable:[[1, 'first_name'], [2, 'last_name']]
      },
      restoreButton:false,
      onSuccess:function(data, textStatus, jqXHR)
      {
       if(data.action == 'delete')
       {
        $('#'+data.id).remove();
       }
      }
     });
 
});  
 </script>


action.php



<?php  
//action.php
$connect = mysqli_connect('localhost', 'root', '', 'testing');

$input = filter_input_array(INPUT_POST);

$first_name = mysqli_real_escape_string($connect, $input["first_name"]);
$last_name = mysqli_real_escape_string($connect, $input["last_name"]);

if($input["action"] === 'edit')
{
 $query = "
 UPDATE tbl_user 
 SET first_name = '".$first_name."', 
 last_name = '".$last_name."' 
 WHERE id = '".$input["id"]."'
 ";

 mysqli_query($connect, $query);

}
if($input["action"] === 'delete')
{
 $query = "
 DELETE FROM tbl_user 
 WHERE id = '".$input["id"]."'
 ";
 mysqli_query($connect, $query);
}

echo json_encode($input);

?>


Database



--
-- Database: `testing`
--

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

--
-- Table structure for table `tbl_user`
--

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

--
-- Dumping data for table `tbl_user`
--

INSERT INTO `tbl_user` (`id`, `first_name`, `last_name`) VALUES
(2, 'John', 'Smith'),
(3, 'Carol', 'Ferrari'),
(4, 'Darrell', 'Mitten'),
(5, 'Elizbeth', 'Noyes'),
(6, 'Edna', 'William'),
(7, 'Roy', 'Hise'),
(8, 'Sheila', 'Aguinaldo'),
(9, 'Peter', 'Goad'),
(10, 'Kenneth', 'Simons'),
(11, 'Dona', 'Huber'),
(12, 'William', 'Soliz'),
(13, 'Nelson', 'Dismuke'),
(14, 'Sarah', 'Thomas');

--
-- Indexes for dumped tables
--

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

--
-- AUTO_INCREMENT for dumped tables
--

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

41 comments:

  1. Where this script jquery.tabledit.min.js ?

    ReplyDelete
  2. Sorry for my stupid question about the script)
    Thank you very much for your lessons.

    ReplyDelete
  3. how to disable space in field (td) in the example

    ..ty

    ReplyDelete
  4. no edit or delete buttonapears in the page whwn coded

    ReplyDelete
  5. Hello Sir
    I live your tutorials
    My question is how to add a add record function?

    I did go over your tutorial
    http://www.webslesson.info/2016/06/insert-form-data-using-jquery-post-method-with-php-mysql.html
    added this to it, and the data is inserted however not displayed in the table without refresh - how can this be done.

    Your feedback is most welcome

    Kind regards

    ReplyDelete
  6. I think the best website to get started with Ajax coding.
    Well Done and Awensome Website

    ReplyDelete
  7. the index.php code isn't match used in demo

    ReplyDelete
  8. On Deleting Record it remains in the HTML table but in actual it disappears! Any help?

    ReplyDelete
  9. Not Works Demo :(
    Only rediect to StackOverflow

    ReplyDelete
  10. Something is not correct. It will not work correctly whit remove or edit. The button shows, but it does nothing.

    ReplyDelete
  11. i can't see your buttons as above coding......

    ReplyDelete
  12. Edit and delete buttons are not appearing?

    ReplyDelete
  13. pls give jquery.tabledit.min.js file

    ReplyDelete
  14. wheres the edit and delete button?

    ReplyDelete
  15. Hi,
    great tutorial, but can you please help me? how to add 8 more columns?
    Thank you

    ReplyDelete
  16. Dear sir I want to use this plugin in codeigniter please make video for this.
    I hope you will do this
    Thanks for this tutorial

    ReplyDelete
  17. It doesn't work on my end. the data is not changed

    ReplyDelete
  18. good job man thanks for this. but the buttons is not showing in me, how to fix it?

    ReplyDelete
    Replies
    1. hey did you find a way to fix it? I have the same problem.

      Delete
    2. I think it's just advertising "click-bait". The demo only shows the 'th' header fields and a huge advertisement. Doesn't show the table 'td' cells.

      Delete
  19. The 2 edit and delete button are not appearing please help

    ReplyDelete
  20. me also button is not showing how to fix it edit and delete buttons are not showing

    ReplyDelete
  21. someone removed all the data

    ReplyDelete
  22. why the icon edit and delete not appear on my end. I copied straightly from your code

    ReplyDelete
  23. where and how could I apply some javascript validations for the fields before updating?


    ReplyDelete
  24. nice code! works fine. and it is well documented.

    ReplyDelete
  25. Is this 'ONLINE DEMO' supposed to actually work, or is it just another mechanism for 'click bait'? Because I see nothing but the table header.

    ReplyDelete
  26. ... again, I'm saying ... is this demo supposed to work or are you simply using it as click-bait for advertising?

    ReplyDelete
  27. .. again, I ask -- is this demo supposed to actually work or are you simply putting it out here as 'click-bait' to grab advertising money?

    ReplyDelete
  28. hai sir i want jquery.tabledit.min.js file that's missing

    ReplyDelete
  29. The edit or delete buttons are not showing. How can I fix it?

    ReplyDelete
  30. please help me out those 2 buttons are not appearing

    ReplyDelete
  31. Can you tell me where the id="editable_table" is located. I have not been able to find it in any of the files and I would like to change the look just a little bit

    thank you

    ReplyDelete
  32. using this code it can not show edit and delete option

    ReplyDelete
  33. My Table is not editable .There is no button are avilable

    ReplyDelete
  34. i can't able to use 7 columns

    ReplyDelete
  35. i can't able to see buuttons to edit

    ReplyDelete