To datatables crud operations using php, mysql, jQuery, ajax; Through this tutorial, you will learn how to implement crud operation project from MySQL database with modal form using jQuery dataTable + ajax in PHP MySQL. As well as learned to Insert update delete and view data without refreshing pages using PHP MySQL DataTables and ajax.
Through this tutorial, You can use jQuery DataTables to list records with a view add, edit, and 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.
jQuery Datatables Crud Operations using PHP, Ajax and Mysql
Use the following steps, you can create simple php ajax crud with jQuery datatables and bootstrap modals:
- 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;
Recommended:-Insert Update Delete in PHP on Same Page
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>
Recommended:-How to Fetch Data From Database in PHP using Ajax
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/neveropen/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
Recommended:-Send Reset Password Link Email PHP
Recommended:-PHP Google Places Autocomplete Example
Recommended:-PHP Dropzone File Upload Tutorial Example
Recommended:-PHP Signature Pad using Jquery Ajax Example
Recommended:-PHP 8 MySQL Ajax Live Search Autocomplete Example
Recommended:-PHP Google Firebase CRUD Example Tutorial
Recommended:-Replace First and Last Character From String PHP
Recommended:-How to Install PHP 8 on Ubuntu 20.04 using Terminal
Recommended:-PHP Sending Email (text/html)|PHP Mail() Function
Recommended:-Import CSV File into MySQL using PHP
Recommended:-Upload File to AWS S3 Bucket In PHP
Recommended:-Login with Facebook using PHP
Recommended:-PHP MySQL jQuery Ajax Pagination
If you have any questions or thoughts to share, use the comment form below to reach us.
