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);
}


?>

26 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
    Replies
    1. what's the purpose of AUTO_INCREMENT=36;

      Delete
    2. i got error with id sample_data. Is sample_data is just an arbitrary name for the id of the table for css feature? if so then why I got an error?

      Delete
    3. i mean to say, where does sample_data originate?

      Delete
  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
    Replies
    1. hi i am facing the same issue, could you please help me if you find the solution

      Delete
  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
  9. Hai, how to use an another table from php database as an select option?kindly help me

    ReplyDelete
  10. Parabens... congratulations...

    ReplyDelete
  11. Sir,

    Your tutorials are great! But I got this error please help me.

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

    ReplyDelete
  12. Solution :

    1.add updated datatable scrips

    https://cdn.datatables.net/1.10.23/css/dataTables.bootstrap4.min.css
    https://cdn.datatables.net/1.10.23/js/jquery.dataTables.min.js
    https://cdn.datatables.net/1.10.23/js/dataTables.bootstrap4.min.js

    2. the replace this code in search query

    if($_POST["search"]["value"]){
    if(isset($_POST["search"]["value"]))
    {
    $query .= '
    WHERE company_name LIKE "%'.$_POST["search"]["value"].'%"
    OR email_domain LIKE "%'.$_POST["search"]["value"].'%"
    OR company_linked_url LIKE "%'.$_POST["search"]["value"].'%"
    OR industry LIKE "%'.$_POST["search"]["value"].'%"
    OR subindustry LIKE "%'.$_POST["search"]["value"].'%"
    OR sic LIKE "%'.$_POST["search"]["value"].'%"
    OR naics LIKE "%'.$_POST["search"]["value"].'%"
    OR revenue_range LIKE "%'.$_POST["search"]["value"].'%"
    OR employee_size LIKE "%'.$_POST["search"]["value"].'%"
    ';
    }
    }

    3. replace this code in limit query

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

    ReplyDelete
  13. how to display dropdown with selected value when edit ??

    ReplyDelete
    Replies
    1. yes i also want to know can anyone help us

      Delete
  14. Hello Sir, how can use it with MVC?

    ReplyDelete
  15. Hello sir,
    I am getting these error while I am checking in developer tool.

    Notice: Undefined index: length in D:\xampp\htdocs\SCM\Modify\fetch.php on line 32

    Notice: Undefined index: start in D:\xampp\htdocs\SCM\Modify\fetch.php on line 34

    Notice: Undefined index: length in D:\xampp\htdocs\SCM\Modify\fetch.php on line 34

    Notice: Undefined index: draw in D:\xampp\htdocs\SCM\Modify\fetch.php on line 73
    {"draw":0,"recordsTotal":4,"recordsFiltered":4,"data":[]}


    I have added 2 more column. I able to fetch data and only column is working fine while I edit that but other column data lost if I edit that.

    My column:
    Id M_Name Quantity M_Type Supplier Store Location

    Only column Quantity working in edit mode.

    ReplyDelete
  16. Hi how can i create dropdwon list for columns ?

    ReplyDelete
  17. Hi,

    How do I show only genders for example male

    ReplyDelete
  18. How do I show only genders for example male

    ReplyDelete