Hi wonder if you can help I have set up a database query to delete user records older than 3 years. There is a server cron job which loads this script every day and will delete a user record who has not visited the site in 3 years.
$sql =
"SELECT
user_id
FROM
tbl_user
WHERE
DATEDIFF(now(),last_visit) > 1095";
$result = mysql_query($sql);
while($row = mysql_fetch_array($result)) {
$id = $row['user_id'];
$sql =
"DELETE FROM
tbl_user
WHERE
user_id = '$id'"
}
The problem is I only introduced the last_visit field a few months back so many of the date entries are 0000-00-00. Anyway i can manage this so these entries are deleted by cron in three years (without having to manually go back in 3 years time and delete all entries with 0000-00-00)?
change the 0000-00-00 values to NULL and bob’s your uncle
and by the way, you don’t have to fetch the user ids with a SELECT and then delete them one at a time in a loop – you can delete them all at once with an IN list, or better yet, don’t fetch them at all, just add the date comparison to a DELETE statement
Thanks for the advice - i guess null just means 0 so the comparison will delete any record with a difference of todays date and 0 which is greater than 1095 days.
I think i need the id cos I have a number of other related records in other tables i need to delete - i just showed an annotated version of the code.
Thanks
Steven
ok thanks so all records marked with null will not be deleted automatically - i’ll have to do it manually after 3 years anyway? so whats the point of changing date from 0000:00:00 to null?