SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict greg76's Avatar
    Join Date
    Aug 2004
    Location
    Los Feliz, CA
    Posts
    260
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    php mysql add 1 year to date then compare if that new date will come in 30 days

    Hello,

    I have been writing a small script that would suppose to check the expiration date 30 days prior to that date and send email reminder.

    I have a table with column named 'completed' meaning the date whene the course has been finished, completed. Each course is valid for 1 year.

    What I have been trying to do is first add one full year to 'completed' then check if that new date falls exactly 30 days prior to now().

    Example: the course was finished on 31 Dec 2012. It is valid until 31 Dec 2013, so if TODAY is 1 Dec 2013 email reminder should be sent.

    I have this little script:

    PHP Code:
    SELECT distinct
        userID
        
    completed+INTERVAL 1 YEAR AS expires
      FROM activity
    WHERE completed 
    NOW() + INTERVAL 30 DAY 
    but obviously it doesn't do the job...
    Could anyone point me into right direction?

    Thanks!
    Cheers,
    Greg

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    WHERE completed + INTERVAL 1 YEAR < CURRENT_DATE + INTERVAL 30 DAY

    i think
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,747
    Mentioned
    65 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by greg76 View Post
    What I have been trying to do is first add one full year to 'completed' then check if that new date falls exactly 30 days prior to now().

    Example: the course was finished on 31 Dec 2012. It is valid until 31 Dec 2013, so if TODAY is 1 Dec 2013 email reminder should be sent.
    Quote Originally Posted by r937 View Post
    WHERE completed + INTERVAL 1 YEAR < CURRENT_DATE + INTERVAL 30 DAY

    i think
    Exactly would make it WHERE completed + INTERVAL 1 YEAR = CURRENT_DATE + INTERVAL 30 DAY.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.


Tags for this Thread

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
  •