Datatable not sorting as per sql stmt

moris

Hi

I am having a problem with a particular table. The sql statement is as follows
[code]SELECT * FROM page WHERE pagetype = 'Draft' ORDER BY pagename,datesaved DESC[\code]

when datatable is applied the initial sort is not DESC, I have tried it without datatables and it works as it should

Can anyone assist as to why

Thanks

Moris

allan

Hi Moris,

I presume that this query is used to create the table? The thing is that DataTables uses 'asc' as it's default, and does it's own sorting (it has no communication with the SQL statement there, so it doesn't know you are doing a desc sort). You can use aaSorting to change the default sorting that DataTables does: http://datatables.net/examples/basic_init/table_sorting.html .

Note that DataTables uses 'asc', 'desc' for the order that the ordering will be used when the user clicks on a column. In this example I've overridden that to be the reverse for the last column. The default remains for the other columns.

Regards,
Allan

mblander

Hey Moris and Allan,

Im comming up with the same problem i guess but can't find a solution. With my query i select form my database some data.

The date field i get a date form (yyyy/mm/dd) is getting reversed in my query see the following example: "SELECT * , date_format( date, '%d-%m-%Y' ) FROM tbl_table ORDER BY date DESC"

The table I put the data in shows all in correct order. but when i 'install' datatables on the table the date gets ordered as a number. When i show the date like yyyy/mm/dd it will show in the right order.

Can you maybe give me some advice for sorting my date?(dd-mm-yyyy) or maybe tell me how i can disable the ordering?

Thanks in advance,

Mike

allan

Hi Mike,

One thing to remember is that the sorting information (i.e. what direction to sort in and what column) is passed from DataTables to the server-side. What happens if you 'hard code' "order by date desc" into your SQL (which is the impression I get), you are always going to get that. If this is what you want then you should disable sorting in DataTables with bSort:false, but if you want the user to be able to select the order, then you need to do something like in my examples: http://datatables.net/examples/server_side/server_side.html

Hope this helps,
Allan

mblander

Thank you very much Allan! this solved my problem. making the php fit my oop structure right now..

Thanks!

Mike