[DATATABLE] How to use Tabledit plugin with jQuery Datatable in PHP Ajax (ok)

https://www.webslesson.info/2020/05/make-editable-datatable-using-jquery-tabledit-plugin-with-php-ajax.html

C:\xampp\htdocs\wpclidemo\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>

C:\xampp\htdocs\wpclidemo\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);

C:\xampp\htdocs\wpclidemo\database_connection.php

<?php
//database_connection.php
$connect = new PDO("mysql:host=localhost; dbname=testing", "root", "");

C:\xampp\htdocs\wpclidemo\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);
}

C:\Users\Administrator\Downloads\testing.sql

-- phpMyAdmin SQL Dump
-- version 5.1.3
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Dec 18, 2022 at 04:56 AM
-- Server version: 10.4.24-MariaDB
-- PHP Version: 7.4.28
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
--
-- 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 123', 'Female');
--
-- 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;
COMMIT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Previous[DATATABLE] DataTables.net with DataTableQueryBuilder sample input filter (ok)Next[DATATABLE] DataTables Custom Filter Demo (ok)

Last updated 2 years ago

<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>

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

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

-- phpMyAdmin SQL Dump -- version 5.1.3 -- https://www.phpmyadmin.net/ -- -- Host: 127.0.0.1 -- Generation Time: Dec 18, 2022 at 04:56 AM -- Server version: 10.4.24-MariaDB -- PHP Version: 7.4.28 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; START TRANSACTION; SET time_zone = "+00:00"; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8mb4 */; -- -- 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 123', 'Female'); -- -- 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; COMMIT; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;