SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Guru adammc's Avatar
    Join Date
    Aug 2004
    Location
    Cairns, Australia
    Posts
    762
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Calculating date

    Hi Guys,

    I am using the following to store the date when a user registers on my site and determine the date / time 12 months ahead of the registered date.


    PHP Code:
    # Get the current server time
        
    $now time(); 

    # Determine the expiry date '365 days' 
        
    $days2expire 365;

    # timestamp of expiry date 
        
    $expirystamp strtotime('+' $days2expire ' days'$now); 

    # make it human readable 
        
    $expiry_date date("Y/m/d @ H:i:s"$expirystamp); //  example output 2008/08/08 @ 06:54:15
        
    $date_registered date("d-m-Y H:i:a"$expirystamp);  // example output 08-08-2008 06:54:am 

    I will need to write code to update a record in the DB to 'expired' if the expiry_date is past the current date.

    Am I best to store the date in the DB like so using ($expiry_date & $date_registered) ?
    PHP Code:
    date_registered DATETIME NOT NULL default '0000-00-00 00:00:00',
    date_12_months_ahead DATETIME NOT NULL default '0000-00-00 00:00:00'
    Or should I be using timestamps and a different type of table?


    Any advice would be greatly appreciated

  2. #2
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can't store $expiry_date in the DB in its current form because it's got an @ in the middle of it and doesn't really conform to any date/time format.

    I use the MySQL datetime format which looks like YYYY-MM-DD HH:MM:SS
    You should also store all your dates in the same format, not mixing and matching on how you think you want to display them.

    Format the dates when you display them, but store them in a consistent format.

    As for writing code to update the DB when accounts expire, wouldn't it be simpler to check the expiry date as part of the login process?
    So the account is valid if the username and password match, and the expiry date isn't in the past.

  3. #3
    SitePoint Guru adammc's Avatar
    Join Date
    Aug 2004
    Location
    Cairns, Australia
    Posts
    762
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks for the great advice

    If I used MYSQL to do the dates...
    INSERT INTO tablename (date_registered,date_expires) VALUES (NOW(),DATE_ADD(NOW(), INTERVAL 12 MONTH))

    How would I do that check during login to see if the expiry date has past?
    Last edited by adammc; Aug 8, 2007 at 15:32. Reason: typo

  4. #4
    SitePoint Guru adammc's Avatar
    Join Date
    Aug 2004
    Location
    Cairns, Australia
    Posts
    762
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SQL insert (date functions) didnt work All I got was the following:

    0000-00-00 00:00:00


    PHP Code:
        $query "INSERT INTO students (

        date_reg, 
        expire_date) VALUES (
        
        NOW()', 
        'DATE_ADD(NOW(), INTERVAL 12 MONTH')"
    ;
        
        
        
    $result = @mysql_query ($query); 
    Can anyone possibly help?

  5. #5
    Working on it... Contrid's Avatar
    Join Date
    Apr 2006
    Location
    Online
    Posts
    955
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Maybe...

    Code php:
    $now = time();
    $thisYear = date("Y-m-d H:i:s", $now);
    $nextYear = date("Y-m-d H:i:s", strtotime("+12 months"));
    And so I got lost in code...completely asphyxiated by it...

    Premium WordPress plugins - Tribulant Software

  6. #6
    Working on it... Contrid's Avatar
    Join Date
    Apr 2006
    Location
    Online
    Posts
    955
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    I always preferred to store dates/times in the database in Unix timestamp format, but I recently started working with some frameworks that do all the work for me. Automatically update 'modified' dates, etc...

    But even if you store it in the database this way, you can easily convert it back to a timestamp using strtotime();
    And so I got lost in code...completely asphyxiated by it...

    Premium WordPress plugins - Tribulant Software

  7. #7
    SitePoint Guru adammc's Avatar
    Join Date
    Aug 2004
    Location
    Cairns, Australia
    Posts
    762
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thank you sooooo much

  8. #8
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Get rid of the @ before your query and check out the mysql_error() function when a query doesn't work.
    It looks like yours has some misplaced single quotes, such as the one after NOW()'

    As for checking login the query might look like this (assuming username has been escaped and $password is a hash):
    Code SQL:
    SELECT * FROM `users` WHERE username = '$username' AND password='$password' AND dateExpires > NOW()

    If no records are returned the authentication details were wrong, or the account has expired. Alternatively you may want to check the expiry date in PHP so your error message can differentiate between a wrong username/password and an expired account.

  9. #9
    Working on it... Contrid's Avatar
    Join Date
    Apr 2006
    Location
    Online
    Posts
    955
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by adammc View Post
    thank you sooooo much
    Great. Glad to hear that sorted it!
    And so I got lost in code...completely asphyxiated by it...

    Premium WordPress plugins - Tribulant Software


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
  •