Datatables: Previous data still displayed on the Next Page

perintahsql

Hello Everyone!

i have a problem when using datatables to show data from postgresql, here are my problem.

i have 666 entries on my tables, but in the first page the datatables show a message No matching records found, even though the datatables displayed that it's Showing 1 to 10 of 666 entries,

And when i try to go to the next page (2nd page), the message was gone and the data from 11 to 20 records displayed.

And then when i try to go to the third page, the datatables still displayed the previous data (from the 2nd page) but the data from 21 to 30 records diplayed too, even though the datatables displayed that it's Showing 21 to 30 of 666 entries.

it was occuring when i go to the next page, the previous data still displayed in datatables.

Would you mind to tell me a solution for my problem? because i have been stuck on it for almost 1 weeks.
Thanks.

here's my server side processing code,

<?php //include connection file include_once("connection.php"); // initilize all variable $params = $columns = $totalRecords = $data = array(); $params = $_REQUEST; //define index of column $columns = array( 0 =>'terminal_id', 1 =>'tid', 2 => 'merchant_id', 3 => 'encrypted_ktm' ); $where = $sqlTot = $sqlRec = ""; // check search value exist if( !empty($params['search']['value']) ) { $where .=" WHERE "; $where .=" ( tid::text LIKE '%".$params['search']['value']."%' "; $where .=" OR merchant_id::text LIKE '%".$params['search']['value']."%' "; $where .=" OR encrypted_ktm LIKE '%".$params['search']['value']."%' )"; } // getting total number records without any search $sql = "SELECT terminal_id, tid, merchant_id, encrypted_ktm FROM terminal"; $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']." OFFSET ".$params['length']." "; $queryTot = pg_query($sqlTot); $totalRecords = pg_num_rows($queryTot); $queryRecords = pg_query($sqlRec); //iterate on results row and create new index array of data while( $row = pg_fetch_row($queryRecords) ) { $data[] = $row; } $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 ?>

And here's my Javascript Code
$( document ).ready(function() {
$('#employee_grid').DataTable({
"processing": true,
"serverSide": true,
/"ajax": "response.php"./
"ajax":{
url :"response.php", // json datasource
type: "post", // type of method , by default would be get
error: function(){ // error handling code
$("#employee_grid_processing").css("display","none");
}
}
});
});

perintahsql

I have got the solution, the problem was query sqlRec on the limit and offset syntax.
thanks everyone!