Mysql timestamp not updating

franks59

I have a field in my table defined as follows:
TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

I can confirm via Mysql Workbench that when I change a record, the current timestamp is assigned.

However, when I use the Editor, the timestamp does not change at all.

Any suggestions for a starting point to debug?

Thanks,
Frank

The PHP code for the table is:

$data = Editor::inst( $db, 'hosts', 'host_key' )

->field(

    Field::inst( 'hosts.host_key' ),

    Field::inst( 'hosts.host_type' ),

    Field::inst( 'hosts.host_fqdn' ),

    Field::inst( 'hosts.host_ip' ),

    Field::inst( 'hosts.host_site' )

                ->options( 'sites', 'sites.sites_code', 'sites.sites_city' ),

    Field::inst( 'hosts.host_location' ),

    Field::inst( 'hosts.host_desc' ),

    Field::inst( 'hosts.host_mod_dte' ),

    Field::inst( 'hosts.host_mod_by' )

)

->leftJoin( 'sites', 'sites.sites_code', '=', 'hosts.host_site' )

->process( $_POST )

->data();

echo json_encode( $data );

The JS for the Editor and DT is:

var hosts_editor = new $.fn.dataTable.Editor( {
    ajax: {
                url:"php/table.hosts.php",
                type: "POST"
            }, 
    "table": "#hosts",
    "fields": [
        {
            "label": "Type",
            "name": "hosts.host_type"
        },
        {
            "label": "FQDN",
            "name": "hosts.host_fqdn"
        },
        {
            "label": "IP",
            "name": "hosts.host_ip"
        },
        {
            "label": "Site",
            "name": "hosts.host_site",
                            "type": "select"
        },
        {
            "label": "Location",
            "name": "hosts.host_location"
        },
        {
            "label": "Description",
            "name": "hosts.host_desc"
        },
        {
            "label": "Date Modified",
            "name": "hosts.host_mod_dte"
        },
        {
            "label": "Modified By",
            "name": "hosts.host_mod_by"
        }
    ]
} );

var dt_hosts = $('#hosts').DataTable( {
    "dom": "Tfrtip",
    ajax: {
                url:"php/table.hosts.php",
                type: "POST"
            }, 

    "columns": [
                { "data": "hosts.host_key"},
                { "data": "hosts.host_type"},
                { "data": "hosts.host_fqdn"},
                { "data": "hosts.host_ip" },
                { "data": "hosts.host_site" },
                { "data": "hosts.host_location" },
                { "data": "hosts.host_desc"}                        ,
                { "data": "hosts.host_mod_dte"},
                { "data": "hosts.host_mod_by"}
    ],
    "tableTools": {
        "sRowSelect": "os",
        "aButtons": [
            { "sExtends": "editor_create", "editor": hosts_editor },
            { "sExtends": "editor_edit",   "editor": hosts_editor },
            { "sExtends": "editor_remove", "editor": hosts_editor }
        ]
    }
} );

tangerine

{
            "label": "Date Modified",
            "name": "hosts.host_mod_dte"
 },

Is that your TIMESTAMP field? If so, I suggest omitting it from the Editor. Your MySQL will update your timestamp field by default whenever any other field is altered.

franks59

Thanks, Tangerine. That worked.

I guess the way the mysql On Update works is, if you set an explicit Timestamp, it will override the CURRENT_TIMESTAMP property.

Frank

allan

Yup - that is exactly how it works :-). You would need to use a trigger if you wanted to override that.

Allan