{"id":3219,"date":"2020-09-16T17:43:34","date_gmt":"2020-09-16T22:43:34","guid":{"rendered":"http:\/\/bluegalaxy.info\/codewalk\/?p=3219"},"modified":"2020-09-16T17:43:36","modified_gmt":"2020-09-16T22:43:36","slug":"mysql-how-to-automatically-update-timestamp-column-in-phpmyadmin-when-a-row-is-edited","status":"publish","type":"post","link":"https:\/\/bluegalaxy.info\/codewalk\/2020\/09\/16\/mysql-how-to-automatically-update-timestamp-column-in-phpmyadmin-when-a-row-is-edited\/","title":{"rendered":"MySQL: How to automatically update timestamp column in phpMyAdmin when a row is edited"},"content":{"rendered":"\n

I have a table in my database called “usercardsetplaycounts” that has a column called “updatedAt” that I want to get updated with a fresh timestamp every time a row is edited. <\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

When I originally set this column up, I set the default to CURRENT_TIMESTAMP<\/code>. Here is what it looks like in phyMyAdmin:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

The updatedAt date is set to the timestamp of when the table was created, and does not change when rows are edited. <\/p>\n\n\n\n

To fix this, I went to the Structure view in phpMyAdmin and clicked the “Change” link in the Action column. <\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Which then loads the details for this column:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

I then changed the Type from DATETIME<\/code> to TIMESTAMP<\/code>.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

And I changed the Attributes from blank to “on update CURRENT_TIMESTAMP”. For example:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Then on the right side of the page, I clicked the “Preview SQL” button:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Which made this small box appear with the SQL that is being executed:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

The actual SQL code for this table update is:<\/p>\n\n\n\n

ALTER TABLE `usercardsetplaycounts` CHANGE `updatedAt` `updatedAt` TIMESTAMP on update CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;<\/pre>\n\n\n\n
After closing this dialog, all I had to do was click the “Save” button to make this change to the database. Now whenever any row in this table is updated, the updatedAt column will contain a fresh timestamp for that row. Here is what the Structure looks like after the change:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
I have a table in my database called “usercardsetplaycounts” that has a column called “updatedAt” that I want to get updated with a fresh timestamp every time a row is edited. When I originally set this column up, I set the default to CURRENT_TIMESTAMP. Here is what it looks like in phyMyAdmin: The updatedAt date … Continue reading MySQL: How to automatically update timestamp column in phpMyAdmin when a row is edited<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[112],"tags":[108,220],"class_list":["post-3219","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-mysql","tag-phpmyadmin"],"_links":{"self":[{"href":"https:\/\/bluegalaxy.info\/codewalk\/wp-json\/wp\/v2\/posts\/3219","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/bluegalaxy.info\/codewalk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bluegalaxy.info\/codewalk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bluegalaxy.info\/codewalk\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/bluegalaxy.info\/codewalk\/wp-json\/wp\/v2\/comments?post=3219"}],"version-history":[{"count":8,"href":"https:\/\/bluegalaxy.info\/codewalk\/wp-json\/wp\/v2\/posts\/3219\/revisions"}],"predecessor-version":[{"id":3237,"href":"https:\/\/bluegalaxy.info\/codewalk\/wp-json\/wp\/v2\/posts\/3219\/revisions\/3237"}],"wp:attachment":[{"href":"https:\/\/bluegalaxy.info\/codewalk\/wp-json\/wp\/v2\/media?parent=3219"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bluegalaxy.info\/codewalk\/wp-json\/wp\/v2\/categories?post=3219"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bluegalaxy.info\/codewalk\/wp-json\/wp\/v2\/tags?post=3219"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}