Here is the run down:
Our rowing team is fundraising for race sponsors and we have a website for volunteers to coordinate the solictation of sponsors...made out of php nuke of course. Part of this has a separate database of 'prospects' and profiles of contact information. They're assigned to volunteers which we solicit. The whole point is to have us organized and we don't visit the same people twice and we can all monitor each other progress etc.
We wanted in profiles to setup follow up email reminders to the assigned volunteer if they put in a follow up date in the prospect profile. For example, If i have Coca Cola assigned to me, i pull up that profile, and under follow up I pick the date and hit save. On that date I get an email with a message to myself reminding me to follow up with them.
The script is almsot working. It sends emails, and puts in the email information that needs to show up.
What isn't working: dates. It isn't sending the right reminders on the right dates. I think I am not parsing dates correctly - or comparing apples to apples in my sql statement. For example, dates are entered into the database as MM/DD/YYYY and then a variable in the script parses it into a variable as YYYYMMDD (or whatever sql needs) then, supposdely compares them.
In the sql statement below, if have = in it -- even if I have a reminder on that day, I don't get an email.
If i have a <= I get an email with ALL prospects listed in it, regardless of whether a follow update was entered for them. The prospects where there is no follow up date, they all show up as December 31, 1969 -- for some odd reason... and the ones I entered, show up correctly.
PLEASE SOMEONE SMART HELP!!!!!!
here is the code:
<?php
//$number_of_days_before = 1;
//e.g. if the value set to 1, it will enable the reminder to be sent 1 day before the event occurs. Similarly the value set to 0 will send the reminders on the same day of the event.
//2. Destination Email address where the reminder mail has to be sent
//$email = "youremail@yourserver.com";
//The following code will send out all reminders bulked in one mail to avoid unnecessary multiple copies for each reminder. After sending out the mail with all reminder details for a given day, those very reminder entries will have to be deleted from the database to protect from duplicate processing the next day.
//include('database.inc.php'); // Our database connectivity file
include_once("../../common/dbConnection.php");
?>
<?
// Values you need set
$number_of_days_before = 0;
$email = "myemail@myemail.com";
$reminder_details = "";
$todays_date = date( "Ymd" );
$year = substr($todays_date, 0, 4);
$month = substr($todays_date, 5, 2);
$date = substr($todays_date, 7, 2);
$trigger_date = date("Ymd", mktime (0,0,0,$month,$date-$number_of_days_before,$year));
///justin code
$result3 = mysql_query( "SELECT FollowUpDate FROM chrsponsors" );
$nr3 = mysql_num_rows($result3);
while( $row3 = mysql_fetch_array( $result3 ) )
{
$year3 = substr($row3["FollowUpDate"], 7, 4);
$month3 = substr($row3["FollowUpDate"], 0, 2);
$date3 = substr($row3["FollowUpDate"], 4, 2);
$reminder_date3 = date("Ymd", mktime (0,0,0,$month3,$date3,$year3));
}
///
$result = mysql_query( "SELECT * FROM chrsponsors WHERE $reminder_date3 = $trigger_date ORDER BY FollowUpDate ASC" );
$nr = mysql_num_rows($result);
while( $row = mysql_fetch_array( $result ) )
{
$year = substr($row["FollowUpDate"], 7, 4);
$month = substr($row["FollowUpDate"], 0, 2);
$date = substr($row["FollowUpDate"], 3, 2);
$reminder_date = date("M j, Y", mktime (0,0,0,$month,$date,$year));
$reminder_details .= "Event: ".$row["Business_Name"]."\n";
$reminder_details .= "Date: ".$reminder_date."\n";
$reminder_details .= $row["FollowUpMessage"]."\n\n";
}
mysql_free_result( $result );
if( !empty( $nr ) )
{
// Send out Reminder mail
$mailheader = "From: Reminder System <$email>\nX-Mailer: Reminder\nContent-Type: text/plain";
mail("$email","Reminder","$reminder_details","$mailheader");
// Delete the sent reminders
//mysql_query("DELETE FROM reminder_events WHERE reminder_date <= $trigger_date" );
}
?>
