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;