SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2004
    Location
    romania
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    notify before expiration date

    hello,
    pls help with the folloeing.
    I have a table
    [users]
    id
    user_name
    date_added
    exp_date


    the problem is like this:
    let's say I add a new user and he's expiration date is 1 year after the day he was added.
    In notify.php i want to display the users that there expiration date is in less that 7 days from today's date, because I want to be able to contact them and tell them that they should log in again or something like this.

    I know how to display the users in notify.php that have the today() date equal with the expiration date but this doesn't makes me happy.

    this should be something like this:
    select * from users where ((exp_date - date()) < 7).

    I know i didn't explain my self to clear, but i hope you understand.
    thx.

  2. #2
    SitePoint Addict phpster's Avatar
    Join Date
    Feb 2005
    Location
    Toronto, Canada
    Posts
    374
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How do you store the date? if a true date then use mysql's date_diff function to calc the difference.
    phpster

    I wish my computer would do what I want it to.
    Not what I tell it to do...

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2004
    Location
    romania
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the date_exp is stored as date.
    i know, about the datediff, but the problem is that the client has an older version of mysql and that function wasn't added.

    insted i tried using
    select * from users where date_sub(curdate(), interval 7 day) < exp_date

    the problem here is that it shows me the users that have the expiration date in 2009 insted of showing me only users that are in the 7 day interval "+" "-"

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    plus or minus? or just plus?

    for example, today is 2003-03-16, do you want all exp_date values between 2003-03-16 and 2003-03-23, or between 2003-03-09 and 2003-03-23?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict phpster's Avatar
    Join Date
    Feb 2005
    Location
    Toronto, Canada
    Posts
    374
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    try

    select * from users where date_sub(exp_date, interval 7 day) < currdate()
    phpster

    I wish my computer would do what I want it to.
    Not what I tell it to do...

  6. #6
    SitePoint Enthusiast
    Join Date
    Jul 2004
    Location
    romania
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    minus
    i'm interested to display dates that are 0 to 7 days until epiration date.

    i tried to use some of the mysql functions but they just don't do the job for me. Or maybe (probably) i don't know.

    here is what i did and is working so far.
    pls tell me if u have a better option or do you think i might have some troubles.

    SELECT * FROM users WHERE ((data_exp - CURDATE()) <= '7');
    this works (so far)

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    i'm pretty sure you mean plus
    Code:
    ...
     where date_exp 
           between current_date 
               and dateadd(current_date, interval 7 day)
    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
  •