Subtract/Add to date to get a usable variable

[lptl]

I know the title is misleading. I am working on a class project where we are trying to setup a notification system written in PHP and using an MySQL database. We are trying to set-up a notification page that will show what users are nearing expiration. The expiration dates are entered in manually and are not a timestamp. The dates are entered in on another page into the SQL database. Then what we need is to be able to take those dates and find that if they are near (lets say) 7 months to the date entered then it displays the users name that is about to expire.

I have tried different combinations of date(), strtotime(), date(mktime()), dateadd(), strftime(), etc.... and have been unsuccessful. All of those functions work if the date is physically a part of the code or off of the current time. But for some reason I cannot get it to subtract from a date in the database without it returning 12-31-1970.

Any and all help is greatly appreciated and thanks in advance.

(also sorry for the long post, just wanted to be thorough)

[ronverdonk]

Have you tried to do it from the SQL (this is using MySQL) side? I don't know what db you are using but you can select data from the db calculating the difference between the user-entered-date and the sql-table-date. Something like

$user_date=...whatever the date entered
SELECT * FROM table WHERE DIFFDATE($user_date, sql_date) < 100

Ronald

[lptl]

Thank you for the quick response!
I'm trying to run the query inside MySQL but I'm having trouble. I tried using other numbers (assuming that 100 = 1 month if I read correctly) and the opposite sign > and it keeps returning:

MySQL returned an empty result set (i.e. zero rows). (Query took 0.0010 sec)

when I try to run it using phpMyadmin. There is 5 rows of user data with dates in the column i'm trying to use. Any ideas?

Kyle

*Update
also here is my server stats:

Apache version 2.0.61
PHP version 5.2.5
MySQL version 4.1.22-standard

[ronverdonk]

Then show your code (relevant PHP and MySQL) (within code tags) and we know what we are talking about.

Ronald

[lptl]

I think this is what you asked for. Within PHP this is how I call to my db and pull data to display on the web:

<?php include '../config.php'; 
$query = "SELECT user_id, userFirstName, userMiddleInitial, userLastName FROM users ORDER BY userLastName"; 
$result = mysql_query($query) or die('Error, select query failed');				
$row = mysql_fetch_array($result, MYSQL_ASSOC)
?>

Then when displaying I have it running through a loop to display all users in the table formatted like:

print "{$row["userLastName"]}, {$row["userFirstName"]} {$row["userMiddleInitial"]}";

For the date that needs to be subtracted from, I tried using your suggestion: (but returns 0 results)

$query = "SELECT *, DATE_FORMAT(userDateExpire, '%m-%d-%Y') AS userDateExpire FROM users WHERE DATEDIFF('userDateExpire','sql_date') < 700 ORDER BY userLastName";

I have used the following example but had no success either: (also tried various forms of the styles noted in my first post)

$today = date('m-d-Y', mktime(0, 0, 0, date("m")-7, date("d"), date("Y")));

I know this takes today's date and will subtract 7 months but when I have tried to use my date, 'userDateExpire ' in place of 'm', 'd', 'Y' in mktime(), it always returns 12-31-1970 or 01-31-1969. I know that this date is the UTS but unsure of how it calls that instead of the values within my table?

I think this is everything that you had asked to see. Thank you again for your continuing help.

Kyle

[ronverdonk]

Let's hold it a sec! The way MySQL does date calculations is ALWAYS on the YYYY-MM-DD format. So, in whatever format your date is, you must have it (or convert it) to the YYYY-MM-DD format before you do the calculations!

So userDateExpire (in your table having data type DATE or DATETIME) is already in that format.

The date that the user passes must be converted to the YYYY-MM-DD format (using DATE_FORMAT) before doing the DATE_DIFF calculation.

Recap: when the 'userDateExpire ' field in your db has format YYYY-MM-DD.
When the user-entered date (assume in php variable $userdate) has format DD-MM-YYYY, you must reformat it to YYYY-MM-DD.
Then you SQL statement would be .e.g

$query = "SELECT * FROM users 
          WHERE DATEDIFF(userDateExpire,DATE_FORMAT('$userdate', '%d-%m-%Y')) < 700 
		  ORDER BY userLastName";

Ronald

[lptl]

Yep, I understand the SQL stores the date in YYYY-MM-DD format. The userDateExpire is the data type DATE with default value of 0000-00-00. Dates manually entered into the database in the userDateExpire column are entered in the format YYYY-MM-DD.

$query = "SELECT *, DATE_FORMAT(userDateExpire, '%m-%d-%Y') AS userDateExpire FROM users WHERE DATEDIFF('userDateExpire','sql_date') < 700 ORDER BY userLastName";

In the above code, the DATE_FORMAT is being used to convert the data for display only. I researched into DATEDIFF() a little bit more and came up with the following:

SELECT *, DATE_FORMAT(userDateExpire, '%m-%d-%Y') AS userDateExpire FROM users WHERE DATEDIFF(userDateExpire, CURDATE()) <= 213 ORDER BY userLastName

I also found that the number in the WHERE statement, ex. 700, is the number of days not 7 months. 7 months is equal to 213 days (give or take a few decimal points).

This works and displays my data correctly. I also found that when using column names in the DATEDIFF() function there should not be quotes around the column name(s). I think that was my biggest problem when I was running the query.

Thank you again for all of your help!

Kyle