Pagination server side datatable not work

francesco.fabbro

Hello, I came across this problem when using datatables to display records via an ajax call. The pagination shows the right number of page but all record are showed in every page. If i try to filter records using LIMIT (commented inside the below PHP code) into server side PHP using start and length variable only filtered record (1° page) are returned and is not possible to navigate into other pages. I will show all record paginated by group of 10 rows into every pages.

The Js code:

$(document).ready(function() {
                var dataTable = $('#datatable_entratauscita').DataTable( {
                    processing: true,
                    serverSide: true,
                    paging: false,
                    "ajax":{
                        url :"inc/ajax_server_processing.php", // json datasource
                        type: "post",  // method  , by default get
                        dataSrc: function (json){
                            if(json.chart_data){
                                 ...
                                 return json.data;
                        }
                    }
                } );
            } );

And the PHP server side code:

    $conn = OpenConnCloud();

    // storing  request (ie, get/post) global array to a variable
    $requestData= $_REQUEST;

    $columns = array(
    // datatable column index  => database column name
        0 => 'ID_in',
        1 => 'name',
        2 => 'date_in',
    );

    // getting total number records without any search
    $sql = "SELECT * FROM operazione";

    $search_values = $requestData['search']['value'];
    $search_values_array = explode(',', $search_values);

    // check search value exist
    if( !empty($search_values_array[0]))
        $sql.=" AND operazione.name = '".$search_values_array[0]."'";

    $sql.=" ORDER BY ID_in DESC"; //LIMIT ".$requestData['start'].", ".$requestData['length'];

    $query=mysqli_query($conn, $sql) or die("ajax_server_processing.php: get ID_operazione");
    $totalData = mysqli_num_rows($query);
    $totalFiltered = $totalData;  


    $data = array();

    while( $row=mysqli_fetch_array($query) ) {  // preparing an array
        $nestedData=array();

        $nestedData[] = $row["ID_in"];
        $nestedData[] = $row["name"];
        $nestedData[] = $row["date_in"];

        $data[] = $nestedData;
    }

    $json_data = array(
        "draw"            => intval( $requestData['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
        "IOcomplessivi"   => $IOcomplessivi,    //somma operazioni divise per tipologia e direzione
        "chart_data"      => $chart_data        //dati formattati per morris chart
    );

    echo json_encode($json_data);  
    CloseCon($conn);

allan

With server-side processing you need three SQL queries:

1. To get the data for the current page
2. To get the number of records in the data set when filtered
3. To get the number of records in the data set without filtering

Have a look at the demo implementation if you want to see how I've done it in the past.

Allan

francesco.fabbro

Hello Allan,
I have take a look to your code. I have try to compare your code with mine:

1 Data are just retrivered inside json_data[data]
2 Number of filtered records is the value of $totalFiltered. I have remove $totalFiltered = $totalData;

$query=mysqli_query($conn, $sql)
$totalFiltered = mysqli_num_rows($query);

3 Number of not filtered records is the value of $totalData. I have add the code before WHERE stantement of SQL query:

$query=mysqli_query($conn, $sql)
$totalData = mysqli_num_rows($query);

The pagination of datatable is wrong too. I have all the records diplayed on all pages of datatable (the total number of displayed page is right and the number of record per page too). Have I to return inside $json_data[data] only the record of the current view (using LIMIT at the end of datatbase query)? If yes, how to tell datatable that that the passed data are only one page and not all the records?

That is what I have now, all 26 entries in every one of 3 pages: (
"")

allan

Sounds like the limits aren't being correctly applied in the SQL.

The data should only return 10 rows. I'd start there - make sure that the query that gets the data is applying a LIMIT based on the length submitted.

Allan

francesco.fabbro

Ok, I have uncommented the LIMIT part of query (row 23 of first code original code psted above).

$sql.=" ORDER BY ID_in DESC LIMIT ".$requestData['start'].", ".$requestData['length'];

Now i have the right number of records per page, but the total number of page is wrong (only one). If Ichange the "show entries" values, for example from 10 to 50, all the 26 record are correctluy displayed (maybe because all record could stay in only one page).

allan

Have you added the two other SQL queries I mentioned?

As I said, you need three queries:

1. To get the data for the current page
2. To get the number of records in the data set when filtered
3. To get the number of records in the data set without filtering

Allan

francesco.fabbro

Yes, I have try but maybe there is something wrong in my code. This is my code:

    $conn = OpenConnCloud();
    $requestData= $_REQUEST;

    $columns = array(
    // datatable column index  => database column name
        0 => 'ID_in',
        1 => 'name',
        2 => 'date_in',
    );

    $sql = "SELECT * FROM operazione";
     
    //query 3
    $query=mysqli_query($conn, $sql) or die("ajax_server_processing.php: get ID_operazione");
    $totalData = mysqli_num_rows($query);
    
    $search_values = $requestData['search']['value'];
    $search_values_array = explode(',', $search_values);
     
    if( !empty($search_values_array[0]))
        $sql.=" WHERE operazione.name = '".$search_values_array[0]."'";
     
    $sql.=" ORDER BY ID_in DESC LIMIT ".$requestData['start'].", ".$requestData['length'];
     
    // query 2
    $query=mysqli_query($conn, $sql) or die("ajax_server_processing.php: get ID_operazione");
    $totalFiltered = mysqli_num_rows($query);
     
    $data = array();
     
    // quey 1
    while( $row=mysqli_fetch_array($query) ) {  // preparing an array
        $nestedData=array();
     
        $nestedData[] = $row["ID_in"];
        $nestedData[] = $row["name"];
        $nestedData[] = $row["date_in"];
     
        $data[] = $nestedData;
    }
     
    $json_data = array(
        "draw"            => intval( $requestData['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); 
    CloseCon($conn);

allan

Query 2 has the LIMIT attached to it, which it should not.

Allan

francesco.fabbro

Thanks for the response Allan,
If I move the quert 2 before sql .= ... on row 23 the result is the same like the first print screen. I have all the records diplayed on all pages of datatable (the total number of displayed page is right and the number of record per page too).
Maybe something wrong inside Js part?

allan

No, the JS part is fine. I'm still seeing only two queries in your code above (i.e. two calls to mysqli_query).

As I said, you need three queries:

1. To get the data for the current page
2. To get the number of records in the data set when filtered
3. To get the number of records in the data set without filtering

francesco.fabbro

Thank you!