Using PHP/MySQL for Email Reminders

[chris.cavage]

Using php/mysql

This is tricky! I am setting up a CRON job to run a php file daily.

I have a record for customer information. The contacts table has the info for them with fields like:

Patient_ID (primary key, autoincremented),
First_Name, 
Last_Name, 
Phone, 
Appt_Reminder_Email (set to Y or N if they DO or DO NOT want to get appt email reminders), 
Email_Reminder_Time (integer that expresses how many MONTHS they want to be reminded from their last scheduled appointment), 
Last_Appt_Reminder_Email (when the last reminder email was sent)

I am trying to design a query that will email each customer automatically if they haven't had an appointment scheduled - based on how many MONTHs they want to be reminded (set by contacts.Email_Reminder_Time). It would be a ONE TIME email reminder. If they schedule an appointment, then the reminder email would be later sent again based on how many months they want to be reminded.

I have an appts table called events_rec which stands for recurring events. It does have a foreign key for the contacts (events_rec.Patient_ID).

After the email is sent to remind each contact, I will UPDATE the contacts.Last_Appt_Reminder_Email DATE field to the current date in order to weed out those who were reminded once. I thought that was a good idea?

I can set up the CRON easily, and I am using Zend Mail to send the emails, but how can I get a query that does this?

I put this together to test:

SELECT * FROM
(SELECT * FROM 
(SELECT events_rec.Company_ID AS Company, contacts.Patient_ID, contacts.First, contacts.Last, contacts.Email, offices.Office, contacts.Email_Reminder_Time,
MAX(start_date) AS LastDate, COALESCE(contacts.Last_Appt_Reminder_Email, 0) AS LastEmail, contacts.Appt_Reminder_Email, contacts.Active
FROM events_rec
INNER JOIN contacts ON contacts.Patient_ID = events_rec.Patient_ID
INNER JOIN offices ON offices.Office_ID = contacts.Office_ID
GROUP BY events_rec.Patient_ID) t1
WHERE t1.LastDate < DATE_ADD(curdate(), INTERVAL -1 MONTH)
AND t1.LastEmail < DATE_ADD(curdate(), INTERVAL -1 MONTH)
AND t1.Email_Reminder_Time = 1
AND t1.Email LIKE '&#37;@%'
AND t1.Company= '1' 
AND t1.Appt_Reminder_Email = 'Y'
AND t1.Active = 'Y') t2

This works just fine for people who have the Email_Reminder_Time = 1 (month). So, how do I get it to work for all integer values set by contacts.Email_Reminder_Time ? For people who want to be reminded every 2, 3, 4, 6, 12, 24 months, for example.

So I need it to sweep through all Email_Reminder_Time values and then send the email if needed.

Here's some simplified php from the file that will be run by the CRON. It is a start as it will connect to the db, find the people to email (based on ONE MONTH though as an example), email them, and update the last time they were emailed. However, it doesn't include any other Email_Reminder_Time values which exist in that contacts field.

mysql_select_db($database_connectCavage, $connectCavage);
$query_contacts = "QUERY?";

$contacts = mysql_query($query_contacts, $connectCavage) or die(mysql_error());
$totalRows = mysql_num_rows($contacts);

$emailBody = "";

while($row_contacts = mysql_fetch_assoc($contacts))
{
    $config = array('auth' => 'login', 'port' => 25, 'username'=> '[email protected]', 'password'=>'password');	
	$transport = new Zend_Mail_Transport_Smtp('[email protected]', $config);
	
    $arr_ids[] = $row_contacts['Patient_ID'];
	
	*************** MY ZEND EMAIL BODY, SUBJECT, AND OTHER EMAIL INFO GOES HERE*****************
  
    $mail = new Zend_Mail();  
	
    $mail->setBodyHTML($body);
    $mail->setFrom($fromEmail, $fromName);
    $mail->addTo($toEmail, $toName);
    $mail->setSubject($subject);
	
    try {
        $mail->send();
    }
    catch(Exception $ex) {
        Mage::getSingleton('core/session')
            ->addError(Mage::helper('yourmodule')
            ->__('Unable to send email.'));
    }
}

if (isset($arr_ids)){
$sql = "UPDATE contacts SET Last_Appt_Reminder_Email = NOW() WHERE Patient_ID IN (";
$sql .= implode("," , $arr_ids);
$sql .= ");";

mysql_query($sql);
}

Hope I did a good job explaining. Thanks.