Datatables CRUD with Ajax, PHP & MySQL

Datatables CRUD with Ajax, PHP & MySQL

Datatables + CRUD + php + jquery + ajax + bootstrap + mysql; Through this tutorial, you will learn how to implement bootstrap crud datatable from mysql database with modal form using jQuery + ajax in PHP MySQL. As well as learn Insert update delete and view data without refresh page using PHP MySQL DataTables and ajax.

Through this tutorial, You can use jQuery DataTables to list records with a view add, edit, delete records without page refresh from MySQL database using PHP + MySQL + ajax + DataTables js. And also you can download the source code of PHP MySQL dataTable crud source code.

Datatables Add Edit Delete with Ajax, PHP & MySQL

Follow the following steps to add edit delete datatables records with ajax PHP & MySQL:

  • Step 1 – Create Database And Table
  • Step 2 – Create List HTML page
  • Step 3 – Include Datatable Libraries in List Page
  • Step 4 – Create Add Edit Delete Record Ajax Function
  • Step 5 – Fetch data from Mysql DB and Display in Datatable List Page
  • Step 6 – Create Add Edit Delete Record From MySQL Database Function

Step 1 – Create Database And Table

First of all, open your PHPMyAdmin and execute the following MySQL query to create a database and table; as shown below:

CREATE TABLE `users` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
 `email` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `created_at` datetime NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Step 2 – Create List HTML page

Create index.php file, which is used to display data from MySQL database using datatable js; so add the following code into your index.php file; as shown below:

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<title>Live Datatables CRUD with Ajax, PHP & MySQL - Tutsmake.com</title>
<!-- DataTables CSS library -->
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.11.3/css/jquery.dataTables.min.css"/>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js" integrity="sha384-JZR6Spejh4U02d8jOt6vLEHfe/JQGiRRSQQxSfFWpi1MquVdAyjUar5+76PVCmYl" crossorigin="anonymous"></script>
<!-- DataTables JS library -->
<script type="text/javascript" src="https://cdn.datatables.net/1.11.3/js/jquery.dataTables.min.js"></script>
<style type="text/css">
.bs-example{
margin: 20px;
}
</style>
</head>
<body>
<div class="bs-example">
<div class="container">
<div class="row">
<div class="col-md-12">
<div class="page-header clearfix">
<h2 class="float-left">Users List</h2>
<a href="javascript:void(0)" class="btn btn-primary float-right add-model"> Add User </a>
</div>
<table id="usersListTable" class="display" style="width:100%">
<thead>
<tr>
<th>Name</th>
<th>Email</th>
<th>Created</th>
<th>Action</th>
</tr>
</thead>
<tfoot>
<tr>
<th>Name</th>
<th>Email</th>
<th>Created</th>
<th>Action</th>
</tr>
</tfoot>
</table>
</div>
</div>        
</div>
</div>
</body>
<div class="modal fade" id="edit-modal" aria-hidden="true">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<h4 class="modal-title" id="userCrudModal"></h4>
</div>
<div class="modal-body">
<form id="update-form" name="update-form" class="form-horizontal">
<input type="hidden" name="id" id="id">
<input type="hidden" class="form-control" id="mode" name="mode" value="update">
<div class="form-group">
<label for="name" class="col-sm-2 control-label">Name</label>
<div class="col-sm-12">
<input type="text" class="form-control" id="name" name="name" placeholder="Enter Name" value="" maxlength="50" required="">
</div>
</div>
<div class="form-group">
<label class="col-sm-2 control-label">Email</label>
<div class="col-sm-12">
<input type="email" class="form-control" id="email" name="email" placeholder="Enter Email" value="" required="">
</div>
</div>
<div class="col-sm-offset-2 col-sm-10">
<button type="submit" class="btn btn-primary" id="btn-save" value="create">Save changes
</button>
</div>
</form>
</div>
<div class="modal-footer">
</div>
</div>
</div>
</div>
<div class="modal fade" id="add-modal" aria-hidden="true">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<h4 class="modal-title" id="userCrudModal"></h4>
</div>
<div class="modal-body">
<form id="add-form" name="add-form" class="form-horizontal">
<input type="hidden" class="form-control" id="mode" name="mode" value="add">
<div class="form-group">
<label for="name" class="col-sm-2 control-label">Name</label>
<div class="col-sm-12">
<input type="text" class="form-control" id="name" name="name" placeholder="Enter Name" value="" maxlength="50" required="">
</div>
</div>
<div class="form-group">
<label class="col-sm-2 control-label">Email</label>
<div class="col-sm-12">
<input type="email" class="form-control" id="email" name="email" placeholder="Enter Email" value="" required="">
</div>
</div>
<div class="col-sm-offset-2 col-sm-10">
<button type="submit" class="btn btn-primary" id="btn-save" value="create">Save changes
</button>
</div>
</form>
</div>
<div class="modal-footer">
</div>
</div>
</div>
</div>
<script>
$(document).ready(function(){
$('#usersListTable').DataTable({
"processing": true,
"serverSide": true,
"order": [],
"ajax": "fetch.php"
});
});
/*  add user model */
$('.add-model').click(function () {
$('#add-modal').modal('show');
});
// add form submit
$('#add-form').submit(function(e){
e.preventDefault();
// ajax
$.ajax({
url:"add-edit-delete.php",
type: "POST",
data: $(this).serialize(), // get all form field value in serialize form
success: function(){
var oTable = $('#usersListTable').dataTable(); 
oTable.fnDraw(false);
$('#add-modal').modal('hide');
$('#add-form').trigger("reset");
}
});
});  
/* edit user function */
$('body').on('click', '.btn-edit', function () {
var id = $(this).data('id');
$.ajax({
url:"add-edit-delete.php",
type: "POST",
data: {
id: id,
mode: 'edit' 
},
dataType : 'json',
success: function(result){
$('#id').val(result.id);
$('#name').val(result.name);
$('#email').val(result.email);
$('#edit-modal').modal('show');
}
});
});
// add form submit
$('#update-form').submit(function(e){
e.preventDefault();
// ajax
$.ajax({
url:"add-edit-delete.php",
type: "POST",
data: $(this).serialize(), // get all form field value in serialize form
success: function(){
var oTable = $('#usersListTable').dataTable(); 
oTable.fnDraw(false);
$('#edit-modal').modal('hide');
$('#update-form').trigger("reset");
}
});
});  
/* DELETE FUNCTION */
$('body').on('click', '.btn-delete', function () {
var id = $(this).data('id');
if (confirm("Are You sure want to delete !")) {
$.ajax({
url:"add-edit-delete.php",
type: "POST",
data: {
id: id,
mode: 'delete' 
},
dataType : 'json',
success: function(result){
var oTable = $('#usersListTable').dataTable(); 
oTable.fnDraw(false);
}
});
} 
return false;
});
</script>
</html>

Step 3 – Include Datatable Libraries

Include jQuery dataTable js libraries and bootstrap libraries into your index.php file; as shown below:

<!-- DataTables CSS library -->
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.11.3/css/jquery.dataTables.min.css"/>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js" integrity="sha384-JZR6Spejh4U02d8jOt6vLEHfe/JQGiRRSQQxSfFWpi1MquVdAyjUar5+76PVCmYl" crossorigin="anonymous"></script>
<!-- DataTables JS library -->
<script type="text/javascript" src="https://cdn.datatables.net/1.11.3/js/jquery.dataTables.min.js"></script>

Step 4 – Create Add Edit Delete Record Ajax Function

Create jQuery ajax functions to insert, update, delete data from MySQL database; as shown below:

$(document).ready(function(){
$('#usersListTable').DataTable({
"processing": true,
"serverSide": true,
"order": [],
"ajax": "fetch.php"
});
});
/*  add user model */
$('.add-model').click(function () {
$('#add-modal').modal('show');
});
// add form submit
$('#add-form').submit(function(e){
e.preventDefault();
// ajax
$.ajax({
url:"add-edit-delete.php",
type: "POST",
data: $(this).serialize(), // get all form field value in serialize form
success: function(){
var oTable = $('#usersListTable').dataTable(); 
oTable.fnDraw(false);
$('#add-modal').modal('hide');
$('#add-form').trigger("reset");
}
});
});  
/* edit user function */
$('body').on('click', '.btn-edit', function () {
var id = $(this).data('id');
$.ajax({
url:"add-edit-delete.php",
type: "POST",
data: {
id: id,
mode: 'edit' 
},
dataType : 'json',
success: function(result){
$('#id').val(result.id);
$('#name').val(result.name);
$('#email').val(result.email);
$('#edit-modal').modal('show');
}
});
});
// add form submit
$('#update-form').submit(function(e){
e.preventDefault();
// ajax
$.ajax({
url:"add-edit-delete.php",
type: "POST",
data: $(this).serialize(), // get all form field value in serialize form
success: function(){
var oTable = $('#usersListTable').dataTable(); 
oTable.fnDraw(false);
$('#edit-modal').modal('hide');
$('#update-form').trigger("reset");
}
});
});  
/* DELETE FUNCTION */
$('body').on('click', '.btn-delete', function () {
var id = $(this).data('id');
if (confirm("Are You sure want to delete !")) {
$.ajax({
url:"add-edit-delete.php",
type: "POST",
data: {
id: id,
mode: 'delete' 
},
dataType : 'json',
success: function(result){
var oTable = $('#usersListTable').dataTable(); 
oTable.fnDraw(false);
}
});
} 
return false;
});

Step 5 – Fetch data from Mysql DB and Display in Datatable List Page

Create fetch.php file; which is used to fetch data from MySQL database using ajax and display it into your index.php file; as shown below:

<?php 
// Database connection info 
$dbDetails = array( 
'host' => 'localhost', 
'user' => 'root', 
'pass' => '', 
'db'   => 'demos' 
); 
// mysql db table to use 
$table = 'users'; 
// 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' => 'name', 'dt' => 0 ), 
array( 'db' => 'email',  'dt' => 1 ), 
array( 
'db'        => 'created_at', 
'dt'        => 2, 
'formatter' => function( $d, $row ) { 
return date( 'jS M Y', strtotime($row['created_at'])); 
} 
), 
array( 
'db'        => 'id',
'dt'        => 3, 
'formatter' => function( $d, $row ) { 
return '<a href="javascript:void(0)" class="btn btn-primary btn-edit" data-id="'.$row['id'].'"> Edit </a> <a href="javascript:void(0)" class="btn btn-danger btn-delete ml-2" data-id="'.$row['id'].'"> 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 ));

Note that;- The SSP class handles the database related operations. It contains some helper functions to build SQL queries for DataTables server-side processing. You can download from this url :- https://github.com/tutsmake/Datatables-Server-side-Processing/blob/main/ssp.class.php.

Step 6 – Create Add Edit Delete Record From MySQL Database Function

Create a new file name add-edit-delete.php and inside this file create PHP functions with MySQL query to insert, update, delete data from MySQL database; as shown below:

<?php
// Database connection info 
$host='localhost';
$username='root';
$password='';
$dbname = "demos";
$conn=mysqli_connect($host,$username,$password,"$dbname");
if ($_POST['mode'] === 'add') {
$name = $_POST['name'];
$email = $_POST['email'];
mysqli_query($conn, "INSERT INTO users (name,email)
VALUES ('$name','$email')");
echo json_encode(true);
}  
if ($_POST['mode'] === 'edit') {
$result = mysqli_query($conn,"SELECT * FROM users WHERE id='" . $_POST['id'] . "'");
$row= mysqli_fetch_array($result);
echo json_encode($row);
}   
if ($_POST['mode'] === 'update') {
mysqli_query($conn,"UPDATE users set  name='" . $_POST['name'] . "', email='" . $_POST['email'] . "' WHERE id='" . $_POST['id'] . "'");
echo json_encode(true);
}  
if ($_POST['mode'] === 'delete') {
mysqli_query($conn, "DELETE FROM users WHERE id='" . $_POST["id"] . "'");
echo json_encode(true);
}  
?>

Conclusion

Datatables + php + jquery + ajax + bootstrap + mysql; Through this tutorial, you will learned how to create simple bootstrap crud (create, read, update and delete) datatable for database with modal form using jQuery + ajax in PHP MySQL.

Recommended PHP Tutorials

If you have any questions or thoughts to share, use the comment form below to reach us.

AuthorAdmin

My name is Devendra Dode. I am a full-stack developer, entrepreneur, and owner of Tutsmake.com. I like writing tutorials and tips that can help other developers. I share tutorials of PHP, Python, Javascript, JQuery, Laravel, Livewire, Codeigniter, Node JS, Express JS, Vue JS, Angular JS, React Js, MySQL, MongoDB, REST APIs, Windows, Xampp, Linux, Ubuntu, Amazon AWS, Composer, SEO, WordPress, SSL and Bootstrap from a starting stage. As well as demo example.

Leave a Reply

Your email address will not be published.