phpmyadmin Question posted in
The official documentation can be found here.

Auto Incrementing data in a column as each day passes – Phpmyadmin

In my database, a table has a column with an integer that needs to increment every day, counting the days that have passed from a date.

Is there any way I can do this?
I know Auto Increment exists, but I don’t know if it fits for this occasion.

I found a solution using mysql events, but now I’m having trouble with the syntax.
PHPMyadmin gives me a form to complete.

https://imgur.com/Lhru1ZJ

I’m having trouble because I don’t know what informations to put into it.

Tags:

3

Answers


  1. 0

    You should have a look at event schedulers in MySQL, you could use them to run a job that increments your values once a day.

    MySQL Event Scheduler

    Login or Signup to reply.
  2. 0

    The best way to do this is to compute the elapsed days in a query, not to update the table every day.

    For example, suppose you have a table with columns id and start_date.

    This query gives you the elapsed days.

    SELECT id, 
       DATEDIFF(CURDATE(), start_date) elapsed
  FROM tbl

    Doing it this way is better than changing the table every day, for several reasons.

    1. It always works even if the event doesn’t fire for some reason.
    2. Updating an entire table can get more and more expensive as the table grows.
    3. The computational cost of computing the elapsed days is far less than the computational cost of updating every day.
    4. If you happen to use incremental backups, updating the whole table defeats that.
    5. It’s a good practice to avoid representing the same information multiple times in the database.

    You can also add a generated (virtual) column to the table or use a VIEW.

    Login or Signup to reply.
  3. 0

    The following example creates a recurring event that updates a row in a table.

    First, create a new table named counter.

    CREATE TABLE counter (
    id INT PRIMARY KEY AUTO_INCREMENT,
    counts VARCHAR(255) NOT NULL,
    created_at DATETIME NOT NULL
);

    Second, create an event using the CREATE EVENT statement:

    CREATE EVENT IF NOT EXISTS count_event
ON SCHEDULE AT EVERY interval STARTS timestamp [+INTERVAL] ENDS timestamp [+INTERVAL]
DO
  UPDATE counter SET counts = counts + YOUR_COUNT WHERE id = YOUR_ID;

    Replace interval timestamps, your_count and your_id with real variables

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search