close
DataTables is a JavaScript library that helps build data tables and instantly add advanced features to HTML tables. In the web application, the DataTables jQuery plugin is the best option to display data in table view with search and pagination features. DataTables server-side processing allows to fetch data from the database and display live data in tabular format. Advanced features such as search, filter, sorting, and pagination can be attached to the data table without extra effort.
CRUD operations are the most used functionality in the data table. The DataTables can be enhanced with CRUD feature to display and manage data in HTML tables. In this tutorial, we will show you how to integrate DataTables CRUD operations with Ajax using PHP and MySQL. You can manage data in DataTables with live add/edit/delete features using jQuery, Ajax, and PHP.
In the example script, we will display dynamic data from the database and add CRUD functionality in DataTables with PHP.
Before getting started to integrate the CRUD feature in DataTables with PHP, take a look at the file structure.
datatables_crud_with_php/ ├── config.php ├── dbConnect.php ├── index.html ├── fetchData.php ├── ssp.class.php ├── eventHandler.php ├── DataTables/ │ ├── datatables.min.css │ └── datatables.min.js ├── js/ │ ├── jquery.min.js │ ├── bootstrap.min.js │ ├── sweetalert2.all.min.js └── css/ ├── bootstrap.min.css └── style.css
A table is required in the database in which the data will be managed. The following SQL creates a members table with some basic fields in the MySQL database.
CREATE TABLE `members` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
`last_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
`email` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`gender` enum('Male','Female') COLLATE utf8_unicode_ci NOT NULL,
`country` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
`created` datetime NOT NULL DEFAULT current_timestamp(),
`modified` datetime NOT NULL DEFAULT current_timestamp(),
`status` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1=Active | 0=Inactive',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
In the config.php file, some configuration variables are defined.
<?php
// Database credentials
define('DB_HOST', 'MySQL_Database_Host');
define('DB_USER', 'Database_Username');
define('DB_PASS', 'Database_Password');
define('DB_NAME', 'Database_Name');
?>
The dbConnect.php file is used to connect and select the database.
<?php
// Include config file
include_once 'config.php';
// Connect to the database
$conn = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
if($conn->connect_error){
die("Failed to connect with MySQL: " . $conn->connect_error);
}
?>
On page load, the DataTables class is initialized, and dynamic data is fetched from the server side and listed in an HTML table with Add/Edit/Delete buttons.
jQuery Library:
Include the jQuery library that is required to initiate Ajax and build modal popup.
<script src="js/jquery.min.js"></script>
Bootstrap Library:
Include the Bootstrap CSS and JS library files, which will be used to integrate the popup dialog.
<link rel="stylesheet" href="css/bootstrap.min.css">
<script src="js/bootstrap.min.js"></script>
SweetAlert Plugin:
We will use the SweetAlert2 plugin to display alert notification messages on the dialog box.
<script src="js/sweetalert2.all.min.js"></script>
DataTables Library:
Include the DataTables CSS and JS library files.
<link rel="stylesheet" type="text/css" href="DataTables/datatables.min.css"/>
<script type="text/javascript" src="DataTables/datatables.min.js"></script>
HTML Table with Add/Edit/Delete Links:
Create an HTML table structure and specify the column names in <thead> and <tfoot>.
#dataList) in <table> to attach DataTables.<!-- Add button -->
<div class="top-panel">
<a href="javascript:void(0);" class="btn btn-primary" onclick="addData()">Add New User</a>
</div>
<!-- Data list table -->
<table id="dataList" class="display" style="width:100%">
<thead>
<tr>
<th>First name</th>
<th>Last name</th>
<th>Email</th>
<th>Gender</th>
<th>Country</th>
<th>Created</th>
<th>Status</th>
<th>Action</th>
</tr>
</thead>
<tfoot>
<tr>
<th>First name</th>
<th>Last name</th>
<th>Email</th>
<th>Gender</th>
<th>Country</th>
<th>Created</th>
<th>Status</th>
<th>Action</th>
</tr>
</tfoot>
</table>
Add/Edit Modal:
Define HTML to create a modal popup for add and edit operations.
<div class="modal fade" id="userDataModal" tabindex="-1" aria-labelledby="userAddEditModalLabel" aria-hidden="true">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<h1 class="modal-title fs-5" id="userModalLabel">Add New User</h1>
<button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
</div>
<form name="userDataFrm" id="userDataFrm">
<div class="modal-body">
<div class="frm-status"></div>
<div class="mb-3">
<label for="userFirstName" class="form-label">First Name</label>
<input type="text" class="form-control" id="userFirstName" placeholder="Enter firstname">
</div>
<div class="mb-3">
<label for="userLastName" class="form-label">Last Name</label>
<input type="text" class="form-control" id="userLastName" placeholder="Enter lastname">
</div>
<div class="mb-3">
<label for="userEmail" class="form-label">Email address</label>
<input type="email" class="form-control" id="userEmail" placeholder="Enter email">
</div>
<div class="form-radio">
<label>Gender:</label>
<input type="radio" name="userGender" id="userGender_1" value="Male" checked> Male
<input type="radio" name="userGender" id="userGender_2" value="Female"> Female
</div>
<div class="mb-3">
<label for="userCountry" class="form-label">Country</label>
<input type="text" class="form-control" id="userCountry" placeholder="Enter country">
</div>
<div class="form-radio">
<label>Status:</label>
<input type="radio" name="userStatus" id="userStatus_1" value="1" checked> Active
<input type="radio" name="userStatus" id="userStatus_2" value="0"> Inactive
</div>
</div>
<div class="modal-footer">
<input type="hidden" id="userID" value="0">
<button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
<button type="button" class="btn btn-primary" onclick="submitUserData()">Submit</button>
</div>
</form>
</div>
</div>
</div>
JavaScript: Attach DataTables Plugin to HTML Table
Initialize the DataTables API class using the DataTable() method and configure the table object.
processing option to true.serverSide option to true.fetchData.php) in the ajax option.columnDefs option to disable sorting of a specific column.orderable to false.Hold the DataTables object in a variable (table) and use the draw() method to render the table data.
// Initialize DataTables API object and configure table
var table = $('#dataList').DataTable({
"processing": true,
"serverSide": true,
"ajax": "fetchData.php",
"columnDefs": [
{ "orderable": false, "targets": 7 }
]
});
$(document).ready(function(){
// Draw the table
table.draw();
});
JavaScript: DataTables Add Request
The addData() function displays a modal popup with form fields.
function addData(){
$('.frm-status').html('');
$('#userModalLabel').html('Add New User');
$('#userGender_1').prop('checked', true);
$('#userGender_2').prop('checked', false);
$('#userStatus_1').prop('checked', true);
$('#userStatus_2').prop('checked', false);
$('#userFirstName').val('');
$('#userLastName').val('');
$('#userEmail').val('');
$('#userCountry').val('');
$('#userID').val(0);
$('#userDataModal').modal('show');
}
JavaScript: DataTables Edit Request
The editData() function displays a modal popup with prefilled form.
function editData(user_data){
$('.frm-status').html('');
$('#userModalLabel').html('Edit User #'+user_data.id);
if(user_data.gender == 'Female'){
$('#userGender_1').prop('checked', false);
$('#userGender_2').prop('checked', true);
}else{
$('#userGender_2').prop('checked', false);
$('#userGender_1').prop('checked', true);
}
if(user_data.status == 1){
$('#userStatus_2').prop('checked', false);
$('#userStatus_1').prop('checked', true);
}else{
$('#userStatus_1').prop('checked', false);
$('#userStatus_2').prop('checked', true);
}
$('#userFirstName').val(user_data.first_name);
$('#userLastName').val(user_data.last_name);
$('#userEmail').val(user_data.email);
$('#userCountry').val(user_data.country);
$('#userID').val(user_data.id);
$('#userDataModal').modal('show');
}
JavaScript: Submit Add/Edit Form Data via Ajax Request
The submitUserData() function is used to submit input data of the modal form.
eventHandler.php) via Ajax request.function submitUserData(){
$('.frm-status').html('');
let input_data_arr = [
document.getElementById('userFirstName').value,
document.getElementById('userLastName').value,
document.getElementById('userEmail').value,
document.querySelector('input[name="userGender"]:checked').value,
document.getElementById('userCountry').value,
document.querySelector('input[name="userStatus"]:checked').value,
document.getElementById('userID').value,
];
fetch("eventHandler.php", {
method: "POST",
headers: { "Content-Type": "application/json" },
body: JSON.stringify({ request_type:'addEditUser', user_data: input_data_arr}),
})
.then(response => response.json())
.then(data => {
if (data.status == 1) {
Swal.fire({
title: data.msg,
icon: 'success',
}).then((result) => {
// Redraw the table
table.draw();
$('#userDataModal').modal('hide');
$("#userDataFrm")[0].reset();
});
} else {
$('.frm-status').html('<div class="alert alert-danger" role="alert">'+data.error+'</div>');
}
})
.catch(console.error);
}
JavaScript: DataTables Delete Request
The deleteData() function is used to POST delete request to the server-side script (eventHandler.php) via Ajax.
function deleteData(user_id){
Swal.fire({
title: 'Are you sure to Delete?',
text:'You won\'t be able to revert this!',
icon: 'warning',
showCancelButton: true,
confirmButtonColor: '#3085d6',
cancelButtonColor: '#d33',
confirmButtonText: 'Yes, delete it!'
}).then((result) => {
if (result.isConfirmed) {
// Delete event
fetch("eventHandler.php", {
method: "POST",
headers: { "Content-Type": "application/json" },
body: JSON.stringify({ request_type:'deleteUser', user_id: user_id}),
})
.then(response => response.json())
.then(data => {
if (data.status == 1) {
Swal.fire({
title: data.msg,
icon: 'success',
}).then((result) => {
table.draw();
});
} else {
Swal.fire(data.error, '', 'error');
}
})
.catch(console.error);
} else {
Swal.close();
}
});
}
The fetchData.php file performs server-side processing with custom links (edit and delete).
ssp.class.php).simple() function of the SSP class helps to fetch the member’s data from the database based on the mentioned columns using PHP and MySQL.formatter parameter is used to modify the default format of the data returns for the column.editData() functionality is added to the onclick attribute of the Edit anchor. It will trigger the JavaScript method for the edit form popup.deleteData() functionality is added to the onclick attribute of the Delete anchor. It will trigger the JavaScript method for the delete confirmation dialog.<?php
// Include config file
include_once 'config.php';
// Database connection info
$dbDetails = array(
'host' => DB_HOST,
'user' => DB_USER,
'pass' => DB_PASS,
'db' => DB_NAME
);
// DB table to use
$table = 'members';
// Table's primary key
$primaryKey = 'id';
// Array of database columns which should be read and sent back to DataTables.
// The `db` parameter represents the column name in the database.
// The `dt` parameter represents the DataTables column identifier.
$columns = array(
array( 'db' => 'first_name', 'dt' => 0 ),
array( 'db' => 'last_name', 'dt' => 1 ),
array( 'db' => 'email', 'dt' => 2 ),
array( 'db' => 'gender', 'dt' => 3 ),
array( 'db' => 'country', 'dt' => 4 ),
array(
'db' => 'created',
'dt' => 5,
'formatter' => function( $d, $row ) {
return date( 'jS M Y', strtotime($d));
}
),
array(
'db' => 'status',
'dt' => 6,
'formatter' => function( $d, $row ) {
return ($d == 1)?'Active':'Inactive';
}
),
array(
'db' => 'id',
'dt' => 7,
'formatter' => function( $d, $row ) {
return '
<a href="javascript:void(0);" class="btn btn-warning" onclick="editData('.htmlspecialchars(json_encode($row), ENT_QUOTES, 'UTF-8').')">Edit</a>
<a href="javascript:void(0);" class="btn btn-danger" onclick="deleteData('.$d.')">Delete</a>
';
}
)
);
// Include SQL query processing class
require 'ssp.class.php';
// Output data as json format
echo json_encode(
SSP::simple( $_GET, $dbDetails, $table, $primaryKey, $columns )
);
The SSP class handles the database-related operations. It contains some helper functions to build SQL queries for DataTables server-side processing with custom links or buttons. You can see the code of the SSP library here.
Note that: This library and all the required files are included in the source code, you don’t need to download them separately.
The eventHandler.php script handles the add, edit, and delete operations with PHP and MySQL.
addEditUser Request:
Validate the input field’s value before data processing.
deleteUser:
Delete data from the database based on the ID.
<?php
// Include database configuration file
require_once 'dbConnect.php';
// Retrieve JSON from POST body
$jsonStr = file_get_contents('php://input');
$jsonObj = json_decode($jsonStr);
if($jsonObj->request_type == 'addEditUser'){
$user_data = $jsonObj->user_data;
$first_name = !empty($user_data[0])?$user_data[0]:'';
$last_name = !empty($user_data[1])?$user_data[1]:'';
$email = !empty($user_data[2])?$user_data[2]:'';
$gender = !empty($user_data[3])?$user_data[3]:'';
$country = !empty($user_data[4])?$user_data[4]:'';
$status = !empty($user_data[5])?$user_data[5]:0;
$id = !empty($user_data[6])?$user_data[6]:0;
$err = '';
if(empty($first_name)){
$err .= 'Please enter your First Name.<br/>';
}
if(empty($last_name)){
$err .= 'Please enter your Last Name.<br/>';
}
if(empty($email) || !filter_var($email, FILTER_VALIDATE_EMAIL)){
$err .= 'Please enter a valid Email Address.<br/>';
}
if(!empty($user_data) && empty($err)){
if(!empty($id)){
// Update user data into the database
$sqlQ = "UPDATE members SET first_name=?,last_name=?,email=?,gender=?,country=?,status=?,modified=NOW() WHERE id=?";
$stmt = $conn->prepare($sqlQ);
$stmt->bind_param("sssssii", $first_name, $last_name, $email, $gender, $country, $status, $id);
$update = $stmt->execute();
if($update){
$output = [
'status' => 1,
'msg' => 'Member updated successfully!'
];
echo json_encode($output);
}else{
echo json_encode(['error' => 'Member Update request failed!']);
}
}else{
// Insert event data into the database
$sqlQ = "INSERT INTO members (first_name,last_name,email,gender,country,status) VALUES (?,?,?,?,?,?)";
$stmt = $conn->prepare($sqlQ);
$stmt->bind_param("sssssi", $first_name, $last_name, $email, $gender, $country, $status);
$insert = $stmt->execute();
if($insert){
$output = [
'status' => 1,
'msg' => 'Member added successfully!'
];
echo json_encode($output);
}else{
echo json_encode(['error' => 'Member Add request failed!']);
}
}
}else{
echo json_encode(['error' => trim($err, '<br/>')]);
}
}elseif($jsonObj->request_type == 'deleteUser'){
$id = $jsonObj->user_id;
$sql = "DELETE FROM members WHERE id=$id";
$delete = $conn->query($sql);
if($delete){
$output = [
'status' => 1,
'msg' => 'Member deleted successfully!'
];
echo json_encode($output);
}else{
echo json_encode(['error' => 'Member Delete request failed!']);
}
}
Data Table Inline Editing using jQuery, Ajax, PHP and MySQL
DataTables is very useful to build a data list in tabular format and add advanced features to make it user-friendly. You can use this example script to integrate CRUD functionality with DataTables using PHP and MySQL. Here we have used a modal popup to integrate add/edit form UI and delete confirmation window. Since Ajax is used to handle the add, edit, and delete requests, the user can do CRUD operations on a single page without page refresh/reload.
Do you want to get implementation help, or enhance the functionality of this script? Click here to Submit Service Request
