Getting DataTables to sort and paginate server side data

lschneiderman

I have successfully gotten DataTables to fetch my data using server side processing. However, it gives me all the data without dividing it into pages and it doesn't sort the columns when I click the column headers. Can someone tell me what I'm doing wrong?

jquery

$('#medProcDataList').change(function() { //when the dropdown containing hospital codes changes
        
        var $selectedValue = $(this).val();
        var arrSelVal = $selectedValue.split(":");
        $code = arrSelVal[0]; //get the selected hospital code
        
        $('#results').DataTable( {
            "processing": true,
            "serverSide": true,
            "ajax": {
            "url": "./php/getDataForCode.php",
            "type": "POST",
            "dataSrc":"", //I think the problem is here
            "data": {code: $code}
            },
            columns: [
                    { title: "Hospital", data: "hospital_name" },
                    { title: "Description", data: "raw_description" },
                    { title: "Insurer", data: "full_payer_name" },
                    { title: "Insurer type", data: "plan_type"},
                    { title: "You Pay", data: "price" }
            ]
        } );
    
});

PHP/SQL

$db = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
if (mysqli_connect_errno()) {
        printf("Connect failed: %s", mysqli_connect_error());
        exit;
}
mysqli_set_charset($db, 'utf8'); //important! or it won't echo the array
    
if( $_POST) {
        $code = mysqli_real_escape_string($db, $_POST['code']); 
        $data = array();
        $q = "SELECT hospital_name, raw_description, full_payer_name, plan_type, concat('$', format(price,2)) as price FROM `hospital_transparency_data` where procedure_codes = '" . $code . "' order by hospital_name, full_payer_name";
        $result = $db->query($q);
        while($row = $result->fetch_array(MYSQLI_ASSOC)) {
            //Add this row to the reply
            $data[] = $row;
        }
        
        
        $db->close();
        echo json_encode($data); //returns all data
    
} //if POST

Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

colin

It doesn't look like your script is doing any of that - paging or sorting, so yep, that would happen. You could use the supplied scripts, or if not, you'll have to follow the protocol. The protocol is discussed here. Also see examples here.

Cheers,

Colin

lschneiderman

Hi, @colin, I thought I was following the protocols -- I set processing to true and serverSide to true. I followed this example: https://datatables.net/examples/server_side/simple.html and this example: https://datatables.net/manual/server-side. Can you be specific about what is missing please?

kthorngren

The server script needs to follow the protocol. Click on the Server-side script tab of the example. The script uses ssp.class.php. The ssp.class.php is an example of a server side processing script and can be found here:
https://github.com/DataTables/DataTables/blob/master/examples/server_side/scripts/ssp.class.php

Kevin