Happy Monday–
I’m really having a hard time getting my brain wrapped around writing loops to rotate through a result to group data together.
I have a table in my database (‘calendar’) that contains the following fields:
calendar_ID (INT)
calendar_date (DATE)
calendar_reminder (DATE)
calendar_event (VARCHAR)
calendar_assignee (TEXT)
Here is my query (not using actual PHP, but just for you to read):
$now = date("Y-m-d");
SELECT calendar_event, calendar_date FROM calendar
WHERE (calendar_assignee = 'Bob' OR calendar_assignee = 'Both')
AND calendar_reminder = '$now'
ORDER BY calendar_dateNow, this query will return all of the rows with a reminder date of today, ordered by the actual date they are due (calendar_date). These due dates will be an exact interval from today’s date, based on how I am entering them into the database. At any given time, there will be a maximum of 4 different groups being shown (1 day, 3 days, 1 week, 2 weeks).
I am wanting to create an email that contains these results grouped by their due date. I’d like the email to look something like:
================
Hello Bob,
Here are the tasks that are due soon:
3 DAYS (2011-01-27)
- Buy groceries
1 WEEK (2011-01-31)
- Pay electric bill
================
Note that since there are no tasks due 1 day or 2 weeks from today that those groups should not show.
I’ve made a couple of stabs at this, but can’t seem to get it to work. In one of my attempts, I wrote some queries to create a variable for the calendar_date to be matched to, but I’m not sure if these are necessary:
[php]
//SET ‘1 DAY’ VARIABLE
$date = mysql_query(“SELECT DATE_ADD(’$now’, INTERVAL 1 DAY)”);
$row = mysql_fetch_row($date);
$calendar_date_1day = $row[‘0’];
//SET ‘3 DAY’ VARIABLE
$date = mysql_query(“SELECT DATE_ADD(’$now’, INTERVAL 3 DAY)”);
$row = mysql_fetch_row($date);
$calendar_date_3day = $row[‘0’];
//SET ‘1 WEEK’ VARIABLE
$date = mysql_query(“SELECT DATE_ADD(’$now’, INTERVAL 1 WEEK)”);
$row = mysql_fetch_row($date);
$calendar_date_1wk = $row[‘0’];
//SET ‘2 WEEK’ VARIABLE
$date = mysql_query(“SELECT DATE_ADD(’$now’, INTERVAL 2 WEEK)”);
$row = mysql_fetch_row($date);
$calendar_date_2wk = $row[‘0’];
[/php]
Can anyone point me in the best direction? I’m really wanting to learn, as I see many uses for this. Thank you!