Editor - split a database column data into two editor fields

obrienj

I have a database column, "start", defined as type DATETIME.

In the editor I would like to split "start" into two editor fields, date and time, so I can use separate "pickers".

Likewise, once created/edited I would like to combine the date and time editor fields back into the database column "start".

I would like the date in "MM/DD/YYYY" format and time in "hh:mm a" format.

Being a bit new to Datatables and Editor, I'm not sure where to start..

Any examples would be welcome.

Regards,
Jim

allan

Hi Jim,

Formatters are the way to do this.

You would have one field defined that will get the date (and format it), and will also set both the date and time (deformatting them from the client-side to ISO8601).

Have a second field which is get only (->set( false )) and uses a formatter to get only the time.

Basically the JSON you send to the server needs to contain both the date and time - splitting them on the client-side wouldn't be all that easy.

Allan

obrienj

Allan,

Thank you for the input.

Do you know of an example of this process, i.e., what the client-side and server-side look like structurally?

This is the last piece of the puzzle I need to resolve.

Regards,
Jim

allan

I'm afraid I don't have an example of that specifically. The key is the custom get formatters. You'll need to define two custom get formatting functions, they could just do a split on the string read from the db, or it might be best to use strtodate and date to format the output.

Allan

rf1234

Jim,
I have just implemented it myself. I am using one database field that contains the date and time in the usual mysql format of 'Y-m-d H:i:s'. The field is "bidtime_end" in table "rfp".
These are the relevant field instances on the server (PHP). You see I select the same field twice and alias it in the second instance. I also set the aliased field to false so that Editor does not try to update the database for the alias.

Field::inst( 'rfp.bidtime_end' )
        ->getFormatter( function ( $val, $data, $opts ) {
            return getFormatterDate($val);                     
        } )
        ->setFormatter( function ( $val, $data, $opts ) {
            return setFormatterCombineDateTime($val, $data);
        } ),
Field::inst( 'rfp.bidtime_end AS bidEndTime' )->set( false )
        ->getFormatter( function ( $val, $data, $opts ) {
            return getFormatterTime($val);                     
        } ),

These are the functions I call in the example above:

function getFormatterDate(&$val) {
    if ($_SESSION['lang'] === 'de') {     
        return date( 'd.m.Y', strtotime( $val ) );
    } else {
        return date( 'd/m/Y', strtotime( $val ) );
    }        
}

function setFormatterCombineDateTime(&$val, &$data) {
    $date = setFormatterDate( $val );
    $date = date( 'Y-m-d', strtotime( $date ) );
    
    $time = setFormatterDate( $data['bidEndTime'] );
    $time = date( 'H:i:s', strtotime( $time ) );
    
    return $dateTime = $date . ' ' . $time;
}

function setFormatterDate(&$val) {
    $dateTime = $val;
    $dateTime = str_replace(
        ['Januar ', 'Februar ', 'März ', 'April ', 'Mai ', 'Juni ', 'Juli ',
            'August ', 'September ', 'Oktober ', 'November ', 'Dezember '],
        ['January ', 'February ', 'March ', 'April ', 'May ', 'June ', 'July ',
            'August ', 'September ', 'October ', 'November ', 'December '],
        $dateTime);
    $dateTime = str_replace('/', '-', $dateTime);
    $dateTime = str_replace('.', '-', $dateTime);    
    
    return date( 'Y-m-d H:i:s', strtotime( $dateTime ) );
}

function getFormatterTime(&$val) {
    if ($_SESSION['lang'] === 'de') {     
        return date( 'H:i', strtotime( $val ) );
    } else {
        return date( 'H:i', strtotime( $val ) );
    }        
}

On the client side it looks like this.
a) Editor fields:

{
    label: "Bid Period End Date:",
    name:  "rfp.bidtime_end",
    type:  "datetime",
    def:   function () { return tomorrow },
    format: 'L',
    opts:  {
        showWeekNumber: true,
        momentLocale: momentLocale
    }
}, {
    label: "Bid Period End Time:",
    name:  "bidEndTime",
    type:  "datetime",
    def:   function () { return endOfDay },
    format:  'HH:mm'
}

b) DataTable column:

{   data: "rfp.bidtime_end",
    render: function ( data, type, row ) {                   
        return row.rfp.bidtime_end + ' ' + row.bidEndTime;
    }    
}

just to complete the picture the variable "momentLocale" is global and defined elsewhere. It is set to the required language. "tomorrow" and "endOfDay" are also global variables.

obrienj

Great! Thanks.