Basic JSON sorting problem

tdmohr

Hello Everyone,

I have a table that gets all of its data using $_POST server side JSON outputs.

In that table there are many events that all have a start date and time.

I noticed that Datatables was sorting by date but not by time. I did some research here on the forums and found that others were having the same problem.

This post http://datatables.net/forums/comments.php?DiscussionID=1952&page=1#Item_0 appealed to me so I modified my code and was successfully outputting pure integers when I noticed something very strange. The order was still the in the same wrong order.

Datatables was still not sorting the correctly, even though I was giving it one big integer...

After much troubleshooting and brainstorming I remembered my old pal, fnColumnToField()

This has caused me problems before: http://stackoverflow.com/questions/3011021/jquery-datatables-problems-with-post-server-side-json-output

I noticed that its column was only giving a date output. I added my time output to the end of it and suddenly it was sorting correctly.

[code]else if ( $i == 3 )
return "datestart, timestart";[/code]

I have two questions. Where can I read about fnColumnToField(). I can guess from the name that it has something to do with the JSON output, but I cannot find any reference to it on this site or from a google search.

The next question is, now that I have added the extra time output it is sorting correctly on ASC, however trying to change it to DESC and it doesn't sort correctly.

I am hoping that when I find a reference to fnColumnToField(), the reason why this is happening will become obvious.

Sorry about the long post.

Tim

allan

Hi Tim,

1. fnColumnToField: There isn't really any documentation for this function - it's a trivial little thing which simply converts an integer index (i.e the column number) into a column name in the table. So for example if in your DataTable the first column (index 0) has it's data source as the table column "first_name", fnColumnToField would take "0" and return "first_name".

In my new server-side PHP scripts I've actually now done away with this function, to try and make it a little easier to modify: http://datatables.net/development/server-side/php_mysql . Now basically the position in the array of each field does the same thing as the old fnColumnToField function.

However... When I wrote the script (both versions) I hadn't anticipated using multiple fields, so there will need to be a couple of alterations to take account of that...

The easiest way to do that would probably be to write a number of special conditions into the bits of the script which build up the SQL statement. So check for the column index (or even the column name if you want it to be independent of the table structure) and modify the constructed SQL as required.

2. The reason for this is that the script will simple append ASC or DESC to the string returned from the function. So something like "ORDER BY datestart, timestart DESC" - however, you want "ORDER BY date start DESC, timestart DESC".

Allan