Sunday, 17 May 2020

Make Editable Datatable using jQuery Tabledit Plugin with PHP Ajax



Hey Guys Are you looking of tutorial on How can we use jQuery Datatable plugin and jQuery Tabledit plugin together in PHP application by using Ajax. If yes, then you have come on the right page, because in this tutorial, we have will describe you step by step how can we use jQuery Tabledit plugin with Datatable plugin and convert Datable grid into editable PHP grid using Ajax. In this post we will create Live Editable Datatable with jQuery Tabledit plugin using PHP script and Ajax, and in this Live Editable Datatable user can perform Mysql Edit or update data operation and delete or remove data operation by using jQuery Tabledit plugin without refresh of web page because tabledit plugin will send Ajax request to PHP script for Mysql edit or delete data operation.

In this tutorial, we have use jQuery Datatable plugin, which is very powerful jQuery plugin for creating dynamic table grid for display data on web page in tabular format. This plugin has provide functionality like searching, sorting and pagination without any configuratin. In this post we have use jQuery Datatable plugin for load mysql table data and display on web page in PHP table grid with different feature like live searching of data, table column sorting of data, pagination. This all feature will work with PHP server-side operation. This is because here we have use PHP script with jQuery Datatable plugin for fetch data from Mysql table and send back to jquery Datatable plugin in json format because here we will use Ajax for send and received data from PHP script.




Same way here we have also use one more jQuery plugin with Datatable plugin and here we have use jQuery Tabledit plugin with jQuery Datatable plugin for Inline Datatable editing and deleting with PHP script using Ajax. By using Tabledit plugin, it will convert simple html table into inline editable table with feature like live edit and delete of data. This is plugin is compatiable with Bootstrap library. If you want to create live editable table then you can use this jQuery Tabledit plugin which will send Ajax request to PHP script for edit or delete of data from database. In this tutorial, we have use tabledit plugin with jQuery Datatable for convert Datatable grid into inline editable Datatable with edit or delete Datatable data without refresh of web page. So, if you want to make Live Datatable Edit Delete mysql table data then you can use tabledit plugin with jQuery Datatable using PHP script with Ajax. This post will help you to make datatable editable by using jQuery tabledit plugin with PHP script and Ajax. Below you can find complete source code of this tutorial.



Database



--
-- Database: `testing`
--

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

--
-- Table structure for table `tbl_sample`
--

CREATE TABLE `tbl_sample` (
  `id` int(11) NOT NULL,
  `first_name` varchar(250) NOT NULL,
  `last_name` varchar(250) NOT NULL,
  `gender` enum('Male','Female') NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `tbl_sample`
--

INSERT INTO `tbl_sample` (`id`, `first_name`, `last_name`, `gender`) VALUES
(1, 'John', 'Smith', 'Male'),
(2, 'Peter', 'Parker', 'Male'),
(4, 'Donna', 'Huber', 'Male'),
(5, 'Anastasia', 'Peterson', 'Male'),
(6, 'Ollen', 'Donald', 'Male'),
(10, 'Joseph', 'Stein', 'Male'),
(11, 'Wilson', 'Fischer', 'Male'),
(12, 'Lillie', 'Kirst', 'Female'),
(13, 'James', 'Whitchurch', 'Male'),
(14, 'Timothy', 'Brewer', 'Male'),
(16, 'Sally', 'Martin', 'Male'),
(17, 'Allison', 'Pinkston', 'Male'),
(18, 'Karen', 'Davis', 'Male'),
(19, 'Jaclyn', 'Rocco', 'Male'),
(20, 'Pamela', 'Boyter', 'Male'),
(21, 'Anthony', 'Alaniz', 'Male'),
(22, 'Myrtle', 'Stiltner', 'Male'),
(23, 'Gary', 'Hernandez', 'Male'),
(24, 'Fred', 'Jeffery', 'Male'),
(25, 'Ronald', 'Stjohn', 'Male'),
(26, 'Stephen', 'Mohamed', 'Male'),
(28, 'Michael', 'Dyer', 'Male'),
(29, 'Betty', 'Beam', 'Male'),
(30, 'Anna', 'Peterson', 'Female'),
(31, 'Peter', 'Stodola', 'Male'),
(32, 'Ralph', 'Jones', 'Male');

--
-- Indexes for dumped tables
--

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

--
-- AUTO_INCREMENT for dumped tables
--

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





database_connection.php



<?php

//database_connection.php

$connect = new PDO("mysql:host=localhost; dbname=testing", "root", "");

?>


index.php



<html>
 <head>
  <title>How to use Tabledit plugin with jQuery Datatable in PHP Ajax</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://cdn.datatables.net/1.10.12/js/jquery.dataTables.min.js"></script>
  <script src="https://cdn.datatables.net/1.10.12/js/dataTables.bootstrap.min.js"></script>  
  <link rel="stylesheet" href="https://cdn.datatables.net/1.10.12/css/dataTables.bootstrap.min.css" />
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
  <script src="https://markcell.github.io/jquery-tabledit/assets/js/tabledit.min.js"></script>
 </head>
 <body>
  <div class="container">
   <h3 align="center">How to use Tabledit plugin with jQuery Datatable in PHP Ajax</h3>
   <br />
   <div class="panel panel-default">
    <div class="panel-heading">Sample Data</div>
    <div class="panel-body">
     <div class="table-responsive">
      <table id="sample_data" class="table table-bordered table-striped">
       <thead>
        <tr>
         <th>ID</th>
         <th>First Name</th>
         <th>Last Name</th>
         <th>Gender</th>
        </tr>
       </thead>
       <tbody></tbody>
      </table>
     </div>
    </div>
   </div>
  </div>
  <br />
  <br />
 </body>
</html>

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

 var dataTable = $('#sample_data').DataTable({
  "processing" : true,
  "serverSide" : true,
  "order" : [],
  "ajax" : {
   url:"fetch.php",
   type:"POST"
  }
 });

 $('#sample_data').on('draw.dt', function(){
  $('#sample_data').Tabledit({
   url:'action.php',
   dataType:'json',
   columns:{
    identifier : [0, 'id'],
    editable:[[1, 'first_name'], [2, 'last_name'], [3, 'gender', '{"1":"Male","2":"Female"}']]
   },
   restoreButton:false,
   onSuccess:function(data, textStatus, jqXHR)
   {
    if(data.action == 'delete')
    {
     $('#' + data.id).remove();
     $('#sample_data').DataTable().ajax.reload();
    }
   }
  });
 });
  
}); 
</script>


fetch.php



<?php

//fetch.php

include('database_connection.php');

$column = array("id", "first_name", "last_name", "gender");

$query = "SELECT * FROM tbl_sample ";

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

if(isset($_POST["order"]))
{
 $query .= 'ORDER BY '.$column[$_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'];
}

$statement = $connect->prepare($query);

$statement->execute();

$number_filter_row = $statement->rowCount();

$statement = $connect->prepare($query . $query1);

$statement->execute();

$result = $statement->fetchAll();

$data = array();

foreach($result as $row)
{
 $sub_array = array();
 $sub_array[] = $row['id'];
 $sub_array[] = $row['first_name'];
 $sub_array[] = $row['last_name'];
 $sub_array[] = $row['gender'];
 $data[] = $sub_array;
}

function count_all_data($connect)
{
 $query = "SELECT * FROM tbl_sample";
 $statement = $connect->prepare($query);
 $statement->execute();
 return $statement->rowCount();
}

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

echo json_encode($output);

?>


action.php



<?php

//action.php

include('database_connection.php');

if($_POST['action'] == 'edit')
{
 $data = array(
  ':first_name'  => $_POST['first_name'],
  ':last_name'  => $_POST['last_name'],
  ':gender'   => $_POST['gender'],
  ':id'    => $_POST['id']
 );

 $query = "
 UPDATE tbl_sample 
 SET first_name = :first_name, 
 last_name = :last_name, 
 gender = :gender 
 WHERE id = :id
 ";
 $statement = $connect->prepare($query);
 $statement->execute($data);
 echo json_encode($_POST);
}

if($_POST['action'] == 'delete')
{
 $query = "
 DELETE FROM tbl_sample 
 WHERE id = '".$_POST["id"]."'
 ";
 $statement = $connect->prepare($query);
 $statement->execute();
 echo json_encode($_POST);
}


?>

8 comments:

  1. If you anyone want to <a href="https://www.phptpoint.com/advanced-php-tutorial/>learn advance php</a>.
    then visit here to get full tutorial.




    ReplyDelete
  2. hello sir i need your help plz come in messenger

    ReplyDelete
  3. Sir,



    Thank you for the nice tutorial.

    How can I use data from another table and put it as options of a select element in the editable columns? For example, I have a list of hundred of classes and I want to use it as options to the select element.

    ReplyDelete
  4. Hi webslesson, thank you for your lesson on leveraging jquery tabledit with search capability. However, please advise how to use select if we do not use column type 'ENUM' but 'INT' using switch option?

    ReplyDelete
  5. Super génial ! J'en avais vraiment besoin et là ça me sauve ! Grand merci à vous.

    ReplyDelete
  6. Hi sir big thanks for your great tutorials. Please i would like to know something:
    IS IT POSSIBLE TO USE TABLEDIT PLUGIN with A SQL VIEW, i ask because when i try to practise this tutorial we many tables join into VIEW, i have the following error message: DataTables warning: table id=sample_data - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1

    Thanks to answer me !

    ReplyDelete
  7. Hi! In the dropdown (male, female), how do I populate it with the values from a column of a table in my database?

    Thank you!

    ReplyDelete
  8. in fetch.php file i am reciving an error of
    Undefined index: length in C:\xampp\htdocs\php\fetch.php on line 31
    kindly help me out to remove this error.

    ReplyDelete