CODEWITHSUNDEEP
phpjquerymysqldatedatatables
Ajay
Ajay

Reputation: 705

jQuery Datatables MySQL date to PHP date

I'm using datatables server side processing to retrieve all values for my table. One column renders registration dates of users which is in MySQL date format. Can it be converted to PHP date format while displaying.

$('#data').dataTable( {
        "bProcessing": true,
        "bServerSide": true,
        "sAjaxSource": "userlist.php",
} );

Thanks.

Working Solution :

Added this to server-side but no effect,

while ($aRow = mysql_fetch_array($rResult)) {
$row = array();
for ($i = 0; $i < count($aColumns); $i++) {
    if ($aColumns[$i] == "version") {
        /* Special output formatting for 'version' column */
        $row[] = ($aRow[$aColumns[$i]] == "0") ? '-' : $aRow[$aColumns[$i]];
    } elseif ($aColumns[$i] == "date") {
        /* Special output formatting for 'date' column */
        $date = new DateTime($aRow["date"]);
        $timestamp = $date->getTimestamp();
        $row[] = date("d-m-Y", $timestamp);
    } elseif ($aColumns[$i] != ' ') {
        /* General output */
        $row[] = $aRow[$aColumns[$i]];
    } 
}
$output['aaData'][] = $row;

}

One of my mysql columns has the name 'date'.

Upvotes: 1

Views: 2179

Answers (1)

Stefan
Stefan

Reputation: 3900

In userlist.php just use PHP date('d-m-Y', $timestamp) or whatever to reformat the dates as you wish before sending output from the script.

RESPONSE TO COMMENT:

This code is untested, but the principle is as follows:

You would have a date column in your SQL table, let's say it's called 'date'. Therefore in the example script:

$aColumns = array('date', etc.);

At bottom of script, in 'output' section, you see this:

if ( $aColumns[$i] == "version" )
{
    /* Special output formatting for 'version' column */
    $row[] = ($aRow[ $aColumns[$i] ]=="0") ? '-' : $aRow[ $aColumns[$i] ];
}
/* insert this date formatting statement here */
elseif ( $aColumns[$i] == "date" ) {
    /* Special output formatting for 'date' column */
    $date = new DateTime($aRow["date"]);
    $timestamp = $date->getTimestamp();
    $row[] = date("d-m-Y", $timestamp);
}

If you need to do sorting on the date column, it is possible that you might need to use a date sorting plugin, because datatables don't provide default sorting for all date formats.

Upvotes: 1

Related Questions