I am using php and this query select * from table and the result I want to show in jquery datatable in server side processing means I want to use jquery datatable's own ajax method. I have all total 58 rows. So by default I should have 6 page. 10 rows per page. Data is coming and is shown in the table. but with wrong pagination.
Actually it shows all 58 rows in the first page and also showing 6 pagination buttons. If I click on anything nothing is working. searching and sorting not working.
I am not pasting the sql code here but the I am pasting the procedural code of php here.
php code:
require_once("logic/LogsDataLogic.php");
$ldl = new LogsDataLogic();
$data = $ldl->getDataByFromDateToDate();
$arr = [
"draw" => $_POST["draw"],
"recordsTotal" => count($data),
"recordsFiltered" => count($data),
"data" => $data
];
echo json_encode($arr);
jquery code:
$(document).ready(function(){
$('#logs_table').DataTable( {
"processing": true,
"serverSide": true,
ajax: {
url: '../app/getData.php',
type: "POST"
}
} );
});
html code:
<link href="https://cdn.datatables.net/1.10.15/css/jquery.dataTables.min.css" type="text/css" rel="stylesheet">
</head>
<body>
<table class="display" cellspacing="0" width="100%" id="logs_table">
<thead>
<tr>
<th>ID</th>
<th>User ID</th>
<th>Login Time</th>
<th>IP</th>
</tr>
</thead>
<tfoot>
<tr>
<th>ID</th>
<th>User ID</th>
<th>Login Time</th>
<th>IP</th>
</tr>
</tfoot>
</table>
<!-- end here -->
<script src="https://code.jquery.com/jquery-1.12.4.js"></script>
<script src="https://cdn.datatables.net/1.10.15/js/jquery.dataTables.min.js"></script>
I have attached an image of the final result.Table Image here
Sol 1:
if you dont want pagination from server just set "serverSide": false, this will bring all your record ie 58 at one go but pagination will work.
Sol 2:
if you want pagination from server side means fetch only 10 record each time follow these steps
you have added "serverSide": true, so when you look into console along with other parameters some additional parameters is passed. which you need to handle in your server side query with limit 20,10
refer: https://datatables.net/examples/server_side/simple.html
eg:
draw:4
columns[0][data]:0
start:20
length:10
search[value]:
search[regex]:false
Explanation:
when you set serverSide as false
datatable gets all records from server(in your case 58) and paginate in UI as suggested(10 rec each page) so when you chnage page no ajax call is fired
when you set serverSide as true
Datable gets only first 10 record and when you change page ajax call is fired to get another set of 10 records. Datatable adds some parameters along with request and expect you to handle that in server side eg: first record and total no of record per set and total no of record available
SO for each request start and length is passed to get no of records from which index
in response datable expect "draw":2,"recordsTotal":57,"recordsFiltered":57 along with data
draw is unique key which you can use from request recordsTotal is no no of record : for this you need additiona query to get count of all records