Another date compare question

Hi Jerry,

Date math is often kind of tricky. So this is an advanced topic.

When you have time, I'd recommend installing the free "Komodo Edit" software and getting it configured.
http://www.activestate.com/komodo_edit/

It has a helpful feature where it puts a red line under any line that has coding errors. Saves us a ton of time even if you just use it once and a while to detect code errors. Here's the problem with the code (missing ; after 7):

<?php
$days = (int) @$_REQUEST['days'];
if (!$days) { $days = 7; } // set default
$escapedDays = mysql_real_escape_string($days);
?>

For date queries, the first step is to figure out if you need to do it in MySQL or PHP. If you are able to do it in MySQL it's often simpler.

You can find a list of MySQL date/time functions here:
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

And here's some examples:
NOW() - Gives you the current date and time: 2010-03-01 09:41:50
(NOW() - INTERVAL 7 DAY) - Get date/time 7 days ago: 2010-02-22 09:45:25
CURDATE() - Gives you the current date only: 2010-03-01
TIMESTAMP() - Format value as date/time, or if two values, add them together
TIMESTAMP(CURDATE()) - Get date/time at beginning of day: 2010-03-01 00:00:00
TIMESTAMP(CURDATE(), '23:59:59') - Get date/time at end of day 2010-03-01 23:59:59

And you can test all these with the MySQL Console Plugin by entering SELECT followed by the function. So:
SELECT NOW() in the Mysql Console returns: 2010-03-01 09:41:50

So the first step is to figure out the values you want to compare. My guess is you want:
The date 7 days ago: (NOW() - INTERVAL 7 DAY)
The reception date: reception_date
The date at the end of today: TIMESTAMP(CURDATE(), '23:59:59')

If you write it out in English first it's way easier:
- If the reception_date is 7 days or less from now
- AND the reception_date hasn't already passed

I like to arrange my code so if reads like a time range with the test date in the middle like this:
start_date <= test_date AND test_date <= end_date

So that would be:
(NOW() - INTERVAL 7 DAY) <= reception_date AND reception_date <= TIMESTAMP(CURDATE(), '23:59:59')

Let me know if using that as your where works for you. It may be easier to just use another getRecords() query for those Upcoming Reminders rather than re-doing everything in PHP.

Hope that helps! Let me know any questions.