UK Date Sorting Problem

midjam

Great script, perfect for my new site! Thanks for sharing!

I`m having some problems getting the UK dates to show d/m/Y. I used DATE_FORMAT(start_date,"%d/%m/%Y") for the date column and it worked fine but, now the sorting is all messed up. I had a look around this forum and have added the sort plugin but, nothing seems to happen. I have been struggling with this for quite some time so, any feedback would be great, here is my code:

[code]

jQuery.fn.dataTableExt.oSort['uk_date-asc'] = function(a,b) {

var ukDatea = a.split('-');
var ukDateb = b.split('-');

//Treat blank/non date formats as highest sort
if (isNaN(parseInt(ukDatea[0]))) {
return 1;
}

if (isNaN(parseInt(ukDateb[0]))) {
return -1;
}

var x = (ukDatea[2] + ukDatea[1] + ukDatea[0]) * 1;
var y = (ukDateb[2] + ukDateb[1] + ukDateb[0]) * 1;

return ((x < y) ? -1 : ((x > y) ? 1 : 0));
};

jQuery.fn.dataTableExt.oSort['uk_date-desc'] = function(a,b) {
var ukDatea = a.split('-');
var ukDateb = b.split('-');

//Treat blank/non date formats as highest sort
if (isNaN(parseInt(ukDatea[0]))) {
return -1;
}

if (isNaN(parseInt(ukDateb[0]))) {
return 1;
}

var x = (ukDatea[2] + ukDatea[1] + ukDatea[0]) * 1;
var y = (ukDateb[2] + ukDateb[1] + ukDateb[0]) * 1;

return ((x < y) ? 1 : ((x > y) ? -1 : 0));
};

jQuery.fn.dataTableExt.aTypes.push(
function ( sData )
{
if (sData.match(/^(0[1-9]|[12][0-9]|3[01])\-(0[1-9]|1[012])\-(19|20|21)\d\d$/))
{
return 'uk_date';
}
return null;
}
);

$(document).ready(function() {
$('#example').dataTable( {
"iDisplayLength": 25,
"bJQueryUI": true,
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "./includes/process-datatable-search.php",
"aoColumns": [
null,
null,
{"sType": "uk_date"},
null,
null
]
} );
} );
[/code]

allan

[code]
"bServerSide": true
[/code]

You are using server-side processing, which means that the sorting is done on the server-side, not by Javascript. As such, the UK date sorting plug-in is redundant, since it is the SQL 'ORDER BY' which is doing the sort. So the question is, what does your SQL query look like? Is it using the rendered date to do the sort, or is it using the date stamp in the DB?

Allan

midjam

Thanks for the reply

I tried using:

[code]$aColumns = array( 'course_type', 'location_name', DATE_FORMAT(start_date,"%d/%m/%Y")', 'price', 'course_id');[/code]

This worked but, the sorting did not. Here's: process-datatable-search.php

[code]<?php
/*
* Script: DataTables server-side script for PHP and MySQL
* Copyright: 2010 - Allan Jardine
* License: GPL v2 or BSD (3-point)
*/

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Easy set variables
*/

/* Array of database columns which should be read and sent back to DataTables. Use a space where
* you want to insert a non-database field (for example a counter or static image)
*/
/* TRIED: DATE_FORMAT(start_date,"%d/%m/%Y") and works but, sorting does not!*/
$aColumns = array( 'course_type', 'location_name', 'start_date', 'price', 'course_id');

/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "course_id";

/* DB table to use */
$sTable = "courses";

/* Database connection information */
$gaSql['user'] = "root";
$gaSql['password'] = "root";
$gaSql['db'] = "test";
$gaSql['server'] = "localhost";


/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* If you just want to use the basic configuration for DataTables with PHP server-side, there is
* no need to edit below this line
*/

/*
* MySQL connection
*/
$gaSql['link'] = mysql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password'] ) or
die( 'Could not open connection to server' );

mysql_select_db( $gaSql['db'], $gaSql['link'] ) or
die( 'Could not select database '. $gaSql['db'] );


/*
* Paging
*/
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
{
$sLimit = "LIMIT ".mysql_real_escape_string( $_GET['iDisplayStart'] ).", ".
mysql_real_escape_string( $_GET['iDisplayLength'] );
}


/*
* Ordering
*/
if ( isset( $_GET['iSortCol_0'] ) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $i $iFilteredTotal,
"aaData" => array()
);

while ( $aRow = mysql_fetch_array( $rResult ) )
{
$row = array();
for ( $i=0 ; $i[/code]

midjam

After more investigation I have success!!

Used the code here: http://www.datatables.net/forums/discussion/6069/order-date-format-dd-mm-yyyy/p1