Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    Regular Coder
    Join Date
    May 2005
    Posts
    262
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Get number of days from date field and today

    I have a date field called ExpireDate. I would like to query and get the number of days from today until an account will expire to display to the user. I am not sure how to accomplish this in a query. I thought about just querying for the date and doing the math with php functions but I know this can be done in a query.

    ExpireDate ('Y-m-d" format)

    Thank you for any help with this

  • #2
    Senior Coder NancyJ's Avatar
    Join Date
    Feb 2005
    Location
    Bradford, UK
    Posts
    3,174
    Thanks
    19
    Thanked 66 Times in 65 Posts
    Try something like
    Code:
    SELECT DATEDIFF(ExpireDate,NOW()) as daysLeft FROM tablename
    Last edited by NancyJ; 09-13-2006 at 04:27 PM. Reason: switched parameters round

  • #3
    Regular Coder
    Join Date
    May 2005
    Posts
    262
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Cool, Well I plugged that query into mysql and got -17 from today to the 30th of this month so I swapped the NOW() and the field in that function and it returned 17 so it works. But for some reason when I run this in my php page, I am not getting anything in return.

    here is my code:

    Code:
    $db = db_connect();
    
    $query	= "select DATEDIFF(ExpireDate, NOW()) as DaysLeft from user where UserID = ".$_SESSION["UserID"];	
    	
    	
    $result	= @mysql_query($query) or die(mysql_error());
    	
    	
    $row	= @mysql_num_rows($result);
    	
    echo "Days ".$row["DaysLeft"];
    Now if I echo the query and paste it into the shell and run it in mysql, it returns 17 but my echo here only shows "Days"

    I know I am doing something stupid here...

    thanks

  • #4
    Senior Coder NancyJ's Avatar
    Join Date
    Feb 2005
    Location
    Bradford, UK
    Posts
    3,174
    Thanks
    19
    Thanked 66 Times in 65 Posts
    I think this is likely your problem
    PHP Code:
    $row    = @mysql_num_rows($result); 
    should be
    PHP Code:
    $row = @mysql_fetch_assoc($result); 
    If you didnt have the @ before the function call you would get an error message telling you about your mistake since the optional parameter for mysql_num_rows() is the connection id not a mysql result resource. Only put @ in to supress after you've finished debugging and got it working

  • #5
    Regular Coder
    Join Date
    May 2005
    Posts
    262
    Thanks
    4
    Thanked 0 Times in 0 Posts
    See? I knew it was something stupid. I just got through working on a for loop while waiting on a response and had

    Code:
    for($i=0; $i<@mysql_num_rows($result); $i++)
    in my head. DOH!

    THank you again.

  • #6
    Senior Coder NancyJ's Avatar
    Join Date
    Feb 2005
    Location
    Bradford, UK
    Posts
    3,174
    Thanks
    19
    Thanked 66 Times in 65 Posts
    My pleasure.

  • #7
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    For others reading, keep in mind the function datediff() is for MySQL version 4.1 and greater. My host unfortunately is on 4.0 so I can't use datediff().

  • #8
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    that's why the sticky thread is at the top of the forum, it lets people know they need to mention their version of mysql if it is older than 4.1 then you get a solution that will work with your version:

    Code:
    $query	= "select 
    (to_days(expiredate) - to_days(now())) as DaysLeft
    from user 
    where UserID = ".$_SESSION["UserID"];


  •  

    Posting Permissions

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