How to delete data from database after specified h

Question

I am new to mysql I am using phpmyadmin 5.1.43. User perform transaction lets say he reserve a seat and i want that after 48 hours of reservation if he does not pay for reservation his reservation should automatically be cancelled?? how would i do it?

Answer 1

Deleting rows after X amount of time is not the relational way of tackling this kind of problem. Rather you would want to add enough data into the tables so that you can query it and get the result you want.

Ie. add a column "expire_at" or similiar as a datetime, and when you query the table add a clause WHERE expire_at < NOW()

You can even add this to a view which you can then query in an easier way.

Answer 2

You could do it everytime someone asks for a reservation. If the seat is already reserved by someone, and the timestamp of reservation is registered on a specific column, you could get the "age" of the reservation by comparing it with the current date:

mysql> select timestampadd(HOUR, 48, now());
+-------------------------------+
| timestampadd(HOUR, 48, now()) |
+-------------------------------+
| 2011-05-03 14:53:34           |
+-------------------------------+
1 row in set (0.00 sec)

(I choose the function now() to illustrate the point, you would replace now() with the name of the specific column.). Then, if it is overdue, you let the new reservation override the previous one.

Answer 3

There are several possible approaches. Which you choose depends on system considerations:

• Schedule a job to run periodically, perhaps once per minute, hour, or day which executes periodic cleanup functions.
• Leave the records, but when dealing with them, the query contains qualification logic to rule out expired records. The expired records remain there for auditing and historical purposes.