Skip to content

Instantly share code, notes, and snippets.

@aziz-blr

aziz-blr/JQuery Datatable Server Side Processing

Last active August 25, 2020 05:20
Show Gist options
  • Save aziz-blr/f3f645ed1451515597e52ede4be51539 to your computer and use it in GitHub Desktop.
Save aziz-blr/f3f645ed1451515597e52ede4be51539 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
JQuery Datatable Server Side Processing
<!-- Custom page script -->
<script>
var selected = [];
$(function () {
$('#emailTemplatesDataTable').DataTable({
"paging": true,
"lengthChange": true,
"searching": true,
"ordering": true,
"info": true,
"autoWidth": false,
order: [[0, 'DESC']],
columnDefs: [
{ orderable: false, targets: 0 },
{ orderable: false, targets: 1 },
{ orderable: false, targets: 2 },
{ orderable: false, targets: 3 },
{ "width": "5%", "targets": 2 },
{ "width": "10%", "targets": 3 },
{ "className": "dt-center", "targets": 2 },
{ "className": "dt-center", "targets": 3 }
],
"drawCallback": function (settings) {
$("#emailTemplatesDataTable").wrap("<div class='table-responsive'></div>");
$('[data-toggle=confirmation]').confirmation({
rootSelector: '[data-toggle=confirmation]'
});
},
"fnRowCallback": function( nRow, aData) {
$(nRow).attr("rowid", aData[4]);
return nRow;
},
"bProcessing": true,
"serverSide": true,
"language": {
"processing": '<img src="<?php echo BACKEND_URL; ?>/images/lazy-loader.gif">'
},
"ajax":{
url :"ajax/response_email_template.php",
type: "post"
}
});
$('#emailTemplatesDataTable tbody').on('click', 'tr', function () {
var id = $(this).attr('rowid');
var index = $.inArray(id, selected);
if ( index === -1 ) {
selected.push( id );
} else {
selected.splice( index, 1 );
}
console.log(selected);
$(this).toggleClass('selected');
} );
});
</script>
<!-- CHANGE STATUS OF RECORD USING AJAX -->
<script>
$(document).on('click','.ChangeStatus',function(e){
var id = $(this).attr('id');
var status = $(this).attr('status');
var Data = 'action_type=changestatus&table=email_templates&id='+id+'&status='+status;
$.ajax({
type: 'POST',
url: 'ajax/masterChangeStatus.php',
data: Data,
success:function(){
var table = $('#emailTemplatesDataTable').DataTable();
table.ajax.reload();
}
});
});
</script>
<!-- DELETE ONE RECORD USING AJAX -->
<script>
$(document).on('click','.delete',function(e){
var id = $(this).attr('id');
var Data = 'action_type=delete&table=email_templates&id='+id;
$.ajax({
type: 'POST',
url: 'ajax/masterChangeStatus.php',
data: Data,
success:function(){
var table = $('#emailTemplatesDataTable').DataTable();
table.ajax.reload();
}
});
});
</script>
<!--MULTIPLE DELETE RECORD USING AJAX -->
<script>
$(document).on('click','#delete',function(){
var rowIds = selected;
var Data = 'action_type=multipledelete&table=email_templates&rowIds='+rowIds;
$.ajax({
type: 'POST',
url: 'ajax/masterChangeStatus.php',
data: Data,
success:function(){
var table = $('#emailTemplatesDataTable').DataTable();
table.ajax.reload();
}
});
});
</script>
<script>
$(document).on('click','#selectAll',function(){
var table = $('#emailTemplatesDataTable').DataTable();
table.rows().iterator( 'row', function ( context, index ) {
$( this.row( index ).node() ).addClass( 'selected' );
var id = $( this.row( index ).node() ).attr('rowid');
var index = $.inArray(id, selected);
if ( index === -1 ) {
selected.push( id );
} else {
//selected.splice( index, 1 );
}
} );
console.log(selected);
});
</script>
<script>
$(document).on('click','#deSelectAll',function(){
var table = $('#emailTemplatesDataTable').DataTable();
table.rows().iterator( 'row', function ( context, index ) {
$( this.row( index ).node() ).removeClass( 'selected' );
var id = $( this.row( index ).node() ).attr('rowid');
var index = $.inArray(id, selected);
if ( index === -1 ) {
//selected.push( id );
} else {
selected.splice( index, 1 );
}
} );
console.log(selected);
});
</script>
Raw
Scroll Loading Server Side Processing PHP File
<?php
error_reporting(E_ALL ^ E_NOTICE);
ob_start();
session_start();
include 'config.php';
// storing request (ie, get/post) global array to a variable
$requestData= $_REQUEST;
$type = $_REQUEST['type'];
$columns = array(
// datatable column index => database column name
0 => '',
1 => ''
);
// getting total number records without any search
$sql = "SELECT * FROM media WHERE type = '$type' ORDER BY created_on DESC";
$query = mysqli_query($conn, $sql) or die("response_media.php: get event media");
$totalData = mysqli_num_rows($query);
$totalFiltered = $totalData; // when there is no search parameter then total number rows = total number filtered rows.
//$sql = "SELECT * FROM media";
//if( !empty($requestData['search']['value']) ) { // if there is a search parameter, $requestData['search']['value'] contains search parameter
// $sql.=" AND ( employee_name LIKE '".$requestData['search']['value']."%' ";
// $sql.=" OR employee_salary LIKE '".$requestData['search']['value']."%' ";
// $sql.=" OR employee_age LIKE '".$requestData['search']['value']."%' )";
//}
//$query = mysqli_query($conn, $sql) or die("response_media.php: get event media");
//$totalFiltered = mysqli_num_rows($query); // when there is a search parameter then we have to modify total number filtered rows as per search result.
//$sql.= " LIMIT ".$requestData['start']." ,".$requestData['length']." ";
//$query = mysqli_query($conn, $sql) or die("response_media.php: get event media");
$data = array();
while( $row = mysqli_fetch_array($query) ) { // preparing an array
$nestedData=array();
$path_info = pathinfo($row['path']);
$ext = $path_info['extension'];
if ( $row['type'] == 'image' ) {
$nestedData[] = '<img src="timthumb.php?src='.$row['path'].'&w=60&h=40">';
}else{
$nestedData[] = '<a href="'.$row['path'].'" target="_blank"><i class="fa fa-file-text-o"></i> &nbsp;'.substr($row['name'], 0,35).'</a>';
}
$nestedData[] = '<span class="btn btn-danger btn-xs delete" id="'.$row['id'].'" path="'.$row['path'].'" data-toggle="confirmation" data-title="Do you want to delete ?" data-placement="left"><i class="fa fa-trash"></i></span>';
$data[] = $nestedData;
}
$json_data = array(
"draw" => intval( $requestData['draw'] ), // for every request/draw by clientside , they send a number as a parameter, when they recieve a response/data they first check the draw number, so we are sending same number in draw.
"recordsTotal" => intval( $totalData ), // total number of records
"recordsFiltered" => intval( $totalFiltered ), // total number of records after searching, if there is no searching then totalFiltered = totalData
"data" => $data // total data array
);
echo json_encode($json_data); // send data as json format
?>
Raw
Server Side Processing PHP File
<?php
error_reporting(E_ALL ^ E_NOTICE);
ob_start();
session_start();
include 'config.php';
// initilize all variable
$params = $columns = $totalRecords = $data = array();
$params = $_REQUEST;
//define index of column
$columns = array(
0 => 'name',
1 => 'image',
2 => 'created_on');
$sqlTot = $sqlRec = $share = "";
// check search value exist
if (!empty($params['search']['value'])) {
$where .= " WHERE ";
$where .= " AND name LIKE '" . $params['search']['value'] . "%' ";
}
// getting total number records without any search
$sql = "SELECT id,title,image,status,created_on FROM books";
$sqlTot .= $sql;
$sqlRec .= $sql;
//concatenate search sql if value exist
if (isset($where) && $where != '') {
$sqlTot .= $where;
$sqlRec .= $where;
}
$sqlRec .= " ORDER BY ". $columns[$params['order'][0]['column']]." ".$params['order'][0]['dir']." LIMIT " . $params['start'] . " ," . $params['length'] . " ";
$queryTot = mysqli_query($conn, $sqlTot) or die("database error:" . mysqli_error($conn));
$totalRecords = mysqli_num_rows($queryTot);
$queryRecords = mysqli_query($conn, $sqlRec) or die("error to fetch selling company data");
//iterate on results row and create new index array of data
while ($row = mysqli_fetch_assoc($queryRecords)) {
$nestedData = array();
$nestedData[] = $row['title'];
$nestedData[] = '<img src="timthumb.php?src='.$row['image'].'&h=100&w=80&zc=3">';
$nestedData[] = date('d-m-Y', strtotime($row['created_on']));
if ( $row['status'] == 1 ) {
$nestedData[] = '<span class="btn btn-success btn-xs ChangeStatus" id="'.$row['id'].'" status ="'.$row['status'].'" data-toggle="confirmation" data-title="Do you want to change status ?" data-placement="left"><i class="fa fa-check"></i></span>';
} else {
$nestedData[] = '<span class="btn btn-danger btn-xs ChangeStatus" id="'.$row['id'].'" status ="'.$row['status'].'" data-toggle="confirmation" data-title="Do you want to change status ?" data-placement="left"><i class="fa fa-times"></i></span>';
}
$nestedData[] = '<a href="review_book.php?id='.$row['id'].'" class="btn btn-warning btn-xs"><i class="fa fa-quote-left"></i></a> <a href="manage_book.php?id='.$row['id'].'" class="btn btn-primary btn-xs"><i class="fa fa-eye"></i></a> <span class="btn btn-danger btn-xs delete" id="'.$row['id'].'" data-toggle="confirmation" data-title="Do you want to delete ?" data-placement="left"><i class="fa fa-trash"></i></span>';
$data[] = $nestedData;
}
$json_data = array(
"draw" => intval($params['draw']),
"recordsTotal" => intval($totalRecords),
"recordsFiltered" => intval($totalRecords),
"data" => $data // total data array
);
echo json_encode($json_data); // send data as json format
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment