Mysql – Auto update “TIMESTAMP” column only when a manual phptheadmin edit occurs

MySQLphpmyadmin

I have a column in my MySQL database that uses a "TIMESTAMP" to capture the current time. I have an attribute set to "on update CURRENT_TIMESTAMP" so it auto updates the column whenever and update change is made.

Works great, except I have a small issue. My database also tracks views/votes for each row so basically the TIMESTAMP column is always being update because of the views/votes being updated. This isn't exactly what I want as I only want the TIMESTAMP column to update when significant information is changed that is not views/votes.

Is there a way to make phpmyadmin force this column to update when I make a manual edit on a row using the phpmyadmin edit gui? Besides having to type in the current TIMESTAMP which is just too time consuming for me.

Thanks!

Best Answer

The defaults for TIMESTAMP are quite limited. Instead of depending on it, take charge. You should supply values when you need them:

UPDATE tbl  SET
        ...,
        ts = NOW()   -- Include this when you need it; else leave it out.
    WHERE ...

Related Solutions

Mysql – Setting up a use voting table

If you want to restrict your votes to "one user can vote for another user once", you simply use the two user id:s as primary key. As for the vote, just use one column and make it an enum:

CREATE TABLE IF NOT EXISTS `user_votes` (
  `voter` int(11) NOT NULL,
  `voted_for` int(11) NOT NULL,
  `vote` enum('up','down') NOT NULL,
  PRIMARY KEY (`voter`,`voted_for`)
)

MySQL Auto Update Column – How to Auto Update Column After 24 Hours

CREATE EVENT reset
    ON SCHEDULE
      EVERY 1 HOUR
        DO
update T1 
set state=1 
where time < date_sub(now(),interval 24 hour) 
  and (state=0 or state=2) ;

it will run in every hour

Best Answer

Related Solutions

Mysql – Setting up a use voting table

MySQL Auto Update Column – How to Auto Update Column After 24 Hours

Related Question