View Full Version : Get number of days from date field and today

09-13-2006, 04:03 PM
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

09-13-2006, 04:24 PM
Try something like

SELECT DATEDIFF(ExpireDate,NOW()) as daysLeft FROM tablename

09-13-2006, 04:39 PM
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:

$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...


09-13-2006, 05:04 PM
I think this is likely your problem

$row = @mysql_num_rows($result);

should be

$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 ;)

09-13-2006, 05:06 PM
See? I knew it was something stupid. I just got through working on a for loop while waiting on a response and had

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

THank you again.

09-13-2006, 05:12 PM
My pleasure.

09-13-2006, 07:41 PM
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().

09-13-2006, 08:12 PM
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:

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