-1

I Could use some help, i want to auto run a script that deletes a record from my db after 48 working hours (excluding weekends) - some kind of timer if you will.

Tried using phpmyadmin triggers but there're not doing precisely what i want.

Additionally: On my db i've got a listed_date column - which stores the date and time the record was added and i also have a expiry_date column which stores a future date (when the record should expire/be deleted)

Any thoughts??

Sivuyile Shasha
  • 88
  • 2
  • 9

1 Answers1

1

If you want to run a PHP script, create a query in PHP:

//connect to mysql database, then run a query with the following logic

DELETE FROM `table` WHERE `expiry_date` <= NOW();

This script oculd be set up in crontab to run every n mins:

0 * * * * cd /script/directory; php script.php; > /dev/null

This will run on the hour, every hour.

Maninderpreet Singh
  • 2,569
  • 2
  • 17
  • 31
Liam C
  • 138
  • 10
  • Thanks a lot. The cronjob execution now works, however the logic on the sql is somehow confusing: i just want to delete records that are 48 hours old (and older) excluding weekends hours (i've bypassed the weeeknd part by executing the script during weekdays only) – Sivuyile Shasha May 03 '16 at 14:38
  • Glad to help. Your `expiry_date` column holds a datetime equivalent to the time the entry was added, plus 48 hours. `now()` function in `MySQL` returns the current datetime. In the query you are removing all entries that have an `expiry_date` value less than or equal to the current datetime. https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_now You could achieve the same effect by comparing your `listed_date` column with `NOW() - INTERVAL 48 HOUR`, thus removing entries dated earlier than 48 hours ago. Is this clear? – Liam C May 03 '16 at 14:46
  • This helped alot! Thanks again :-) Managing to do what i wanted by combining your sugetions with the solution given here http://stackoverflow.com/questions/336127/calculate-business-days – Sivuyile Shasha May 04 '16 at 12:01