Auto delete Expired Data Row in MySQL

Question

I have added some bonus code rows into my bonusdetails table in the database. All bonus codes have an expiry date.

Is it possible to automatically delete the row that its expiry date has reached by php? Code I'm using is(Validity is date):-

$query = "select * 
          from bonusdetails 
          where BonusType='Match Bonus' 
          order by Validity ASC limit 0,30;";

$result = mysql_query($query);

echo '<table>';
.....
.....
.....
echo '</table>';

?>

Answer 1

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 `expireDateCol` < NOW();

END

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

SET GLOBAL event_scheduler = ON;

Answer 2

This query will delete rows where date in Validity is past.

$query = "DELETE FROM bonusdetails 
          WHERE BonusType = 'Match Bonus' 
          AND Validity < '".date('Y-m-d', time())."'";

If Validity is DATETIME :

$query = "DELETE FROM bonusdetails 
          WHERE BonusType = 'Match Bonus' 
          AND Validity < '".date('Y-m-d H:i:s', time())."'";

After :

• You can call this query in your code (but this will be call everytime so this isn't the best solution)
• Make a cron to call this once a day (better)

Answer 3

  $conn = new mysqli($servername,$username,$password,$dbname);
  $query = "DELETE FROM Urls WHERE created_at < now()";
  $conn->query($query);

this might be helpful

Answer 4

You can create Trigger that will delete your record every time someone update your data.

DELIMITER $$
Create Trigger trigger_name
  AFTER UPDATE ON bonusdetails
  FOR EACH ROW BEGIN

  DELETE bonusdetails
  WHERE Validity > Now();

END$$ 
DELIMITER ;