We are going to implement complete demo of datatable CRUD. We use datatable library with call of ajax and return data from MySQL. all operation we use bootstrap model without reload table data.
So let’s begin with create database and connection.
Create MySQL table
CREATE TABLE `crud` (
`firstname` varchar(30) NOT NULL,
`id` int(11) NOT NULL,
`lastname` varchar(30) NOT NULL,
`email` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `crud`
ADD PRIMARY KEY (`id`);
ALTER TABLE `crud`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT
Create config.php
<?php
$servername = "localhost";
$username = "root";
$password = "";
$db = "test";
$conn = mysqli_connect($servername, $username, $password, $db);
// Check The Connection
if (mysqli_connect_errno() ){
echo "Database Connection Failed: " . mysqli_connect_error();
die;
}Now, create index.php file in that we are demonstrate table, retrieve data, edit data and delete data for all operation.
Create index.php file
<!DOCTYPE html>
<html lang="en">
<head>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" crossorigin="anonymous">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap-theme.min.css" crossorigin="anonymous">
<link rel="stylesheet" href="https://cdn.datatables.net/1.11.4/css/jquery.dataTables.min.css" crossorigin="anonymous">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" crossorigin="anonymous"></script>
<script src="https://cdn.datatables.net/1.11.4/js/jquery.dataTables.min.js" crossorigin="anonymous"></script>
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<div class="panel panel-card ">
<!-- Start .panel -->
<div class="panel-heading">
<h4 class="panel-title"> <button id="categorie_table_add" type="button" class="btn btn-primary" data-toggle="modal" data-target="#myModal">+ Add</button></h4>
<div class="panel-actions">
<a href="#" class="panel-action panel-action-toggle" data-panel-toggle></a>
<a href="#" class="panel-action panel-action-dismiss" data-panel-dismiss></a>
</div>
</div>
<div class="panel-body">
<table id="mandatory_iban" class="table table-bordered" cellspacing="0" width="100%">
<thead>
<tr>
<th>Categorie</th>
<th>Mandatory_IBAN</th>
<th>Pays with CB</th>
<th>Action</th>
</tr>
</thead>
</table>
</div>
</div><!-- End .panel -->
</div><!--end .col-->
</div><!--end .row-->
<!-- Modal MANDATORY IBAN-->
<div class="modal fade" id="myModal" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" style="padding-top: 5%;">
<div class="modal-dialog" role="document">
<div class="modal-content">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">×</span></button>
<h4 class="modal-title" id="myModalLabel">New</h4>
</div>
<div class="modal-body">
<div class="form-group"><label>firstname</label> <input type="text" class="form-control" name="firstname"></div>
<div class="form-group"><label>lastname</label> <input type="text" class="form-control" name="lastname"></div>
<div class="form-group"><label>email</label> <input type="text" class="form-control" name="email"></div>
<input type="hidden" name="op" value="">
<input type="hidden" name="iban_id" value="">
</div>
<div class="modal-footer">
<button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
<button type="button" class="btn btn-primary" onclick="formsubmit()">Save changes</button>
</div>
</div>
</div>
</div>
</div>
</body>
</html>create one js file to include in index.php file. that will call model and all ajax functions are there. reload datatable after successfully inserted
create custom.js file
$(document).ready(function(){
fill_datatable();
function fill_datatable(id = '', firstname = '',lastname='',email=''){
var rows_selected = [];
var info = {};
info["id"] = id;
info["firstname"] = firstname;
info["lastname"] = lastname;
info["email"] = email;
$('#mandatory_iban').DataTable({
'processing': true,
'serverSide': true,
"searching" : false,
'ajax': {
'url':'./mandatory_iban.php',
'type':"GET",
'data': {info:info}
},
'order': [1, 'desc'],
'rowCallback': function(row, data, dataIndex){
// Get row ID
var rowId = data[0];
// If row ID is in the list of selected row IDs
if($.inArray(rowId, rows_selected) !== -1){
$(row).find('input[type="checkbox"]').prop('checked', true);
$(row).addClass('selected');
}
},
'columns': [
{ data: 'firstname' },
{ data: 'lastname' },
{ data: 'email' },
{
data: "id",
searchable :false,
orderable :false,
render: function ( data, type, full, row ) {
if ( type === 'display' ) {
return '<a class="edit_iban btn btn-primary" id="'+full.id+'" class="btn btn-primary a-btn-slide-text"><span class="glyphicon glyphicon-edit" aria-hidden="true"></span><span><strong>Edit</strong></span></a> <a id="delete_iban_'+full.id+'" class="btn btn-primary a-btn-slide-text"><span class="glyphicon glyphicon-remove" aria-hidden="true"></span><span><strong>Delete</strong></span></a>';
}
return data;
},
className: "dt-body-center"
}
]
});
}
});
$(document).on("click",".edit_iban", function(){
$('#myModal').modal('show');
var id=$(this).attr("id");
var edit_pop="POPUP_EDIT";
$.ajax({
type: 'post',
url: './ajax_request.php',
dataType:'json',
data: {id:id,edit_pop:edit_pop},
success: function (response) {
if(response.success==1){
$("input[name=firstname]").val(response.firstname);
$("input[name=lastname]").val(response.lastname);
$("input[name=email]").val(response.email);
$("input[name=op]").val("EDIT");
$("input[name=iban_id]").val(id);
//alert(response.success);
}
}
});
});
$(document).on("click","#categorie_table_add", function(){
$("input[name=categorie]").val('');
$("input[name=mandatory_iban]").val('');
$("input[name=play_with_cb]").val('');
$("input[name=op]").val('');
$("input[name=iban_id]").val('');
$('#myModal').modal('show');
});
$(document).on('click', '[id^="delete_iban_"]', function() {
var $button = $(this);
var id = this.id.split('_').pop();
var table = $('#mandatory_iban').DataTable(); // replace with your table id
var deleteval=1;
var table_nm='crud';
$.ajax({
type: 'post',
url: './ajax_request.php',
dataType: 'json',
data: {id: id,table_nm:table_nm,deleteval:deleteval},
success: function(callback)
{
table.row( $button.parents('tr') ).remove().draw();
},
error: function(status)
{
console.log(status);
}
});
});
function formsubmit(e) {
var firstname = $("input[name=firstname]").val();
var lastname = $("input[name=lastname]").val();
var email = $("input[name=email]").val();
var op = $("input[name=op]").val();
var iban_id = $("input[name=iban_id]").val();
if(op==''){
op="ADD";
}
if(firstname!='' && lastname!='' && email!=''){
$.ajax({
type: 'post',
url: './ajax_request.php',
dataType:'json',
data: {firstname:firstname,lastname:lastname,email:email,op:op,iban_id:iban_id},
success: function (response) {
if(response.success==1){
var table = $('#mandatory_iban').DataTable();
table.ajax.reload();
$('#myModal').modal('hide');
}
}
});
}
}
}mandatory_iban.php we call that file in ajax that retrieve data from mysql.
Create mandatory_iban.php file
<?php
## Database configuration
include 'config.php';
## Read value
$draw = $_GET['draw'];
$row = $_GET['start'];
$rowperpage = $_GET['length']; // Rows display per page
$columnIndex = $_GET['order'][0]['column']; // Column index
$columnName = $_GET['columns'][$columnIndex]['data']; // Column name
$columnSortOrder = $_GET['order'][0]['dir']; // asc or desc
$searchValue = mysqli_real_escape_string($conn,$_GET['search']['value']); // Search value
## Search
$searchQuery = " ";
if($searchValue != ''){
$searchQuery = " and (firstname like '%".$searchValue."%' or
lastname like '%".$searchValue."%' or
email like '%".$searchValue."%') ";
}
## Total number of records without filtering
$sel = mysqli_query($conn,"select count(*) as allcount from crud");
$records = mysqli_fetch_assoc($sel);
$totalRecords = $records['allcount'];
## Total number of record with filtering
$sel = mysqli_query($conn,"select count(*) as allcount from crud WHERE 1 ".$searchQuery);
$records = mysqli_fetch_assoc($sel);
$totalRecordwithFilter = $records['allcount'];
## Fetch records
$empQuery = "select * from crud WHERE 1 ".$searchQuery." order by ".$columnName." ".$columnSortOrder." limit ".$row.",".$rowperpage;
$empRecords = mysqli_query($conn, $empQuery);
$data = array();
while ($row = mysqli_fetch_assoc($empRecords)) {
$data[] = array(
"id"=>$row['id'],
"firstname"=>$row['firstname'],
"lastname"=>$row['lastname'],
"email"=>$row['email']
);
}
## Response
$response = array(
"draw" => intval($draw),
"iTotalRecords" => $totalRecords,
"iTotalDisplayRecords" => $totalRecordwithFilter,
"aaData" => $data
);
echo json_encode($response);
Create another file ajax_request.php. we perform all operation there add, edit, delete and view.
Create ajax_request.php file
<?php
## Database configuration
include 'config.php';
if(isset($_POST['op']) && $_POST['op']=='ADD'){
$imports[] = "('".iconv('ISO-8859-1', 'UTF-8', addslashes(trim($_POST['firstname'], '"')))."','".iconv('ISO-8859-1', 'UTF-8', addslashes(trim($_POST['lastname'], '"')))."','".iconv('ISO-8859-1', 'UTF-8', addslashes(trim($_POST['email'], '"')))."')";
$response=array();
if(!mysqli_query($conn,"INSERT INTO crud(firstname, lastname, email) VALUES ".implode(',', $imports))){
$response['success']=0;
}else{
$response['success']=1;
}
echo json_encode($response);
}
if(isset($_POST['op']) && $_POST['op']=='EDIT'){
$response=array();
if(!mysqli_query($conn,"UPDATE crud SET firstname = '".iconv('ISO-8859-1', 'UTF-8', addslashes(trim($_POST['firstname'], '"')))."', lastname = '".iconv('ISO-8859-1', 'UTF-8', addslashes(trim($_POST['lastname'], '"')))."', email = '".iconv('ISO-8859-1', 'UTF-8', addslashes(trim($_POST['email'], '"')))."' WHERE id='".$_POST['iban_id']."'")){
$response['success']=0;
}else{
$response['success']=1;
}
echo json_encode($response);
}
if(isset($_POST['edit_pop']) && $_POST['edit_pop']=='POPUP_EDIT'){
$response=array();
$query=mysqli_query($conn,"SELECT * FROM crud WHERE id='".$_POST['id']."'");
$num_row=mysqli_num_rows($query);
if($num_row==1){
$row=mysqli_fetch_array($query);
$response['success']=1;
$response['firstname']=$row['firstname'];
$response['lastname']=$row['lastname'];
$response['email']=$row['email'];
echo json_encode($response);
}
}
if(isset($_POST['deleteval']) && $_POST['deleteval']==1){
$response=array();
if(!mysqli_query($conn,"DELETE FROM ".$_POST['table_nm']." WHERE id = ".$_POST['id']."")){
$response['success']=0;
}else{
$response['success']=1;
}
echo json_encode($response);
}