0

I'am currently using this Serverside SQLite version of Datatables

http://www.abrandao.com/2014/03/server-side-datatables-with-sqlite-or-mysql-via-pdo/

As you can see in their online demo it has a strange sorting order

http://www.abrandao.com/lab/datatable_pdo/client.php

A
B
C
a
b
c

Is it possible to change it to this without a helper cell ?

a
A
b
B
c
C

or to

A
a
B
b
C
c
Bennek
  • 25
  • 4
  • you can use a php sort function as [PHP Sorting](https://www.w3schools.com/php/php_arrays_sort.asp) – Rene May 04 '20 at 07:12
  • Or you use the SQL Query functions with Order by [sql_orderby](https://www.w3schools.com/sql/sql_orderby.asp) – Rene May 04 '20 at 07:15
  • Does this answer your question? [How to use SQL Order By statement to sort results case insensitive?](https://stackoverflow.com/questions/2413427/how-to-use-sql-order-by-statement-to-sort-results-case-insensitive) – Rene May 04 '20 at 07:21
  • Sorry, I didn't understand the question in the first place but can you check the link to the proposed duplicate? – Rene May 04 '20 at 07:23
  • I am not sure if its the same Problem. – Bennek May 04 '20 at 08:22
  • Why not? The user asked, if a SQL Query function exists to get a descending or ascending order, which is not case sensitive: so instead of A B C a b c the following is needed: A a b B C c. Therefore, you can use s.th. like this: SELECT * FROM movie_table movie ORDER BY movie COLLATE NOCASE ASC – Rene May 04 '20 at 09:06
  • I was a little bit confused because of the desired order of Aa bB Cc g T. I think its a typo. He wants Aa Bb Cc g T. I saw no system in it ! Sorry my mistake. – Bennek May 05 '20 at 00:29

1 Answers1

1

(Untested) example sippets

You could extend the underlying database query / request from the example site (see Server Side information)

Existing Query:

$pdo = new ServerDataPDO($db_dsn,$db_user,$db_pass,$d['sql'],$d['table'],$d['idxcol']);
$result=$pdo->query_datatables(); //now return the JSON Requested data */
echo $result;

Needed query extension

$pdo->query("SELECT * FROM movie_table movie ORDER BY movie COLLATE NOCASE ASC");

If you look into the code on the bottom of the site (you need to download the zip file), you can find the function "query_datatables()" in line 273 in serverdatapdo.php. Within this function, you are able to see all defined base queries.

At the beginning of the function, you will find the variable "sOrder". So, I guess, in your case you have to change the definition of "sOrder" to change the SQL Query.

Existing code

            $sOrder .= "`".$this->aColumns[ intval( $_GET['iSortCol_'.$i] ) ]."` ".
                ($_GET['sSortDir_'.$i]==='asc' ? 'asc' : 'desc') .", ";

Proposed code

            $sOrder .= "`".$this->aColumns[ intval( $_GET['iSortCol_'.$i] ) ]."` ".
                ($_GET['sSortDir_'.$i]==='asc' ? 'COLLATE NOCASE ASC' : 'COLLATE NOCASE desc') .", ";

EDIT

Otherwise, you can also work (Sorting etc.) with the entire object in your php code, which you receive from the database. But in this case, your server is doing the same job more or less two times. :)

Rene
  • 976
  • 1
  • 13
  • 25