I have added some rows into my ad table in the database, their properties includes created date and expiry date.
I need php script to automatically delete the row that its expiry date has reached.
Pls assist my project. Thanks u
3 Answers
You could use MySQL's event scheduler to automatically delete such records when they expire:
CREATE EVENT delete_expired_101
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 3 WEEK DO
DELETE FROM my_table WHERE id = 101;
Or to run an automatic purge of all expired records on a regular basis:
CREATE EVENT delete_all_expired
ON SCHEDULE EVERY HOUR DO
DELETE FROM my_table WHERE expiry < NOW();
1 Comment
Frankie
The event schedule must be available on virtual host or web hosting
<?php
$today = getdate();
if($today <= $expirayDate)
{
//your deletion logic
}
?>
The other way would be to filter your select query i-e hide all those fields which aren't expire;
$query = "SELECT * FROM `yourTable` WHERE `expiration` >= NOW();";
Comments
What you are looking for is crontab.
Using it, you will be able to schedule scripts to run in determinate times, for example:
Run deleteExpiredRows.php every 5 minutes
Run updateOnlineUsers.php every 2 hours
Cron is very easy to use. Check this quick reference guide
Setting up crontabs at your web hosting:
/path/to/hosting/php /home/user/public_html/script.php
Path to php may vary, so contact your hosting to get this information. More information about crontab can be found here at stackoverflow.
Note: you might want to check if your web hosting service supports crontab.
After this is done, you just need to create your PHP script to delete every row older than current time.
