CODEWITHSUNDEEP
phpmysql
YathuGulan
YathuGulan

Reputation: 111

Auto delete a record in table when date is expired?

I would like to know how can I auto delete a record when date is expired, I'm creating a Air-ticket booking website. I need to delete from my mysql database all the expired flight details. I read somewhere that I can use cron but I have no idea how to do it. Any Help for the script will be very helpful.

Upvotes: 10

Views: 21668

Answers (6)

BlitZ
BlitZ

Reputation: 12168

You may try to use MySQL Events for that:

CREATE EVENT IF NOT EXISTS `dbName`.`eventName`
ON SCHEDULE
    EVERY 1 DAY -- or 1 HOUR
COMMENT 'Description'
DO
    BEGIN
    
    DELETE FROM `dbName`.`TableName` WHERE `DateCol` < NOW();
    
    END

NOTE that MySQL Event Scheduler need to be enabled on your server:

SET GLOBAL event_scheduler = ON;

More info here.

Upvotes: 13

Abdullah
Abdullah

Reputation: 1

If your table currently only has 2 fields, you'll need to add a timestamp field (with default as CURRENT_TIMESTAMP) to be able to tell when the row was added.

Then you can run the following MySQL query...

DELETE FROM `table` WHERE `timestamp` &gt; DATE_SUB(NOW(), INTERVAL 10 MINUTE)

; You will need to run this as a cron though.

As far as I know, it can't be done in MySQL alone.

Upvotes: 0

Shankar Narayana Damodaran
Shankar Narayana Damodaran

Reputation: 68536

Make use of CRON Jobs. Have a look at the below representation.

Minutes [0-59]  
|   Hours [0-23]  
|   |   Days [1-31]  
|   |   |   Months [1-12]  
|   |   |   |   Days of the Week [Numeric, 0-6]  
|   |   |   |   |  
*   *   *   *   * home/path/to/command/the_command.sh

If you want to schedule a task to run every Saturday at 8:30am , it would most probably look like this.

30 8 * * 6 home/path/to/command/the_command.sh

Just play around with your settings and you will eventually get it.

Upvotes: 0

Nabil Kadimi
Nabil Kadimi

Reputation: 10414

Cron is most suited in your situation.

  • Create your php file that deletes or flag the unwanted flight, the script will simply connect to the database, execute your query, and maybe output a short success/failure message.
  • Setup the cron job that executes that php script every X hours or whatever you want.

If you are using CPanel, you can setup your cron job from it

Upvotes: 0

January Mmako
January Mmako

Reputation: 330

Deleting records is not a good idea, I will suggest you Add ActiveFrom and ActiveTo DateTime Column in your Details table then do not display the expired records in your front end.

Upvotes: 1

Onur Gazioğlu
Onur Gazioğlu

Reputation: 511

You need the service to check expired records and delete periodically. You should use this SQL:

DELETE YourTable WHERE RecordDate < @Expiration

Upvotes: 0

Related Questions