Automatically updating date column on edit in phpMyAdmin

Question

I have a small website I'm working on and for this project I want to use phpMyAdmin's user interface to edit items directly in the database instead of building an admin user interface for the website. I'm going to quick edit items like this:

_{(source: forumbilder.se)}

(Not actually using wordpress, just took a random database table to use as an example)

The problem is that I have a column holding the datetime for when the row was last edited, and I don't know how to make said column update itself automatically after an edit like this is made. Any help would be appreciated.

Answer 1

You can implement this by using a TIMESTAMP column appplying the ON UPDATE CURRENT_TIMESTAMP clause - See https://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html

So if you are creating the table from scratch:

CREATE TABLE t1 (
  ...
  post_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  post_content ...
  ...
);

or if you already have the table, you can alter the relevant column:

ALTER TABLE t1 MODIFY post_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Note that the column definition in both examples sets a default value of the current date/time for new records. That is usually how this feature tends to be implemented, but of course your requirement may vary, so you may wish to set the default differently.

UPDATE: If you wish to implement this using phpMyAdmin:

1. Browse to the table in question
2. Click the Structure tab
3. Click the edit icon for the relevant column
4. In the next page, set the column properties as below: