Unable to stop initial sorting using datatables

Question

I am using datatables 1.10.7.

My original question referenced here Question on datatables forum

I want to disable the initial sort that datatables does automatically.

Datatables refuses to listen to

"aaSorting": []

OR

"aaSorting": [[0,'desc'],[1,'desc']]

It sorts by the first column in the table in ASC order. The only way to stop this is to use

"bSort": false

however that removes all sorting functionality on my table.

My table works 100% for everything else except sorting. Filtering, searching, paging etc works 100%. When I set

"bSort" : true

or, when I leave it out, as I believe the default is true, I get the following error in a alert message:

"error":"An SQL error occurred: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY column1 ASC LIMIT 0, 10' at line 4"

I have tried with and without aaSorting, with and without bSort and mixing them together in various ways.

Any help ?

PS, here is my table definitions:

oTable = $('#table_demo').DataTable(
{
    "bSort": true,
    "aaSorting": [[0,'desc'],[1,'desc']],
    "bJQueryUI": true,
    "bPaginate": true,
    "bStateSave": true,
    "processing": true,
    "serverSide": true,
    "sPaginationType": "full_numbers",
    "ajax":
    {
        "url": "view_demo_remote.php",
        "data":
        {
            "field1": "".$_SESSION['field1']."",
            "field2": "".$_SESSION['field2']."",
            "field3": "".$_SESSION['field3'].""
        }
    },
    "columns":[
        { "bSortable": true, "data": "col1" },
        { "bSortable": true, "data": "col2" },
        { "bSortable": true, "data": "col3" },
        { "bSortable": false, "data": "col4" },
        { "bSortable": false, "data": "col5" },
        { "bSortable": false, "data": "col6" }
    ],
    "fnCreatedRow": function( nRow, aData, iDataIndex )
    {
        $(nRow).attr("attrname",aData["col1"]);
    },
    "fnDrawCallback": function( oSettings )
    {

    }
});

EDIT:

I also tried using this from the latest api , but with no success:

"order": []

EDIT:

Here is my view_demo_remote.php for the server side processing:

<?php
    /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     * Easy set variables
     */

    /* Array of database columns which should be read and sent back to DataTables. Use a space where
     * you want to insert a non-database field (for example a counter or static image)
     */

    session_start();

        $table = 'table_demo';

        $primaryKey = 'id';

        $field1 = mysql_real_escape_string($_REQUEST["field1"]);
        $field2 = mysql_real_escape_string($_REQUEST["field2"]);
        $field3 = mysql_real_escape_string($_REQUEST["field3"]);


    $columns = array(
            array(
                'db' => 'id',
                'dt' => 'DT_RowId',
                'formatter' => function( $d, $row )
                {
                    return $d;
                }
            ),
            array( 'db' => 'name',   'dt' => 'col1' ),
            array( 'db' => 'surname',   'dt' => 'col2' ),
            array( 'db' => 'title',   'dt' => 'col3' ),
            array( 'db' => 'date',   'dt' => 'col4' ),
            array( 'db' => 'telephone',   'dt' => 'col5' ),
            array( 'db' => 'email',   'dt' => 'col6' )
            );


        $sql_details = array(
                'user' => DBUSER,
                'pass' => DBUSERPASS,
                'db'   => DBNAME,
                'host' => DBHOST
        );


        require( 'libraries/DataTables-1.10.7/examples/server_side/scripts/ssp.class.php' );


        echo json_encode(SSP::complex( $_GET, $sql_details, $table, $primaryKey, $columns, "","name <>'' ORDER BY id DESC, datecreated DESC"));

Answer 1

Using order to specify that no initial ordering should occur is perfectly fine, see the example below:

$('#example').dataTable( {
    "order": []
} );

There is an error in your PHP code in call to SSP::complex. The functions expects the following arguments

static function complex ( $request, $conn, $table, $primaryKey, $columns, $whereResult=null, $whereAll=null )

but you're specifying "name <>'' ORDER BY id DESC, datecreated DESC" for $whereAll argument.

This may work if sorting is disabled, but when it's not and another column is sorted, SSP class will use two ORDER clauses (one from $whereAll argument and another based on the column being sorted) resulting in SQL error.

Replace "name <>'' ORDER BY id DESC, datecreated DESC" with "name <>''" to correct this issue.

Answer 2

Have you tried to call the order method instead?

var table = $('#example').DataTable();

// Sort by column 1 and then re-draw
table
    .order( [] )
    .draw();