I have been working on some code to check the database for certificates that are nearing 2 years old and then send an email to the students letting them know that their certificate is about to expire.
My certificates are valid for 2 years, then the student has to log back in and and the class again to renew it. I want to have an email sent out 90 day and 30 day before the certificate expires reminding the student.
I have accomplished this using a custom cron job that runs the code below. I have also added to fields to the certificate_issues table so that I can keep track of when a reminder email is sent. I called the new fields "reminder1" and "reminder2".
The code below will check for certificates that have a "certdate" that is 21 months old or older and then send out an email to those people. I have removed some names and images for privacy reasons.
I'm very new to PHP and SQL so if anyone has any suggestions on how to improve the code please let me know!
START OF CODE
$firstreminder = "SELECT tbl_certificate_issues.id , tbl_certificate_issues.userid , tbl_certificate_issues.studentname , tbl_certificate_issues.classname , tbl_certificate_issues.certdate, tbl_user.email, tbl_user_info_data.data
FROM tbl_certificate_issues, tbl_user, tbl_user_info_data
WHERE DATE_ADD( FROM_UNIXTIME( `certdate` ) , INTERVAL 21
MONTH ) < DATE_FORMAT( NOW() , GET_FORMAT( DATETIME, 'ISO' ) )
AND `reminder1` = '0'
AND tbl_certificate_issues.userid=tbl_user.id
AND tbl_user.id=tbl_user_info_data.userid";
//Run query and send email for certifications that expire in 90 days or less
$r = mysql_query($firstreminder);
for ($count=0; $count <= mysql_numrows($r)-1; $count++) {
$certid = mysql_result($r,$count,"id");
$username = mysql_result($r,$count,"studentname");
$certname = mysql_result($r,$count,"classname");
$certdateunix = mysql_result($r,$count, "certdate");
$certnumber = mysql_result($r,$count,"data");
$certdate = strftime("%B %d %Y", $certdateunix);
//Calculate experation date by adding 2 years to certdate
$expdate = str_replace(' 0', ' ', strftime('%B %d, %Y',(strtotime("+2 years", $certdateunix))));
$to = "email@www.com";
$subject = "Your Certification Expires in 90 Days or Less!";
$message = "
<style type=\"text/css\">
<!--
.p_main {
font-family: Arial, Helvetica, sans-serif;
font-size: 14px;
margin-left: 12px;
margin-right: 12px;
}
#email_border {
border: 2px solid #057CB1;
position: relative;
height: auto;
width: 760px;
margin-right: auto;
margin-left: auto;
}
.blue {
font-family: Arial, Helvetica, sans-serif;
font-size: 14px;
font-weight: 700;
color: #0000FF;
}
-->
</style>
<body>
<div id=\"email_border\">
<p><a href=\"http://www.com\"><img src=\"
\" alt=\"Site\" width=\"750\" height=\"133\" border=\"0\" /></a></p><p class=\"p_main\">Hello $username,</p>
<p class=\"p_main\">You are receiving this email because our records indicate that your <strong>$certname</strong> certification expires in the next 90 days.</p>
<p class=\"p_main\"><strong>Certification Date:</strong> $certdate</p>
<p class=\"p_main\"><strong>Expiration Date:</strong> $expdate</p>
<p class=\"p_main\">Please login to <a href=\"http://www.com\">Site</a> at http://www.com to sign-up for a class and renew your certification. It is important to keep your certification current and learn of changes that have been made since you received your certification almost 2 years ago!</p>
<p class=\"p_main\">If you have any questions or concerns about your certification or any FFS product please contact us toll free at 1-800-555-5555 or +1-555-555-5555.</p>
<p class=\"p_main\"><strong>Your certification number is:</strong><span class=\"blue\"> $certnumber</span></p>
<p class=\"p_main\">Thank you for your support of our products!</p>
<p class=\"p_main\"><strong>Technical Service</strong><br />
http://www.com<br />
Toll Free 1-555-555-5555<br />
International +1-555-555-5555</p>
</div>
</body>
</html>";
$headers = 'From: noreply@www.com' . "\r\n" .
'Content-type: text/html; charset=iso-8859-1rn' .
'X-Mailer: PHP/' . phpversion();
mail($to, $subject, $message, $headers);
//Put time stamp in reminder1 field to mark that a 90 day reminder was sent and when.
execute_sql("UPDATE {$CFG->prefix}certificate_issues
SET reminder1 = UNIX_TIMESTAMP(NOW())
WHERE id = $certid");
}
