Server Side pagination not working

Server Side pagination not working

AmitaSinghAmitaSingh Posts: 6Questions: 2Answers: 0
edited May 2020 in Free community support

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 );
    ?>

This question has accepted answers - jump to:

Answers

  • allanallan Posts: 61,450Questions: 1Answers: 10,055 Site admin
    Answer ✓

    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

  • AmitaSinghAmitaSingh Posts: 6Questions: 2Answers: 0

    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>`
    
  • tangerinetangerine Posts: 3,342Questions: 35Answers: 394

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

  • AmitaSinghAmitaSingh Posts: 6Questions: 2Answers: 0

    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.

  • colincolin Posts: 15,118Questions: 1Answers: 2,583

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

    Colin

  • AmitaSinghAmitaSingh Posts: 6Questions: 2Answers: 0

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

  • FarivFariv Posts: 4Questions: 0Answers: 0

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

  • allanallan Posts: 61,450Questions: 1Answers: 10,055 Site admin

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

    Allan

  • FarivFariv Posts: 4Questions: 0Answers: 0

    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?

  • tangerinetangerine Posts: 3,342Questions: 35Answers: 394

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

  • FarivFariv Posts: 4Questions: 0Answers: 0
    edited 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.

  • kthorngrenkthorngren Posts: 20,145Questions: 26Answers: 4,736
    Answer ✓

    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

  • FarivFariv Posts: 4Questions: 0Answers: 0

    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.

  • jaydeep549jaydeep549 Posts: 1Questions: 0Answers: 0
    Array
    (
        [draw] => 1
        [recordsTotal] => 11
        [recordsFiltered] => 10
        [data] => Array
            (
                [0] => Array
                    (
                        [id] => 1
                        [branchname] => ahmedabad test
                        [branchref] => BR-0010
                        [actions] => 
                    )
    
                [1] => Array
                    (
                        [id] => 2
                        [branchname] => Canada
                        [branchref] => BR-0008
                        [actions] => 
                    )
    
                [2] => Array
                    (
                        [recid] => 3
                        [branchname] => indai
                        [branchref] => BR-0019
                        [actions] => 
                    )
    
                [3] => Array
                    (
                        [id] => 4
                        [branchname] => india
                        [branchref] => BR-0024
                        [actions] => 
                    )
    
                [4] => Array
                    (
                        [id] => 5
                        [branchname] => India
                        [branchref] => BR-0042
                        [actions] => 
                    )
    
                [5] => Array
                    (
                        [recid] => 6
                        [branchname] => new added ahmedabad after sorting and filter cases 
                        [branchref] => BR-0033
                        [actions] => 
                    )
    
                [6] => Array
                    (
                        [recid] => 7
                        [branchname] => Test branch
                        [branchref] => BR-0026
                        [actions] => 
                    )
    
                [7] => Array
                    (
                        [id] => 8
                        [branchname] => Test branch
                        [branchref] => BR-0030
                        [actions] => 
                    )
    
                [8] => Array
                    (
                        [id] => 9
                        [branchname] => Test branch
                        [branchref] => BR-0031
                        [actions] => 
                    )
    
                [9] => Array
                    (
                        [id] => 10
                        [branchname] => Test branch final edit 
                        [branchref] => BR-0035
                        [actions] => 
                    )
    
            )
    
    )
    

    Hello can please help
    pagination not working here

  • allanallan Posts: 61,450Questions: 1Answers: 10,055 Site admin

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

    Allan

  • kthorngrenkthorngren Posts: 20,145Questions: 26Answers: 4,736

    @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

Sign In or Register to comment.