Complex Table Sorting

We track project progression in a single database. Over the last two years, the requirements of management have changed the format of which we store dates (originally, just the year, then year month, now full dates). To ensure there were no complaints from the database, the previous administrator set-up the table to store the dates in varchar instead of date. I've created a web-interface for users to access and generate reports, but the webpage is static (user-side). Management wants to be able to sort by click on the table headers. That's easy. My problem is those dates! Because of the formats, they don't sort correctly.

Here's the three forms the dates can appear in from the PHP: %Y, %Y%m, and %c/%e/%Y.

I'm using tablesorter to sort the table, but I can't figure out how to sort the dates via the jQuery. The inital sorting is done entirely in the MySQL query:

ORDER BY
 CASE WHEN cstatus = 'Complete' THEN 1 ELSE 0 END DESC,
 CASE WHEN CHAR_LENGTH(cdevelopmentStart) > 6 THEN
  DATE_FORMAT(STR_TO_DATE(cdevelopmentStart, '%m/%d/%Y'), '%c/%e/%2014')
 ELSE
  CASE WHEN CHAR_LENGTH(cdevelopmentStart) > 4 THEN
   DATE_FORMAT(STR_TO_DATE(cdevelopmentStart, '%Y%m'), '%c/%e/%Y')
  ELSE
   DATE_FORMAT(STR_TO_DATE(cdevelopmentStart, '%Y'), '%c/%e/%Y')
  END
 END DESC,
 id DESC

I need to replicate the sorting for the date columns in the jQuery sorting.

Hopefully, I'll have more luck after a nights rest, but I wanted to post this up in case anyone can give me a headstart. ^^

Solution:

I modified the query to, instead of sorting by the conditions initially, leave it to default sorting and creating additional columns to populate the data-text attribute, as suggested by Mottie.

SELECT
 CASE WHEN cdevelopmentStart IS NULL THEN
  '01/01/2012'
 ELSE
  CASE WHEN CHAR_LENGTH(cdevelopmentStart) > 6 THEN
   DATE_FORMAT(STR_TO_DATE(cdevelopmentStart, '%m/%d/%Y'), '%m/%d/%Y')
  ELSE
   CASE WHEN CHAR_LENGTH(cdevelopmentStart) > 4 THEN
    DATE_FORMAT(STR_TO_DATE(cdevelopmentStart, '%Y%m'), '%m/01/%Y')
   ELSE
    DATE_FORMAT(STR_TO_DATE(cdevelopmentStart, '%Y'), '01/01/%Y')
   END
  END
 END AS developmentSort,
 CASE WHEN courses.cstatus = 'Complete' THEN
  0
 ELSE
  CASE WHEN courses.cstatus = 'Cancelled' THEN
   1
  ELSE
   CASE WHEN courses.cstatus = 'Hold' THEN
    2
   ELSE
    CASE WHEN courses.cstatus = 'Review' THEN
     3
    ELSE
     CASE WHEN courses.cstatus = 'Rework' THEN
      4
     ELSE
      CASE WHEN courses.cstatus = 'Open' THEN
       5
      ELSE
       6
      END
     END
    END
   END
  END
 END AS statusSort

Then, I added data-text="' . $result['developmentSort'] . '" and data-text="' . $result['statusSort'] . '" to the corresponding columns of the PHP-generated table. Lastly, I set-up the default sorting and instructed it to pull from the data-text attribute, if set, by adding the following to the <HEAD>:

$("#mainlist").tablesorter({
 textAttribute: 'data-text',
 usNumberFormat: true,
 sortList: [[19, 0], [6, 1], [0, 1]]
});

Thank you, Mottie, for the quick reply and solution. :)

Solution

Maybe the easiest solution would be to add a data-attribute to each table cell which contains a "properly" formatted date string

<td data-text="1/1/2015">2015 01</td>

then, if you use my fork of tablesorter, you can use the following code (demo):

$(function () {
    $('table').tablesorter({
        theme: 'blue',
        // data-attribute that contains alternate cell text
        textAttribute: 'data-text',
        // false for German "1.234.567,89" or French "1 234 567,89"
        usNumberFormat: true
    });
});

If you must use the original tablesorter, then you can use this code with the same HTML (demo):

$(function () {
    $('table').tablesorter({
        // set to "us", "pt", "uk", "dd/mm/yy"
        dateFormat: "us",
        // custom table cell text extraction method
        textExtraction: function (node) {
            var $node = $(node);
            return $node.attr('data-text') || $node.html();
        }
    });
});