AJAX + PHP + Mysqli = pagination problems

patrickmau

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";
            }
    }
}

allan

Let's step back for a moment. Do you need server-side processing? have you got 10'000 or more rows in the table? Anything less and I would suggest you let DataTables do the paging on the client-side. From 10k to 50k is a grey area where you might use server-side processing, or client-side might be okay (depending on network latency, etc). For 50k and more, use server-side processing.

If you do need server-side processing, can you show me an example of the JSON that is being returned, or use the debugger to upload a trace for me please?

Thanks,
Allan

patrickmau

To answer your questions:
1. yes, I need server side processing, POST is not an option for this particular page
2. I have min. 12k to max. 15k records at some stage per year. After 2 years, that amount will likely double which is when I will look at my DB structure.

One peculiar problem that popped up while I was fiddling around is that the filter text at the bottom shows "Showing 1 to 25 of 25 entries (filtered from 104 total entries)" which is wrong.

I should have 104 total records without any filtering from my side, displayed with 25 records per page. This leads me to believe that for whatever reason, DT loads the datatable with some filter already in place, suggesting a query issue. Looking at all my queries and when the recordsTotal and recordsFiltered variables are calculated, I cant see any problem.

I am thinking to let it run in an empty html file with only the table to see if there any problems, but am quite certain that no other elements of my site impact DT.

Anyway, debugger config upload is done, please refer to case " asojag" for more details.

Thanks,

colin

One peculiar problem that popped up while I was fiddling around is that the filter text at the bottom shows "Showing 1 to 25 of 25 entries (filtered from 104 total entries)" which is wrong.

Your server is returning this on that first draw:

    "recordsTotal": 104,
    "recordsFiltered": 25,

I checked and there doesn't appear to be a filter in place - but you can easily verify by looking at the Network tab in the browser's developer tools and see what was sent to the server for that initial draw. Filtering can be either on columns or globally, but both would be in the request.

Colin

patrickmau

I did just that in Chrome Debugger, but as far as I can say all values fit. The total records is indeed 104 and is generated via $result->num_rows; before filters are added to the SQL query.

What I dont understand is the 25 recordsFiltered value, since there are no filters in place which would mean that it should return only the total record. So where do the 25 records come from? Max rows per page value perhaps?

As far as I can tell, all the data sent and responses received are ok, narrowing it down to a PHP / Mysqli problem which I just cant seem to find...

allan

I believe the issue stems from here:

$totalFiltered = $result->num_rows;

num_rows appears to return the number of rows including the LIMIT. So since there is a limit of 25, 25 is all that is returned.

What I would suggest is that you query the DB again - this time just getting a count with the correct where condition applied (in the case of the first draw that would be no condition).

If you have a look at my demo script (which is PDO, but the algorithm still applies) you will see how it builds up three queries to get the data required.

Allan

patrickmau

Yep, thats it. Thank you very much for pointing that out.

I circumvented this issue by splitting the final part of the SQL query into 2 bits ($sql 1 + $sql2), one query which has no limit element that I can use for the count, and another that has the limit which I can use for the DT result.

                // 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']."%' ";
                }
                
                // remove LIMIT for accurate DataTables row count
                $sql2 = $sql . " ORDER BY `Medallia Collected Date 1` DESC";      // use for totalFiltered
                
                // order Datatables result by date and apply pagination
                $sql.=" ORDER BY `Medallia Collected Date 1` DESC LIMIT ".$requestData['start'].", ".$requestData['length'];
                
                // run final query
                $result = $db->query($sql) or die(mysqli_error($db));
                
                if($result->num_rows > 0) {
                    
                    // return total number of rows for pagination
                    $result2 = $db->query($sql2) or die(mysqli_error($db));
                    $totalFiltered = $result2->num_rows;
                    
                ...
                
                }

Again, thanks for all the help, this finally fixed. One other "issue" if you want though is that when I use the buttons to export the datatable content to CSV/TXT/etc, it only shows the 25 records of page instead of the full 104. That is as expected though and not really an issue.

Thanks a lot Allan!