SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Addict
    Join Date
    Aug 2005
    Posts
    254
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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.

    Code:
    $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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Aug 2005
    Posts
    254
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Steve100 View Post
    i guess null just means 0
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Aug 2005
    Posts
    254
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Steve100 View Post
    ok i'll have to do it manually after 3 years anyway?
    no, because by then they will have a valid last_visit value
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast
    Join Date
    Jul 2011
    Location
    South Africa
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  8. #8
    SitePoint Enthusiast
    Join Date
    Jul 2011
    Location
    South Africa
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    chjust add the date comparison to a DELETE statement
    DELETE FROM
    tbl_user
    WHERE
    DATEDIFF(now(),last_visit) > 1095

    is this correct?

  9. #9
    SitePoint Addict
    Join Date
    Aug 2005
    Posts
    254
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think it is a correct SQL statement but as i mentioned i need ids to delete related records in other tables.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by ulricht609 View Post
    is this correct?
    what happened when you tested it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •