Using 'order': [1, 'asc'] and 'orderable': false create a SQL error

dudanesk

Hello,
On my server-side datatable, I'd like to order a column (using 'order': [1, 'asc']) during initialisation, but I want to disable ordering for the end user (using 'orderable': false). This creates the error: "DataTables warning: table id=filterContinent - 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 '' at line 4"

HTML:

                    <table id="filterContinent" class="display" width="175px">
                        <thead>
                            <tr>
                                <th width="25px"></th>
                                <th width="175px">Continent</th>
                            </tr>
                        </thead>
                    </TABLE>

JAVASCIPT:

        var continentTable = $('#filterContinent').DataTable( {
            dom: 'Bfrtip',
            "serverSide": true,
            "paging": false,
            "info": false,
            'order': [1, 'asc'],
            'columnDefs': [
                {'targets': 0, 'checkboxes': {'selectRow': true}},
                {'targets': 1, 'orderable': false},
            ],
            select: {
                style: 'multi'
            },
            "ajax": {
                "url": "m_continent.php",
                "type": "POST"
            },
        } );

SERVER-SIDE SCRIPT:

```
<?php

$table = 'continent';
$primaryKey = 'id';
$columns = array(
array( 'db' => 'id', 'dt' => 0 ),
array( 'db' => 'continent_name', 'dt' => 1 )
);

$sql_details = array(
'user' => '',
'pass' => '',
'db' => '',
'host' => ''
);

require( 'ssp.class.php' );

echo json_encode(
SSP::simple( $_POST, $sql_details, $table, $primaryKey, $columns )
);

<?php > ``` ?>

Thank you for your time

allan

Can you use:

'order': [[1, 'asc']]

please?

order should be a 2D array.

Allan

dudanesk

I can and I did, but unfortunately this doesn't fix the issue. I have the same error. If I comment or remove the line {'targets': 1, 'orderable': false} for troubleshooting purpose, then I have no error message. Of course, the column is orderable then... Thank you for getting involved Allan, I do appreciate it

allan

Got it - thanks. I've committed a fix for the invalid SQL that is being generated there.

However, that is coming from the fact that ordering is being performed on a column that is not orderable. The server-side processing demo script actually checks if a column is orderable or not before performing any ordering on it. You might need to disable that check if you want that ability in your script.

Allan

dudanesk

Allan, I feel bad for all the work you're putting on to fix my issue. At the very least, I hope many people can benefit from it. Now, I used your fix on "ssp.class.php" and the good news is {'targets': 1, 'orderable': false} does work the way it should. But it seems that my 'order': [[1, 'asc']] on initialization doesn't work anymore though (which makes sense since no ordering is being made at all anymore).

I tried to fix that by implementing a static order straight in "ssp.class.php". This would work in my case because I have 4 server side datatables using "ssp.class.php" and they all follow the same pattern: I'd like a [[1, 'asc']] on initialization and a {'targets': 1, 'orderable': false} to disable ordering ability for end users on all 4 datatables. But I lack knowledge here and I can't find the right syntax. Something like:
$order = 'ORDER BY ' . $columns[1];
return $order;

Does it make any sense?

Again, a big fat thanks to you mate

allan

Hi,

No worries - glad I can help .

If you need to be able to order the table by column index 1, but still mark it as not orderable, you'll need to modify the SSP class slightly.

Specifically comment out this line and the closing brace on line 135. Keep lines 130-134 active. You just want to remove the check on the orderable flag.

Allan

dudanesk

That's it!
Thank you