How can I add DATE_FORMAT on the server side script?

TarponWebGuy

Hi there!
I'm am using the Server side (PHP) script to help me load the data faster when the page loads. Much like the example here: http://datatables.net/release-datatables/examples/data_sources/server_side.html

I have it working for the most part, however, I cannot figure out how to convert a timestamp from one of my columns in the database to just a date (such as MM/DD/YYYY) or the time (HH:MM am/pm). If I don't apply a date formatter, then I get the exact same timestamp in two columns; which prevents me from having the option to sort the table by time or date. I tried replacing one of my columns with a mysql DATE_FORMAT function to apply the date or time, but then I got an error that says [code] "Unknown column 'DATE_FORMAT(timeStamp,'%b %e, %Y')' in 'field list' [/code]

If you have any ideas on how I can apply the DATE_FORMAT on one of my columns from the DataBase, then please do share. Otherwise, I cannot move forward and I will have to rely on javascript to load most of the data.

Below is my "server_side_process.php" script:

[code]
<?php
include 'functions.php';
$customerID = 65;

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* 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)
*/
$aColumns = array( "DATE_FORMAT(timeStamp,'%b %e, %Y')", "DATE_FORMAT(timeStamp,'%h:%i %p')", 'identifier', 'identifier', 'viewed_Type' );

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

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

/* Database connection information */
$gaSql['user'] = "--USER--";
$gaSql['password'] = "--password--";
$gaSql['db'] = "--db--";
$gaSql['server'] = "--server--";

/* REMOVE THIS LINE (it just includes my SQL connection user/pass) */
//include( $_SERVER['DOCUMENT_ROOT']."/datatables/mysql.php" );


/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* 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
*/
$sOrder = "";
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]

Thank you for you time.

TarponWebGuy

Also, I'd like to do the all sorting by mysql (on the .php server script) if possible. I'm not a big fan of all the sorting being relied on javascript.

TarponWebGuy

I've already looked at all of these solutions:
http://datatables.net/forums/discussion/122/x#Item_8
http://datatables.net/forums/discussion/81/x

And none of them are helping me. However, this link: http://datatables.net/forums/discussion/7252/how-to-solve-date_format-sorting-issue-in-server_processing.php/p1

seems to be the best solution so far. I haven't got it to work yet, but I'm working on it.