Automatic deletion of db content after 3 years

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)?

Steven

what happened when you tested it? :wink:

I think it is a correct SQL statement but as i mentioned i need ids to delete related records in other tables.

no, because by then they will have a valid last_visit value

…only if they have visited the site. If they haven’t visited the site, then the last_visit will still be null.

DELETE FROM
tbl_user
WHERE
DATEDIFF(now(),last_visit) > 1095

is this correct?

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

no, absolutely not

NULL is NULL, NULL is not equal to anything, not even another NULL, and any calculations involving NULL will return NULL

a comparison like DATEDIFF(now(),last_visit) > 1095 will completely ignore any rows where the last_visit value is NULL

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?

no, because by then they will have a valid last_visit value