Using 'order': [1, 'asc'] and 'orderable': false create a SQL error
Using 'order': [1, 'asc'] and 'orderable': false create a SQL error
dudanesk
Posts: 21Questions: 5Answers: 0
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 )
);
Thank you for your time
This question has an accepted answers - jump to answer
Answers
Can you use:
please?
ordershould be a 2D array.Allan
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
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
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
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
orderableflag.Allan
That's it!
Thank you