July 15, 2020

Inline Edit, Save & Delete Data Using jQuery Tabledit With Ajax and PHP

In this article, I show you how to do inline edit, save & delete data using jQuery Tabledit with Ajax and PHP. With the jQuery Tabledit plugin, we can edit, save, or delete data from the MySQL database. By using PHP we going to fetch the data from MySQL database and display on the Bootstrap DataTable. Then using Ajax and jQuery we going to edit, update, or delete the data from the MySQL database.

Inline edit with data sorting, search, and pagination helps us to display and update the data quickly without page refresh. Overall it’s reduce working hours with the best access of User Interface(UI) and allows you to load a huge number of data from MySQL to the webpage.

Below you have the code, using that you can integrate the with your project.

Database

--
-- Database: `inline_edit`
--

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

--
-- Table structure for table `emp_database`
--

CREATE TABLE `emp_database` (
  `id` int(11) NOT NULL,
  `emp_name` varchar(250) NOT NULL,
  `emp_designation` varchar(250) NOT NULL,
  `gender` enum('Male','Female') NOT NULL,
  `emp_contact` varchar(15) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

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

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `emp_database`
--
ALTER TABLE `emp_database`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=20;
COMMIT;

-- Database: `inline_edit`

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

-- Table structure for table `emp_database`

CREATE TABLE `emp_database` (

`id` int(11) NOT NULL,

`emp_name` varchar(250) NOT NULL,

`emp_designation` varchar(250) NOT NULL,

`gender` enum('Male','Female') NOT NULL,

`emp_contact` varchar(15) NOT NULL

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- Indexes for dumped tables

-- Indexes for table `emp_database`

ALTER TABLE `emp_database`

ADD PRIMARY KEY (`id`);

-- AUTO_INCREMENT for dumped tables

-- AUTO_INCREMENT for table `emp_database`

ALTER TABLE `emp_database`

MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=20;

COMMIT;

If you look for a database with dummy data, then you can download now with the project files.

index.php

<html>
    <head>
        <title>Inline Edit, Save & Delete Data Using jQuery Tabledit With Ajax and PHP</title>
        <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/3.3.6/css/bootstrap.min.css" />
        <link rel="stylesheet" href="https://cdn.datatables.net/1.10.12/css/dataTables.bootstrap.min.css" />
    </head>
    <body>
        <div class="container">
            <h1 align="center">Inline Edit, Save & Delete Data Using jQuery Tabledit With Ajax and PHP</h1>
            <br />
            <h3>Employee Database</h3>
            <div class="panel panel-default">
                <div class="panel-body">
                    <div class="table-responsive">
                        <table id="emp_list" class="table table-bordered">
                            <thead>
                                <tr>
                                    <th>Emp ID</th>
                                    <th>Employee Name</th>
                                    <th>Employee Designation</th>
                                    <th>Gender</th>
                                    <th>Contact Number</th>
                                </tr>
                            </thead>
                            <tbody></tbody>
                        </table>
                    </div>
                </div>
            </div>
        </div>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/2.2.3/jquery.min.js"></script>
        <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>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/3.3.6/js/bootstrap.min.js"></script>
        <script src="https://markcell.github.io/jquery-tabledit/assets/js/tabledit.min.js"></script>
        <script type="text/javascript" language="javascript">
            $(document).ready(function () {
                var dataTable = $("#emp_list").DataTable({
                    processing: true,
                    serverSide: true,
                    order: [],
                    ajax: {
                        url: "fetch.php",
                        type: "POST",
                    },
                });

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

<html>

<head>

<title>Inline Edit, Save & Delete Data Using jQuery Tabledit With Ajax and PHP</title>

</head>

<body>

<h1 align="center">Inline Edit, Save & Delete Data Using jQuery Tabledit With Ajax and PHP</h1>

<br />

<h3>Employee Database</h3>

<thead>

<tr>

<th>Employee Name</th>

<th>Employee Designation</th>

<th>Gender</th>

<th>Contact Number</th>

</tr>

</thead>

</table>

</div>

$(document).ready(function () {

var dataTable = $("#emp_list").DataTable({

processing: true,

serverSide: true,

order: [],

ajax: {

url: "fetch.php",

type: "POST",

});

$("#emp_list").on("draw.dt", function () {

$("#emp_list").Tabledit({

url: "edit.php",

dataType: "json",

columns: {

identifier: [0, "id"],

editable: [

[1, "emp_name"],

[2, "emp_designation"],

[3, "gender", '{"1":"Male","2":"Female"}'],

[4, "emp_contact"],

restoreButton: false,

onSuccess: function (data, textStatus, jqXHR) {

if (data.action == "delete") {

$("#" + data.id).remove();

$("#emp_list").DataTable().ajax.reload();

}

});

</script>

</body>

</html>

conn.php

<?php 
$connect = new PDO("mysql:host=localhost; dbname=inline_edit", "root", "");
?>

<?php

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

fetch.php

<?php
//DB Connection
include 'conn.php';

$column = ["id", "emp_name", "emp_designation", "gender", "emp_contact"];

$query = "SELECT * FROM emp_database ";

if (isset($_POST["search"]["value"])) {
    $query .=
        '
 WHERE emp_name LIKE "%' .
        $_POST["search"]["value"] .
        '%" 
 OR emp_designation LIKE "%' .
        $_POST["search"]["value"] .
        '%" 
 OR gender LIKE "%' .
        $_POST["search"]["value"] .
        '%" 
 OR emp_contact 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 ASC ';
}
$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 = [];

foreach ($result as $row) {
    $sub_array = [];
    $sub_array[] = $row['id'];
    $sub_array[] = $row['emp_name'];
    $sub_array[] = $row['emp_designation'];
    $sub_array[] = $row['gender'];
    $sub_array[] = $row['emp_contact'];
    $data[] = $sub_array;
}

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

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

echo json_encode($output);

?>

<?php

//DB Connection

include 'conn.php';

$column = ["id", "emp_name", "emp_designation", "gender", "emp_contact"];

$query = "SELECT * FROM emp_database ";

if (isset($_POST["search"]["value"])) {

$query .=

WHERE emp_name LIKE "%' .

$_POST["search"]["value"] .

'%"

OR emp_designation LIKE "%' .

$_POST["search"]["value"] .

'%"

OR gender LIKE "%' .

$_POST["search"]["value"] .

'%"

OR emp_contact 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 ASC ';

}

$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 = [];

foreach ($result as $row) {

$sub_array = [];

$sub_array[] = $row['id'];

$sub_array[] = $row['emp_name'];

$sub_array[] = $row['emp_designation'];

$sub_array[] = $row['gender'];

$sub_array[] = $row['emp_contact'];

$data[] = $sub_array;

}

function count_all_data($connect)

{

$query = "SELECT * FROM emp_database";

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

$statement->execute();

return $statement->rowCount();

}

$output = [

'draw' => intval($_POST['draw']),

'recordsTotal' => count_all_data($connect),

'recordsFiltered' => $number_filter_row,

'data' => $data,

];

echo json_encode($output);

edit.php

<?php
//DB Connection
include 'conn.php';

if ($_POST['action'] == 'edit') {
    $data = [
        ':emp_name' => $_POST['emp_name'],
        ':emp_designation' => $_POST['emp_designation'],
        ':gender' => $_POST['gender'],
        ':emp_contact' => $_POST['emp_contact'],
        ':id' => $_POST['id'],
    ];

    $query = "
 UPDATE emp_database 
 SET emp_name = :emp_name, 
 emp_designation = :emp_designation, 
 gender = :gender,
 emp_contact = :emp_contact 
 WHERE id = :id
 ";
    $statement = $connect->prepare($query);
    $statement->execute($data);
    echo json_encode($_POST);
}

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

?>

<?php

//DB Connection

include 'conn.php';

if ($_POST['action'] == 'edit') {

$data = [

':emp_name' => $_POST['emp_name'],

':emp_designation' => $_POST['emp_designation'],

':gender' => $_POST['gender'],

':emp_contact' => $_POST['emp_contact'],

':id' => $_POST['id'],

];

$query = "

UPDATE emp_database

SET emp_name = :emp_name,

emp_designation = :emp_designation,

gender = :gender,

emp_contact = :emp_contact

WHERE id = :id

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

$statement->execute($data);

echo json_encode($_POST);

}

if ($_POST['action'] == 'delete') {

$query =

DELETE FROM emp_database

WHERE id = '" .

$_POST["id"] .

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

$statement->execute();

echo json_encode($_POST);

}

Conclusion:

I hope this article will help you to do inline edit, update, and delete data from MySQL database using jQuery Tabledit plugin. If you have any doubts about this topic let me know in the comment section. If you like this article share it with your friends.

Preview & Download

Mraj

Creative Designer & Developer specialist by the spirit and a loving blogger by thoughts. If you have any questions let me drop an email with the article name to the following email id: [email protected]

Inline Edit, Save & Delete Data Using jQuery Tabledit With Ajax and PHP

Share:

Preview & Download

Mraj

Don't miss on latest updates