We’ve all come across this issue at some point or another. We have time-sensitive records that expire at a certain point in time; and we want to make sure that said rows no longer show on our website once that point in time has been reached. Examples include classified adverts, job listings or special offers that have a specified end date.
Most people make the mistake of thinking that some sort of cron job is required. i.e. A scheduled task is run at set points and expired records are removed from the database. To me, this doesn’t really make sense, as there are a lot of scenarios where you won’t want to physically remove the rows in question. For example: A user lets their classified advert expire – However, after two or three days, they return to your website to activate it again. If I force them to re-enter all of their details, they might lose patience; thereby costing me a sale.
A better approach is to set an expiry DATE / DATETIME column and then use that column in your SELECT statements. That way, you don’t have to worry about manually expiring rows or deleting MySQL records. That way, you let the SELECT query exclude the expired rows from being returned.
An example with PHP and MySQL:
$sql = "SELECT id, title FROM adverts WHERE expiry_date > CURDATE()"; $stmt = $pdo->prepare($sql); $stmt->execute();
The SQL query above will only select rows from MySQL that have not expired. That way; you don’t have to worry about cron jobs or scheduled tasks (you could run these every now and again, just to free up table space).
Hopefully, this offered you an alternative solution!
Related Reading: MySQL dates.