I have a mysql table with unique records, date and email information. I want to automatically send a 'reminder' email to the email address given for each record that the renewal date is coming up. And in case the select condition applies to multiple records and the email address matches, send a list of those records in one email to the matching email address. So the table looks e.g. like this
dataID | record_name | accountID | accountName | email | renewal_date | notification_period
1 | Record1 | 1 | Client A | Aclient@mail.com | 2016-12-30 | 10
2 | Record2 | 2 | Client B | Bclient@mail.com | 2017-01-25 | 10
3 | Record3 | 1 | Client A | Aclient@mail.com | 2016-12-30 | 10
4 | Record4 | 1 | Client A | Aclient@mail.com | 2016-01-18 | 14
5 | Record5 | 3 | Client C | Cclient@mail.com | 2016-12-30 | 10
6 | Record6 | 1 | Client A | Aclient@mail.com | 2016-12-30 | 10
I've set up a cronjob (once a day) and I'm using PHPMailer, which both works. How can I now dynamically loop through the table in my template (mail.html) and fill the column with the appropriate information and send it to the desired recepient? Here is what I have so far:
mail.php
<?php
$mysqli = new mysqli("host", "database", "password", "table");
$query = "SELECT dataID, record_name, account_name, email, renewal_date, notification_period
FROM accounts_mail
WHERE email != ''
AND renewal_date = DATEADD(day,notification_period,CURDATE())";
$select = mysqli_query($mysqli, $query);
$i=0;
while($rs = mysqli_fetch_assoc($select)){
$data[$i] = array('dataID'=>$rs['dataID'], 'record_name'=>$rs['record_name'], 'account_name'=>$rs['account_name'], 'email'=> $rs['email'], 'renewal_date'=> $rs['renewal_date'], 'notification_period'=> $rs['notification_period']);
$i++;
}
//echo $data;
require('../PHPMailerAutoload.php');
$time = time();
$message = file_get_contents('mail.html');
$message = str_replace('%currentTime%', $time, $message);
$mail = new PHPMailer();
$mail->SetFrom('mymail@address.com', 'mymail@address.com');
$mail->AddAddress('clientmail@address.com', 'client name');
$mail->Sender = 'mymail@address.com';
$mail->Subject = 'Some Subject';
$mail->MsgHTML($message);
$mail->IsHTML(true);
$mail->CharSet = 'utf-8';
?>
mail.thml
<!DOCTYPE html>
<html>
<head>
<title>Titel & %currentTime%</title>
...
<style type="text/css">
...
</style>
</head>
<body>
<!-- HEADER -->
<table border="0" cellpadding="0" cellspacing="0" width="100%">
...
<tr>
<td bgcolor="#ffffff" align="center" style="padding: 15px;" class="padding">
<table border="0" cellpadding="0" cellspacing="0" width="800" class="responsive-table">
<tr>
<td>
<!-- TWO COLUMNS -->
<table cellspacing="0" cellpadding="0" border="0" width="100%">
<tr>
<td valign="top" style="padding: 0;" class="mobile-wrapper">
<!-- LEFT COLUMN -->
<table cellpadding="0" cellspacing="0" border="0" width="47%" style="width: 47%;" align="left">
<tr>
<td style="padding: 0 0 10px 0;">
<table cellpadding="0" cellspacing="0" border="0" width="100%">
<tr>
<td align="left" style="font-family: Arial, sans-serif; color: #333333; font-size: 16px;">Record Name</td>
</tr>
</table>
</td>
</tr>
</table>
<!-- RIGHT COLUMN -->
<table cellpadding="0" cellspacing="0" border="0" width="47%" style="width: 47%;" align="right">
<tr>
<td style="padding: 0 0 10px 0;">
<table cellpadding="0" cellspacing="0" border="0" width="100%">
<tr>
<td align="right" style="font-family: Arial, sans-serif; color: #333333; font-size: 16px;">Account Name</td>
</tr>
</table>
</td>
</tr>
</table>
</td>
</tr>
</table>
</td>
</tr>
</table>
</td>
</tr>
</table>
</body>
</html>