CODEWITHSUNDEEP
phpmysqlsql
user2077650
user2077650

Reputation: 61

Auto delete Expired Data Row in MySQL

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>';

?>

Upvotes: 4

Views: 16985

Answers (4)

Rajon Kobir
Rajon Kobir

Reputation: 173

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

this might be helpful

Upvotes: 1

fdehanne
fdehanne

Reputation: 1718

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)

Upvotes: 1

Charlesliam
Charlesliam

Reputation: 1313

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 ;

Upvotes: 0

Ezhil
Ezhil

Reputation: 1006

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;

Upvotes: 10

Related Questions