dataTable switch to next page with no entry - paging issue

Question

I want to display records from mysql-database on a monitor using a table from dataTables with serverside paging. Maximum rows per side should be 14. When filling up the database and reaching the 14th row the table already starts paging (!) showing the message "no matching records found".... then it switches back to the page with 1-14 rows....

Any clue what I am doing wrong here?

My code is as follows:

php-page creating json array

//fetch.php
$connect = new PDO("mysql:host=localhost;dbname=eflightbook", "root", "");
$column = array("usersName", "usersFirstname", "fldirector");
$query = "SELECT usersName, usersFirstname, TIME(date), fldirector, t1.*
FROM users, flightbook t1
WHERE usersLoginStatus <> 'false'
AND id = anw_id
AND t1.date = (SELECT MAX(t2.date)
                FROM users, flightbook t2
                WHERE t2.anw_id = t1.anw_id)

";

if(isset($_POST['order']))
{
    $query .= 'ORDER BY '.$column[$_POST['order']['0']['column']].' '.$_POST['order']['0']['dir'].' ';
}
else
{
    $query .= 'ORDER BY TIME(date) ASC ';
}
$query1 = '';
if($_POST['length'] != -1)
{
    $query1 = 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
}
$statement = $connect->prepare($query);
$statement->execute();
$number_filter_row = $statement->rowCount();
$result = $connect->query($query . $query1);
$data = array();
foreach($result as $row)
{
    $sub_array = array();
    $sub_array[] = $row['usersName'];
    $sub_array[] = $row['usersFirstname'];
    $sub_array[] = $row['TIME(date)'];
    $sub_array[] = ($row['fldirector'] == "1") ? "✔" : "";
    $data[] = $sub_array;
}
function count_all_data($connect)
{
    $query = "SELECT anw_id, usersName, usersFirstname, TIME(date), fldirector, t1.*
    FROM users, flightbook t1
    WHERE usersLoginStatus <> 'false'
    AND id = anw_id
    AND t1.date = (SELECT MAX(t2.date)
                    FROM users, flightbook t2
                    WHERE t2.anw_id = t1.anw_id)
    
    ";
    $statement = $connect->prepare($query);
    $statement->execute();
    return $statement->rowCount();
}
$output = array(
    "draw"      =>  intval($_POST["draw"]),
    "recordsTotal"  =>  count_all_data($connect),
    "recordsFiltered"   =>  $number_filter_row,
    "data"      =>  $data
);
echo json_encode($output);
?>


**Monitor html/php Page**
  

<script>

// Active Pilots Table 

$(document).ready(function(){
    function load_data(start, length)
    {
        var dataTable = $('#datatable').DataTable({
            "processing" : false,
            "serverSide" : true,
            "pageLength" : 14,
            "lenghtChange" : false,
            "language": {
             "emptyTable": "** kein Eintrag **"},
             "columnDefs": [
                       {
                        targets: -1,
                        className: 'dt-body-center'
                       }],
            "responsive" : false,
            "autoWidth" : false,
            "ordering" : false,
            "searching" : false,
            "scrollCollapse" : true,
            "binfo" : false,
            "bFilter" : false,
            "bLengthChange" : false,
            dom: "lfrti",
            "order" : [],
           "retrieve": true,
            "ajax" : {
                url:"activep.php",
                method:"POST",
                data:{start:start, length:length}
            },
            "drawCallback" : function(settings){
                var page_info = dataTable.page.info();

                console.log(page_info);
             
            }
        });
    }
    load_data();


    var table = $('#datatable').DataTable();

    setInterval(function(){ 

    var info = table.page.info();

    if (info.start < info.end) {
        var page_number = (info.page < info.pages) ? info.page + 1 : 1; 
    }
    else {
        page_number = 0;
     
    }
    ;

    table.page(page_number).draw(false); 

}, 6000);

});

Answer 1

Your problem is here:

var info = table.page.info();

if (info.start < info.end) {
    var page_number = (info.page < info.pages) ? info.page + 1 : 1; 
}
else {
    page_number = 0;
 
}
;

table.page(page_number).draw(false);

There are two issues. First, to specifically answer your question: assuming info.page is equal to info.pages (which means you have one page of records) you are setting the page number to 1, but that property is indexed at 0 (and also documentation here), so you are actually forcing it to the second page:

var page_number = (info.page < info.pages) ? info.page + 1 : 1;

and then later, you pass that page number value to your table:

table.page(page_number).draw(false);

And the second issue (which is not the cause of your specific problem but is definitely problematic in terms of variable scope): you define the page_number variable inside the scope of the if statement but then use it outside of that scope. That type of variable declaration should be avoided. You should declare the variable outside of the if statement.