OK, I am at a loss with this one. I have searched all over, read the manual (which has a completely different syntax and only gives PDO examples) and in general spent way too much time on this just because I want to make it work in AJAX because its the right thing to do
So, I obviously have a datatable with lots of records and am struggling to get the pagination to work, search and ordering all work fine.
Following the manual, I can gather that the pagination is dealt with by using the LIMIT command on the SQL query, which then again gets the amount of rows from the $_REQUEST variable, modifying the query accordingly to show the correct start, end number of rows.
My problem now seems to be that datatables() indeed correctly calculates the number of rows that should be displayed (eg. 25 of 100), but then does not paginate, leaving me stuck on the 1st page with the same 25 entries.
This is supposed to be simple and comes out of the box when doing a classic POST submit, but when dealing with an AJAX call it all seems to get infinitely more difficult....
Appreciate any help with his as I am ready to give up. Thanks
Javascript:
<script type="text/javascript" language="javascript" class="init">
$(document).ready(function() {
$('#med_feedback').DataTable( {
// load table data via AJAX
"processing": true,
"serverSide": true,
"ajax":{
url: "../../plugins/MySQL/ajax_action.php", // json datasource
data: { action:"view_med_surveys", property: $("#property_select").val(), date: $("#daterangepicker").val() },
type: "POST", // connection method (default: GET)
},
"columns": [
{ "aaData": "Svy_ID" },
{ "aaData": "OSAT" },
{ "aaData": "INT" }
],
columnDefs: [
{ // adjust survey output
targets: [0],
render: function (data, type, row, meta) {
var Svy_ID = row[0]; // define Survey as a variable since array is an object
return '<a href="#" data-toggle="modal" data-target="#DetailSurveyModal" data-keyboard="true" data-id="' + Svy_ID +'">' + Svy_ID + '</a>';
},
}
],
select: {
style: 'single',
//items: 'cell'
},
dom: 'Bfrtip',
stateSave: true,
buttons: [
'copyHtml5',
'excelHtml5',
'csvHtml5',
'pdfHtml5',
{
extend: 'print',
message: 'DO NOT DISTRIBUTE'
},
{
extend: 'collection',
text: 'Others',
buttons: [
{
text: 'Toggle button',
action: function ( e, dt, node, config ) {
dt.column( -4 ).visible( ! dt.column( -4 ).visible() );
}
},
'colvis',
'columnsToggle',
]
},
],
"pagingType": "full_numbers",
"pageLength": 25,
"lengthChange": true,
"searching": true,
"ordering": false,
//"order": [[ 1, "asc" ], [ 3, "asc" ]],
"info": true,
"autoWidth": true
})
});
</script>
Serverside PHP:
if(isset($_POST['action']) && ($_POST['action'] == 'view_med_surveys')) {
if(isset($_SESSION['Access'])) {
// Start MySQLi connection
include 'connect_db.php';
$db = new mysqli($dbhost,$dbuser,$dbpass,$dbname);
// display error if connection cannot be established
if($db->connect_errno > 0){
die('Unable to connect to database [' . $db->connect_error . ']'); }
// define variables
$requestData = $_REQUEST; // DataTables AJAX request
$property = mysqli_real_escape_string($db,$_POST['property']);
$med_date = str_replace(" - ","' AND '", mysqli_real_escape_string($db,$_POST['date']));
// check if table exists
$result = $db->query("SELECT `Survey ID` FROM `medallia_import_".$property."` WHERE WHERE DATE(`Survey Collected Date 1`) BETWEEN '".$med_date."' LIMIT 1");
//if ($result->num_rows > 0) { // if at least one record is found, proceed accordingly
// show all records
$sql = "SELECT `Survey ID`, `Overall Experience`, `Internet Service` FROM `medallia_import_".$property."` WHERE DATE(`Survey Collected Date 1`) BETWEEN '".$med_date."'";
// run query to get total number of records
$result = $db->query($sql) or die(mysqli_error($db));
$totalData = $result->num_rows;
// if there is a search parameter, $requestData['search']['value'] contains search parameter
if( !empty($requestData['search']['value']) ) {
$sql.=" AND ( `Survey ID` LIKE '".$requestData['search']['value']."%' ";
$sql.=" OR `Overall Experience` LIKE '".$requestData['search']['value']."%' ";
$sql.=" OR `Internet Service` LIKE '".$requestData['search']['value']."%' ";
}
// sort by collection date - NO PAGINATION
$sql.=" ORDER BY `Survey Collected Date 1` DESC LIMIT ".$requestData['start'].", ".$requestData['length']." ";
//$sql.=" ORDER BY `Survey Collected Date 1` DESC"; // this works
// run final query
$result = $db->query($sql) or die(mysqli_error($db));
if($result->num_rows > 0) {
// return total number of rows for pagination
$totalFiltered = $result->num_rows;
// return table data - MUST BE NON-ASSOCIATIVE ARRAY
while($row = mysqli_fetch_array($result)) {
$data[] = array(
$row['Survey ID'],
$row['Overall Experience'],
$row['Internet Service']
);
}
// finalize array with elements required by DataTable plugin
$json_data = array(
"draw" => intval( $requestData['draw'] ), // unique draw number identifier (required)
"recordsTotal" => intval( $totalData ), // total number of records
"recordsFiltered" => intval( $totalFiltered ), // total number of records after searching, if there is no searching then totalFiltered = totalData
"success" => true, // success message - false / true (required)
"aaData" => $data // table data as array
);
echo json_encode($json_data);
} else {
echo "No data found";
}
}
}
