Vanilla DataTables is a JavaScript library which is write in pure Vanilla JavaScript and it used for load dynamic MySql table data in a very interactive and user friendly way. This Vanilla DataTables have very rich features which you allows to perform live searching, sorting and server-side processing of data.
So if you are looking for implementing Vanilla DataTables in your web application, then you have first learn the basic How can we perform CRUD (Create, Read, Update, Delete) operation with this Vanilla DataTables. If you want to learn Basic CRUD opeation with Vanilla DataTables, then you have come on the right place. Because in previous tutorial we have already show you how to load dynamic MySql table data under this Vanilla DataTables with server-side processing of Data using PHP script. Now in this tutorial, we will show you how to implement Live CRUD operation with Vanilla DataTables by using JavaScript fetch API, Ajax PHP and Mysql Database.
In this tutorial, we will cover how to perform CRUD operation with Vanilla DataTables step by step. So here we will create Vanilla DataTables single page CRUD application with Mysql table dynamic Data.
So now lets create how to make Live Vanilla DataTables CRUD Application with JavaScript, Ajax, PHP and Mysql. Below you can find the file structure of Vanilla DataTables CRUD Application.
- action.php
- fetch.php
- function.php
- index.php
Step 1: Create MySQL Database Table
For Perform CRUD Operation with Vanilla DataTables, so first we need to create Mysql database table, so we have to run following SQL script for create customer_table for CRUD operation.
--
-- Database: `testing`
--
-- --------------------------------------------------------
--
-- Table structure for table `customer_table`
--
CREATE TABLE `customer_table` (
`customer_id` int(11) NOT NULL,
`customer_first_name` varchar(200) NOT NULL,
`customer_last_name` varchar(200) NOT NULL,
`customer_email` varchar(300) NOT NULL,
`customer_gender` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Indexes for dumped tables
--
--
-- Indexes for table `customer_table`
--
ALTER TABLE `customer_table`
ADD PRIMARY KEY (`customer_id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `customer_table`
--
ALTER TABLE `customer_table`
MODIFY `customer_id` int(11) NOT NULL AUTO_INCREMENT;
Step 2: Include Bootstrap Stylesheet, JSTable Library
In this tutorial we have use pure JavaScript, so here we will only use Bootstrap stylesheet library and for Vanilla DataTables here we have use JSTable library which is written in pure JavaScript. This files, we have to include in index.php file.
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.2/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-EVSTQN3/azprG1Anm3QDgpJLIm9Nao0Yz1ztcQTwFspd3yD65VohhpuuCOmLASjC" crossorigin="anonymous">
<link rel="stylesheet" type="text/css" href="library/jstable.css" />
<script src="library/jstable.min.js" type="text/javascript"></script>
Step 3: Load Data in Vanilla DataTable with Server-side Processing
In CRUD Application first stage we need to load dynamic data in Vanilla DataTables with Server side processing of Data. So first we have to create one html table in index.php file.
<span id="success_message"></span>
<div class="card">
<div class="card-header">
<div class="row">
<div class="col col-md-6">Customer Data</div>
<div class="col col-md-6" align="right">
<button type="button" name="add_data" id="add_data" class="btn btn-success btn-sm">Add</button>
</div>
</div>
</div>
<div class="card-body">
<div class="table-responsive">
<table id="customer_table" class="table table-bordered table-striped">
<thead>
<tr>
<th>First Name</th>
<th>Last Name</th>
<th>Email</th>
<th>Gender</th>
</tr>
</thead>
<tbody>
<?php echo fetch_top_five_data($connect); ?>
</tbody>
</table>
</div>
</div>
</div>
After this, we have load data under this DataTables when page has been load, So we have to write php script for last five data and count total number of data under this mysql table. So we have to open function.php and write PHP script under this file.
<?php
//function.php
$connect = new PDO("mysql:host=localhost;dbname=testing", "root", "");
function fetch_top_five_data($connect)
{
$query = "
SELECT * FROM customer_table
ORDER BY customer_id DESC
LIMIT 5";
$result = $connect->query($query);
$output = '';
foreach($result as $row)
{
$output .= '
<tr>
<td>'.$row["customer_first_name"].'</td>
<td>'.$row["customer_last_name"].'</td>
<td>'.$row["customer_email"].'</td>
<td>'.$row["customer_gender"].'</td>
</tr>
';
}
return $output;
}
function count_all_data($connect)
{
$query = "SELECT * FROM customer_table";
$statement = $connect->prepare($query);
$statement->execute();
return $statement->rowCount();
}
?>
After this we have to called PHP fetch_top_five_data($connect) function at index.php file, so when page load then it will display last five data in Vanilla DataTable.
Next we have go to index.php file and we need to initialize Vanilla DataTables. So for this, we have to write following script in JavaScript section.
var table = new JSTable("#customer_table", {
serverSide : true,
deferLoading : <?php echo count_all_data($connect); ?>,
ajax : "fetch.php"
});
This script will send Ajax request to fetch.php file and by this script we can perform server-side processing of data and perform searching, sorting and pagination operation at server-side. Below you can find fetch.php source in which you can learn how to write php script for perform server-side processing of data.
<?php
//fetch.php
include('function.php');
$startGET = filter_input(INPUT_GET, "start", FILTER_SANITIZE_NUMBER_INT);
$start = $startGET ? intval($startGET) : 0;
$lengthGET = filter_input(INPUT_GET, "length", FILTER_SANITIZE_NUMBER_INT);
$length = $lengthGET ? intval($lengthGET) : 10;
$searchQuery = filter_input(INPUT_GET, "searchQuery", FILTER_SANITIZE_STRING);
$search = empty($searchQuery) || $searchQuery === "null" ? "" : $searchQuery;
$sortColumnIndex = filter_input(INPUT_GET, "sortColumn", FILTER_SANITIZE_NUMBER_INT);
$sortDirection = filter_input(INPUT_GET, "sortDirection", FILTER_SANITIZE_STRING);
$column = array("customer_first_name", "customer_last_name", "customer_email", "customer_gender");
$query = "SELECT * FROM customer_table ";
$query .= '
WHERE customer_id LIKE "%'.$search.'%"
OR customer_first_name LIKE "%'.$search.'%"
OR customer_last_name LIKE "%'.$search.'%"
OR customer_email LIKE "%'.$search.'%"
OR customer_gender LIKE "%'.$search.'%"
';
if($sortColumnIndex != '')
{
$query .= 'ORDER BY '.$column[$sortColumnIndex].' '.$sortDirection.' ';
}
else
{
$query .= 'ORDER BY customer_id DESC ';
}
$query1 = '';
if($length != -1)
{
$query1 = 'LIMIT ' . $start . ', ' . $length;
}
$statement = $connect->prepare($query);
$statement->execute();
$number_filter_row = $statement->rowCount();
$result = $connect->query($query . $query1);
$data = array();
foreach($result as $row)
{
$sub_array = array();
$sub_array[] = $row['customer_first_name'];
$sub_array[] = $row['customer_last_name'];
$sub_array[] = $row['customer_email'];
$sub_array[] = $row['customer_gender'];
$data[] = $sub_array;
}
$output = array(
"recordsTotal" => count_all_data($connect),
"recordsFiltered" => $number_filter_row,
"data" => $data
);
echo json_encode($output);
?>
Step 4: Add or Insert Data into Mysql Table using Bootstrap Modal
Under this Vanilla DataTables CRUD Application, we will use Bootstrap Modal for process data for Add or Insert Data into Mysql table using JavaScript Fetch API with PHP Script. So first we need to create Bootstrap Modal in index.php file.
<div class="modal" id="customer_modal" tabindex="-1">
<form method="post" id="customer_form">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<h5 class="modal-title" id="modal_title">Add Customer</h5>
<button type="button" class="btn-close" id="close_modal" data-bs-dismiss="modal" aria-label="Close"></button>
</div>
<div class="modal-body">
<div class="mb-3">
<label class="form-label">First Name</label>
<input type="text" name="first_name" id="first_name" class="form-control" />
<span class="text-danger" id="first_name_error"></span>
</div>
<div class="mb-3">
<label class="form-label">Last Name</label>
<input type="text" name="last_name" id="last_name" class="form-control" />
<span class="text-danger" id="last_name_error"></span>
</div>
<div class="mb-3">
<label class="form-label">Email</label>
<input type="text" name="customer_email" id="customer_email" class="form-control" />
<span class="text-danger" id="customer_email_error"></span>
</div>
<div class="mb-3">
<label class="form-label">Gender</label>
<select name="customer_gender" id="customer_gender" class="form-control">
<option value="Male">Male</option>
<option value="Female">Female</option>
</select>
</div>
</div>
<div class="modal-footer">
<input type="hidden" name="action" id="action" value="Add" />
<button type="button" class="btn btn-primary" id="action_button">Add</button>
</div>
</div>
</div>
</form>
</div>
<div class="modal-backdrop fade show" id="modal_backdrop" style="display:none;"></div>
Under this tutorial, we will pop up and close Bootstrap Modal by using JavaScript without using jQuery library and after pop up Bootstrap Modal we need to send form data from Modal to PHP Script, so for this we will use JavaScript Fetch API, so this code you can find below which has been write in index.php file.
function _(element)
{
return document.getElementById(element);
}
function open_modal()
{
_('modal_backdrop').style.display = 'block';
_('customer_modal').style.display = 'block';
_('customer_modal').classList.add('show');
}
function close_modal()
{
_('modal_backdrop').style.display = 'none';
_('customer_modal').style.display = 'none';
_('customer_modal').classList.remove('show');
}
_('add_data').onclick = function(){
open_modal();
}
_('close_modal').onclick = function(){
close_modal();
}
_('action_button').onclick = function(){
var form_data = new FormData(_('customer_form'));
_('action_button').disabled = true;
fetch('action.php', {
method:"POST",
body:form_data
}).then(function(response){
return response.json();
}).then(function(responseData){
_('action_button').disabled = false;
if(responseData.success)
{
_('success_message').innerHTML = responseData.success;
close_modal();
table.update();
}
else
{
if(responseData.first_name_error)
{
_('first_name_error').innerHTML = responseData.first_name_error;
}
else
{
_('first_name_error').innerHTML = '';
}
if(responseData.last_name_error)
{
_('last_name_error').innerHTML = responseData.last_name_error;
}
else
{
_('last_name_error').innerHTML = '';
}
if(responseData.customer_email_error)
{
_('customer_email_error').innerHTML = responseData.customer_email_error;
}
else
{
_('customer_email_error').innerHTML = '';
}
}
});
}
Now at server side data will be received at action.php file. This file will first check validation error, if there is validation error occures then it will send validation error to index.php which will display under Bootstrap Modal and suppose data are proper then it will Insert or Add into Mysql table using PHP script.
<?php
//action.php
include('function.php');
if(isset($_POST["action"]))
{
if($_POST["action"] == 'Add' || $_POST["action"] == 'Update')
{
$output = array();
$first_name = $_POST["first_name"];
$last_name = $_POST["last_name"];
$customer_email = $_POST["customer_email"];
$customer_gender = $_POST["customer_gender"];
if(empty($first_name))
{
$output['first_name_error'] = 'First Name is Required';
}
if(empty($last_name))
{
$output['last_name_error'] = 'Last Name is Required';
}
if(empty($customer_email))
{
$output['customer_email_error'] = 'Email is Required';
}
else
{
if(!filter_var($customer_email, FILTER_VALIDATE_EMAIL))
{
$output['customer_email_error'] = 'Invalid Email Format';
}
}
if(count($output) > 0)
{
echo json_encode($output);
}
else
{
if($_POST['action'] == 'Add')
{
$data = array(
':customer_first_name' => $first_name,
':customer_last_name' => $last_name,
':customer_email' => $customer_email,
':customer_gender' => $customer_gender
);
$query = "
INSERT INTO customer_table
(customer_first_name, customer_last_name, customer_email, customer_gender)
VALUES (:customer_first_name, :customer_last_name, :customer_email, :customer_gender)
";
$statement = $connect->prepare($query);
if($statement->execute($data))
{
$output['success'] = '<div class="alert alert-success">New Data Added</div>';
echo json_encode($output);
}
}
}
}
}
?>
Step 5: Edit or Update Mysql Data with Bootstrap Modal
Under this Vanilla DataTables CRUD application, we have come one Update Data operation. So for update data operation, we need to fetch singe customer data from database and load in Bootstrap modal, so we can change data in Bootstrap modal and again submit to form for update data operation. So first we need to send Ajax request to for fetch single customer data by using Fetch API, which source code you can find below.
index.php
function fetch_data(id)
{
var form_data = new FormData();
form_data.append('id', id);
form_data.append('action', 'fetch');
fetch('action.php', {
method:"POST",
body:form_data
}).then(function(response){
return response.json();
}).then(function(responseData){
_('first_name').value = responseData.first_name;
_('last_name').value = responseData.last_name;
_('customer_email').value = responseData.customer_email;
_('customer_gender').value = responseData.customer_gender;
_('customer_id').value = id;
_('action').value = 'Update';
_('modal_title').innerHTML = 'Edit Data';
_('action_button').innerHTML = 'Edit';
open_modal();
});
}
When this above Ajax request will be send then at action.php file, it will fetch single customer data from Mysql database and send back data to ajax request in JSON format.
action.php
if($_POST['action'] == 'fetch')
{
$query = "
SELECT * FROM customer_table
WHERE customer_id = '".$_POST["id"]."'
";
$result = $connect->query($query);
$data = array();
foreach($result as $row)
{
$data['first_name'] = $row['customer_first_name'];
$data['last_name'] = $row['customer_last_name'];
$data['customer_email'] = $row['customer_email'];
$data['customer_gender'] = $row['customer_gender'];
}
echo json_encode($data);
}
When above script data to Ajax request, then Bootstrap modal will be pop up with filled customer data and we can edit customer details and again send back data to action.php file, which you can seen below.
action.php
if($_POST["action"] == 'Add' || $_POST["action"] == 'Update')
{
$output = array();
$first_name = $_POST["first_name"];
$last_name = $_POST["last_name"];
$customer_email = $_POST["customer_email"];
$customer_gender = $_POST["customer_gender"];
if(empty($first_name))
{
$output['first_name_error'] = 'First Name is Required';
}
if(empty($last_name))
{
$output['last_name_error'] = 'Last Name is Required';
}
if(empty($customer_email))
{
$output['customer_email_error'] = 'Email is Required';
}
else
{
if(!filter_var($customer_email, FILTER_VALIDATE_EMAIL))
{
$output['customer_email_error'] = 'Invalid Email Format';
}
}
if(count($output) > 0)
{
echo json_encode($output);
}
else
{
$data = array(
':customer_first_name' => $first_name,
':customer_last_name' => $last_name,
':customer_email' => $customer_email,
':customer_gender' => $customer_gender
);
if($_POST['action'] == 'Add')
{
$query = "
INSERT INTO customer_table
(customer_first_name, customer_last_name, customer_email, customer_gender)
VALUES (:customer_first_name, :customer_last_name, :customer_email, :customer_gender)
";
$statement = $connect->prepare($query);
if($statement->execute($data))
{
$output['success'] = '<div class="alert alert-success">New Data Added</div>';
echo json_encode($output);
}
}
if($_POST['action'] == 'Update')
{
$query = "
UPDATE customer_table
SET customer_first_name = :customer_first_name,
customer_last_name = :customer_last_name,
customer_email = :customer_email,
customer_gender = :customer_gender
WHERE customer_id = '".$_POST["customer_id"]."'
";
$statement = $connect->prepare($query);
if($statement->execute($data))
{
$output['success'] = '<div class="alert alert-success">Data Updated</div>';
}
echo json_encode($output);
}
}
}
Step 6: Delete or Remove Data From Mysql with Fetch API
After performing update data operation with Bootstrap Modal, now we need to remove unwanted data from MySQL table, so in this step we will show you how to delete or remove data fromm MySQL table by using JavaScript Fetch API with PHP script and display remaining data in Vanilla JavaScript DataTables.
For Delete data from Database, first we need to create delete button in each row of table, so we have make required changes in function.php file and fetch.php file. Under this file code, so it will add make delete button in row of data in Vanilla DataTables.
function.php
function fetch_top_five_data($connect)
{
$query = "
SELECT * FROM customer_table
ORDER BY customer_id DESC
LIMIT 5";
$result = $connect->query($query);
$output = '';
foreach($result as $row)
{
$output .= '
<tr>
<td>'.$row["customer_first_name"].'</td>
<td>'.$row["customer_last_name"].'</td>
<td>'.$row["customer_email"].'</td>
<td>'.$row["customer_gender"].'</td>
<td><button type="button" onclick="fetch_data('.$row["customer_id"].')" class="btn btn-warning btn-sm">Edit</button> <button type="button" class="btn btn-danger btn-sm" onclick="delete_data('.$row["customer_id"].')">Delete</button></td>
</tr>
';
}
return $output;
}
fetch.php
<?php
//fetch.php
include('function.php');
$startGET = filter_input(INPUT_GET, "start", FILTER_SANITIZE_NUMBER_INT);
$start = $startGET ? intval($startGET) : 0;
$lengthGET = filter_input(INPUT_GET, "length", FILTER_SANITIZE_NUMBER_INT);
$length = $lengthGET ? intval($lengthGET) : 10;
$searchQuery = filter_input(INPUT_GET, "searchQuery", FILTER_SANITIZE_STRING);
$search = empty($searchQuery) || $searchQuery === "null" ? "" : $searchQuery;
$sortColumnIndex = filter_input(INPUT_GET, "sortColumn", FILTER_SANITIZE_NUMBER_INT);
$sortDirection = filter_input(INPUT_GET, "sortDirection", FILTER_SANITIZE_STRING);
$column = array("customer_first_name", "customer_last_name", "customer_email", "customer_gender");
$query = "SELECT * FROM customer_table ";
$query .= '
WHERE customer_id LIKE "%'.$search.'%"
OR customer_first_name LIKE "%'.$search.'%"
OR customer_last_name LIKE "%'.$search.'%"
OR customer_email LIKE "%'.$search.'%"
OR customer_gender LIKE "%'.$search.'%"
';
if($sortColumnIndex != '')
{
$query .= 'ORDER BY '.$column[$sortColumnIndex].' '.$sortDirection.' ';
}
else
{
$query .= 'ORDER BY customer_id DESC ';
}
$query1 = '';
if($length != -1)
{
$query1 = 'LIMIT ' . $start . ', ' . $length;
}
$statement = $connect->prepare($query);
$statement->execute();
$number_filter_row = $statement->rowCount();
$result = $connect->query($query . $query1);
$data = array();
foreach($result as $row)
{
$sub_array = array();
$sub_array[] = $row['customer_first_name'];
$sub_array[] = $row['customer_last_name'];
$sub_array[] = $row['customer_email'];
$sub_array[] = $row['customer_gender'];
$sub_array[] = '<button type="button" onclick="fetch_data('.$row["customer_id"].')" class="btn btn-warning btn-sm">Edit</button> <button type="button" class="btn btn-danger btn-sm" onclick="delete_data('.$row["customer_id"].')">Delete</button>';
$data[] = $sub_array;
}
$output = array(
"recordsTotal" => count_all_data($connect),
"recordsFiltered" => $number_filter_row,
"data" => $data
);
echo json_encode($output);
?>
So when user click on delete button then it will called delete_data(id) javascript function which will send ajax request to PHP script using JavaScript Fetch API, this function we have to make in index.php file.
index.php
function delete_data(id)
{
if(confirm("Are you sure you want to remove it?"))
{
var form_data = new FormData();
form_data.append('id', id);
form_data.append('action', 'delete');
fetch('action.php', {
method:"POST",
body:form_data
}).then(function(response){
return response.json();
}).then(function(responseData){
_('success_message').innerHTML = responseData.success;
table.update();
});
}
}
Above delete_data() function has send ajax request to action.php file for delete data operation. Below you can find the PHP script for delete data from Mysql table.
action.php
if($_POST['action'] == 'delete')
{
$query = "
DELETE FROM customer_table
WHERE customer_id = '".$_POST["id"]."'
";
if($connect->query($query))
{
$output['success'] = '<div class="alert alert-success">Data Deleted</div>';
echo json_encode($output);
}
}
So here our Vanilla DataTables CRUD Application is ready and under this Application we can load data in Vanilla DataTables, Insert or Add data with Bootstrap Modal, Update or Edit data with Bootstrap modal and lastly we can also perform delete data operation also. So here we have make Single Page Vanilla JavaScript CRUD Application with Fetch API and PHP script.
If you need complete source in single, so you can get by click on below button, once you have click on this button, then one dialog box will appear and it will ask you email address, so enter your valid email address, and once you have enter email address, then it will send source code file at your email address within 5 minutes. If you have not receive email in your inbox, please kindly check your SPAM folder also. Because our most of email goes in SPAM folder.
