Server Side pagination not working

AmitaSingh · May 2020

I don't understand what i am doing wrong. Please help me

   <?php
    // SQL server connection information
    /* DB table to use */


    include '../conn/Session.php';
    include '../conn/MySQL.php';
    $db =  new MySQL();
    $company = $_SESSION["userdata"]['company'];
    $utype = $_SESSION["userdata"]['utype'];

    $sTable = "psb_billing";

    $aColumns = array( 'b_id', 'cr_note', 'invoice_date', 'invoice_no', 'job_no', 'client', 'branch', 'invoice_amount_wt_gst', 'igst', 'cgst', 'sgst', 'total_gst','currency', 'conversion_rate', 'added_by', 'created_date');

    /* Indexed column (used for fast and accurate table cardinality) */
    $sIndexColumn = "b_id";



        /* 
         * Paging
         */
        if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
            {
                $sLimit = "LIMIT ".mysql_real_escape_string( $_GET['iDisplayStart'] ).", ".
                    mysql_real_escape_string( $_GET['iDisplayLength'] );
            }

    /*
     * Ordering
     */
    if ( isset( $_GET['iSortCol_0'] ) )
        {
            $sOrder = "ORDER BY  ";
            for ( $i=0 ; $i<intval( $_GET['iSortingCols'] ) ; $i++ )
            {
                if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" )
                {
                    $sOrder .= $aColumns[ intval( $_GET['iSortCol_'.$i] ) ]."
                        ".mysql_real_escape_string( $_GET['sSortDir_'.$i] ) .", ";
                }
            }

            $sOrder = substr_replace( $sOrder, "", -2 );
            if ( $sOrder == "ORDER BY" )
            {
                $sOrder = "";
            }
        }

    /* 
     * Filtering
     * NOTE this does not match the built-in DataTables filtering which does it
     * word by word on any field. It's possible to do here, but concerned about efficiency
     * on very large tables, and MySQL's regex functionality is very limited
     */
    $sWhere = "";
        if ( $_GET['sSearch'] != "" )
        {
            $sWhere = "WHERE (";
            for ( $i=0 ; $i<count($aColumns) ; $i++ )
            {
                $sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ";
            }
            $sWhere = substr_replace( $sWhere, "", -3 );
            $sWhere .= ')';
        }

    /* Individual column filtering */
    for ( $i=0 ; $i<count($aColumns) ; $i++ )
        {
            if ( $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
            {
                if ( $sWhere == "" )
                {
                    $sWhere = "WHERE ";
                }
                else
                {
                    $sWhere .= " AND ";
                }
                $sWhere .= $aColumns[$i]." LIKE '%".mysql_real_escape_string($_GET['sSearch_'.$i])."%' ";
            }
        }


    /*
     * SQL queries
     * Get data to display
     */
        $sQuery = "
            SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
            FROM   $sTable
            $sWhere
            $sOrder
            $sLimit
        ";
        $rResult = mysql_query( $sQuery ) or die(mysql_error());

    /* Data set length after filtering */
        $sQuery = "
            SELECT FOUND_ROWS()
        ";
        $rResultFilterTotal = mysql_query( $sQuery ) or die(mysql_error());
        $aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
        $iFilteredTotal = $aResultFilterTotal[0];

        /* Total data set length */
        $sQuery = "
            SELECT COUNT(".$sIndexColumn.")
            FROM   $sTable
        ";
        $rResultTotal = mysql_query( $sQuery ) or die(mysql_error());
        $aResultTotal = mysql_fetch_array($rResultTotal);
        $iTotal = $aResultTotal[0];




    while ( $aRow = mysql_fetch_array( $rResult ) )
    {
        $row = array();
            for ( $i=0 ; $i<count($aColumns) ; $i++ )
            {
                if ( $aColumns[$i] == "version" )
                {
                    /* Special output formatting for 'version' column */
                    $row[] = ($aRow[ $aColumns[$i] ]=="0") ? '-' : $aRow[ $aColumns[$i] ];
                }
                else if ( $aColumns[$i] != ' ' )
                {
                    /* General output */
                    $row[] = $aRow[ $aColumns[$i] ];
                }
            }

         /* For Edit*/
            if($utype == "E"){
            $row[]='<button type="button" id="getEdit" class="btn btn-primary btn-xs"  data-id="'.$row[0].'"><i class="glyphicon glyphicon-pencil">&nbsp;</i>Edit</button>
                    <button type="button" id="getDelete" class="btn btn-danger btn-xs" data-id="'.$row[0].'"><i class="glyphicon glyphicon-trash">&nbsp;</i>Delete</button>';
            }else{
                 echo "<td><b>Permission given to concern person</b></td>";   
            }/* For Edit end */
        $output['aaData'][] = $row;

    }

    /*
     * Output
     */
    $output = array( 
            'draw' => 0,    
            "sEcho" => intval($_GET['sEcho']),
            "iTotalRecords" => $iTotal,
            "iTotalDisplayRecords" => $iFilteredTotal,
            "aaData" => $output['aaData']
    );


    header('Content-Type: application/json');
    echo json_encode( $output );
    ?>

allan · May 2020

Can you link to a page showing the issue please? We need to be able to see the client-side code and also what the server is responding with.

'draw' => 0,

This is wrong. draw should never be 0. Also sEcho and draw should never be used together. You've mixed the legacy and current protocols together there which is probably why it isn't working for you.

I'd suggest using the SSP class or use the Editor libraries which you can do for just server-side processing without Editor (client-side).

Allan

AmitaSingh · May 2020

Thanks your reply. i am using this code on view page. Every thing working with this code. Only pagination not working.

`<script type="text/javascript" language="javascript" >
        $(document).ready(function(){
            var dataTable = $('#billingtable').DataTable({               
                "bProcessing": true,
                "bServerSide": true,   
                "ajax":{
                    url:"server_side/fetch_billing.php",
                    type:"post"
                },
                "fnRowCallback" : function(nRow, aData, iDisplayIndex){
                    $("td:first", nRow).html(iDisplayIndex +1);
                   return nRow;
                }

            });

        });
    </script>`

tangerine · May 2020

You need to deal with the points raised in Allan's reply.

AmitaSingh · May 2020

Hi,
I am working on my local server(Xampp). I am attaching the screenshot of my json return data.

In a view page show all data than show pagination. see the attached image.

Thank You.

colin · June 2020

As Allan said, please link to your page so we can diagnose the issue.

Colin

AmitaSingh · June 2020

Thanx for reply Colin.
I have already solved the issue.

Fariv · July 2021

@AmitaSingh Can you please answer how did you solve the Issue? I may be facing similar issue.

allan · July 2021

@Fariv If you can provide a link to your page, then we can take a look and try to help resolve it.

Allan

Fariv · July 2021

Hi @allan

Thanks for responding.

The page I have is confidential, so regretfully, cannot share that page.
If i paste the relevant codes here, can you be able to guess the issue or error?

tangerine · July 2021

You could provide a test case using dummy data.
https://datatables.net/manual/tech-notes/10

Fariv · July 2021

Hi @tangerine @allan ,

Here's the full view link of test case

And here, you can see the codes

As you may notice that, First time when page loads, datatable is loading first 10 records, but later, searching in the input box and pagination not working even though server is sending correct response until I'm wrong.

kthorngren · July 2021

It doesn't look like your server script is handling the draw parameter correctly. It looks like it is always returning 1. This is an example of the response after clicking on Page 2:

{
    "draw": 1,
    "recordsTotal": "28619",
    "recordsFiltered": "28619",
    "data": [{
....

Datatables has sent draw: 2 for the page 2 request:

draw: 2
columns[0][data]: message

The Server Side Processing docs explain the SSP protocol. The draw parameter is a sequence and Datatables looks for the draw sequence number it sent in the response. You need to update your server script to return the proper draw value.

Kevin

Fariv · August 2021

Hi @kthorngren

Thank you for the solution.

It seems I did not understand the draw parameter.

But now I understand it and it is working smoothly.

Thanks again.

jaydeep549 · August 2021

Hello can please help
pagination not working here

allan · August 2021

@jaydeep549 Please link to your page so we can help debug it.

Allan

kthorngren · August 2021

@jaydeep549

[draw] => 1

Is your script always returning [draw] => 1. If so you need to read how this parameter is used. See the above comments in this thread.

Kevin

Server Side pagination not working

Server Side pagination not working

I don't understand what i am doing wrong. Please help me

Answers

Howdy, Stranger!

Categories

DataTables

Server Side pagination not working

Server Side pagination not working

I don't understand what i am doing wrong. Please help me

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables