How to auto update a second column if first column is edited

bips

Description of problem: Say I have a table with two columns and they are - "payment_status" and "date". User can only view and edit/update the column "payment_status" with available dropdown options - Paid or Dues. Let whatever option they chose to update the column "payment_status", the another column "date" should update automatically with the current date-time (similar to NOW() ) in my database

My table have both inline editing and primary editing options enabled. I am using newest version.

Please suggest (I am a beginner). There are already few questions in the forum similar to mine but those are very old (for old version)

tangerine

MySQL can update timestamp fields automatically. You would need to apply the attribute "ON UPDATE CURRENT_TIMESTAMP()".

bips

@tangerine - The table I mentioned in my question is just an example. I can't apply the attribute on my database as you have mentioned because in my real table there are many other columns too which my user keeps updating.

rf1234

This is easy to find in the editor back end documentation. Just search for “set“, “setValue“, setFormatter etc.. If you can't get it going please post your code or at least let us know what programming language you are using at the back end.

allan

A server-side event (assuming you are using out libraries), such as preEdit, in combination with the setValue method that rf1234 mentioned is the way to go here.

Allan

bips

@rf1234 , @allan I looked into the documentation and tried doing what documentation is saying but doing this make the column auto update every time even when I update non-desired column.
Here is what I am doing:

Field::inst( 'payment_status' ) ->set( Field::SET_EDIT ),
Field::inst( 'payment_date' )
    ->on( 'preEdit', function ( $editor, $id, $values ) {
               $editor
                   ->field( 'payment_date' )
                   ->setValue( date("Y-m-d h:i:s") );
        } )

This works. If I update the column "payment_status", the column "payment_date" is updating automatically with current date-time.
BUT problem is, I also have many other columns in that table for e.g. column "delivery_status", "delivery_date", etc. Now if I update the column "delivery_status", the column "payment_date" too is getting auto update which I don't want.

I want column "payment_date" to auto update only when column "payment_status" is updated not when other columns are updated.

As I stated I am beginner with dataTables/Editor and have less knowledge of js, therefore if you suggest with example code, I will highly appreciate it.

Back end programming language is PHP and phpMyAdmin (database)

tangerine

Your preEdit function should be applied to your 'payment_status' field, not your 'payment_date' field.

rf1234

I want column "payment_date" to auto update only when column "payment_status" is updated not when other columns are updated.

Don't know whether the ->set() method for field payment_status is wrong or merely redundant: If you want that field to be set on create and on update you don't need anything at all. Just the field definition.

But lets take a look at your problem.
If you are using submit: "all" which is the default Editor form option it is not so easy to determine whether your field has changed or not. https://editor.datatables.net/reference/option/formOptions.main
You could change the form options to submit: "changed". If you do that you can check on preEdit whether or not the value has changed just by checking for "isset" with PHP. If the variable is not set it cannot have been changed. If it is set it must have been changed. The downside is that you won't have all values accessible in your back end Editor instance. This doesn't have to be a disadvantage but it can be in some use cases (e.g. if you need other submitted field values for your setFormatters or for validation purposes).

The best way in my opinion is this one:
Leave the submission defaults and don't update your date field with Editor. Use a db trigger to update the date field whenever status changes. If the database does the job for you you don't have to do anything programmatically.

Here is something on SO which should be helpful:
https://stackoverflow.com/questions/34612791/mysql-after-update-update-datetime-column-of-same-table

But I'm sure you'll find many other posts on this elsewhere. It only took me one google search to find it.

allan

Agreed - I’d use a database trigger for this personally.

That said, if you do want to do it client-side, you would use a preEdit (or preCreate) server-side event handler to check if the value of the payment status has been set or changed. If it has, then inside the preEdit you would set the value for the payment date field.

Allan

rf1234

If it has, then inside the preEdit you would set the value for the payment date field.

In my opinion this client side solution is probably the best option apart from using the trigger.