CODEWITHSUNDEEP
mysql
sirlouis
sirlouis

Reputation: 65

how to use PHP to automatically delete from mysql at set date

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

Upvotes: 3

Views: 7957

Answers (3)

Renato Massaro
Renato Massaro

Reputation: 554

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.

Upvotes: 1

eggyal
eggyal

Reputation: 126035

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();

Upvotes: 4

Idrees Khan
Idrees Khan

Reputation: 7752

<?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();";

Upvotes: 2

Related Questions