4

I am fairly new to phpmyadmin and SQL Databases.

I would like to know how I can add a "script" that resets a value in all columns of a table after a certain time period -> I want the column to set the int value to 0 every 72hours for every row. Is that possible and how?

Isaac Bennetch
  • 11,830
  • 2
  • 32
  • 43
Brinkhoff
  • 43
  • 1
  • 3

1 Answers1

5

What you want is called an "event". Here's a potential definition; your needs may vary.

CREATE EVENT `zero_my_column`
    ON SCHEDULE
        EVERY 72 HOUR STARTS '2015-07-13 00:00:00'
    ON COMPLETION PRESERVE
    ENABLE
DO BEGIN
         UPDATE mytable SET counter = 0 WHERE counter <> 0;
END

There's some configuration work to do to ensure your MySQL server will run this event correctly.

This is the actual update query.

UPDATE mytable SET counter = 0 WHERE counter <> 0;

Notice the WHERE clause. That prevents redundant updating of rows that already have a zero column value for counter.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • On StackOverflow, it's best to mark an answer Accepted (click the green check) if it helped. That way, others with similar questions can find your question and the answer. – O. Jones Jul 14 '15 at 17:07
  • 1
    Since you mention using phpMyAdmin, you may be interested in the "Events" tab which you can see from any database page. – Isaac Bennetch Jul 16 '15 at 18:18