Hi all. I am trying to merge queries and send to user but been having a hard time figuring it out.
Here is my code. Thanks
<?php
##--------------------------##
## Prepare the mailer class ##
##--------------------------##
//$message = htmlspecialchars($mess);
$mail = new PHPMailer;
//$mail->SMTPDebug = 3; // Enable verbose debug output
$mail->isSMTP(); // Set mailer to use SMTP
$mail->Host = 'localhost'; // Specify main and backup SMTP servers
$mail->SMTPAuth = true; // Enable SMTP authentication
$mail->Username = '[email protected]'; // SMTP username
$mail->Password = 'pasword'; // SMTP password
$mail->SMTPSecure = 'ssl'; // Enable TLS encryption, `ssl` also accepted
$mail->Port = 465; // TCP port to connect to
$mail->From = '[email protected]';
$mail->FromName = 'Coy Name';
$mail->WordWrap = 587; // Set word wrap to 50 characters
$mail->AddEmbeddedImage("../img/logo.png", "logo");
$mail->isHTML(true); // Set email format to HTML
$mail->Subject = 'Notice';
##----------------------##
## Create and run query ##
##----------------------##
$bill_status = "OVERDUE";
$query = "SELECT username, trans_ref, due_date, service_provider,
service_type, amount_paid, bill_status, recurring,
DATEDIFF(NOW(), due_date) AS days_diff
FROM bills
JOIN login_details ON bills.user_id = login_details.user_id
WHERE bills.bill_status = :bill_status";
$st = $pdo->prepare($query);
$st->bindValue(':bill_status', $bill_status, PDO::PARAM_STR);
$st->execute();
##--------------------------------------------------##
## Loop through records and create structured array ##
##--------------------------------------------------##
while($row = $st->fetch(PDO::FETCH_ASSOC))
{
$records[$rows['username']][] = $row;
}
##-----------------------------------------------------##
## Iterate over records sending one email to each user ##
##-----------------------------------------------------##
foreach($records as $username => $userRecords)
{
//Create message header
$message = "
<div class='messages'>
<h3><img src='cid:my_logo'></h3><br>
<div style='font-size:15px;'>Email Notification on Bill Due for Payment
<p>Dear: Customer</p>
<p>The following bills are due for payment:</p>
<table width='80%' border='0' cellspacing='0' cellpadding='0'>
<tr style='font-weight:bold;'>
<td>Trans Ref</td>
<td>Due Date</td>
<td>Days Overdue</td>
<td>Service Provider</td>
<td>Service Type</td>
<td>Amount Paid</td>
<td>Bill Status</td>
<td>Recurring</td>
</tr>";
//Iterate over all records for the user
foreach($userRecords as $record)
{
$message .= "<tr>";
$message .= " <td>".$record['trans_ref']."</td>";
$message .= " <td>".$record['due_date']."</td>";
$message .= " <td>".$record['days_diff']."</td>";
$message .= " <td>".$record['service_provider']."</td>";
$message .= " <td>".$record['service_type']."</td>";
$message .= " <td>".$record['amount_paid']."</td>";
$message .= " <td>".$record['bill_status']."</td>";
$message .= " <td>".$record['recurring']."</td>";
$message .= "</tr>";
}
//Add message closing tags
$message .= "</table>";
$message .= "</div>";
$message .= "</div>";
}
$bill_status2 = "SCHEDULED";
$query2 = "SELECT username, trans_ref, due_date, service_provider,
service_type, amount_paid, bill_status, recurring
FROM bills
JOIN ogin_details ON xbp_bills.user_id = login_details.user_id
WHERE bills.bill_status = :bill_status";
$stmt2 = $pdo->prepare($query2);
$stmt2->bindValue(':bill_status', $bill_status2, PDO::PARAM_STR);
$stm2t->execute();
##--------------------------------------------------##
## Loop through records and create structured array ##
##--------------------------------------------------##
while($rows2 = $stmt2->fetch(PDO::FETCH_ASSOC))
{
$records2[$rows2['username']][] = $rows2;
}
foreach($records2 as $username2 => $userRecords2)
{
//Create message header
$message2 = "
<img src='cid:my_logo'>
<p>Dear: $username</p>
<h3 style='margin:40px 0 20px 0; color:#900; text-align:center;'>Here is your weekly report summary:</h3>
<h4 style='margin:40px 0 20px 0; color:#900'>Up-Coming Bills</h4>
<table width='100%' border='0' cellspacing='0' cellpadding='0'>
<tr style='font-weight:bold; background-color:#333; color:#fff; height:25px;'>
<td style='padding:5px; text-align:left;'>Trans Ref</td>
<td style='padding:5px; text-align:left;'>Due Date</td>
<td style='padding:5px; text-align:left;'>Service Provider</td>
<td style='padding:5px; text-align:left;'>Service Type</td>
<td style='padding:5px; text-align:left;'>Amount Paid</td>
<td style='padding:5px; text-align:left;'>Recurring</td>
</tr>";
//Iterate over all records for the user
foreach($userRecords2 as $record2)
{
$message2 .= " <tr style='height:25px; border-bottom: 1px solid #ddd; padding:15px; text-align:left;'>";
$message2 .= " <td style='padding:5px; text-align:left;'>".$record2['trans_ref']."</td>";
$message2 .= " <td style='padding:5px; text-align:left;'>".$record2['due_date']."</td>";
$message2 .= " <td style='padding:5px; text-align:left;'>".ucwords($record2['service_provider'])."</td>";
$message2 .= " <td style='padding:5px; text-align:left;'>".ucwords($record2['service_type'])."</td>";
$message2 .= " <td style='padding:5px; text-align:left;'>".number_format($record2['amount_paid'],2)."</td>";
$message2 .= " <td style='padding:5px; text-align:left;'>".$record2['recurring']."</td>";
$message2 .= " </tr>";
}
$message2 .= "</table>";
}
$query3 = " SELECT username, due_date,
SUM(amount_paid) as total_amt
FROM bills
JOIN login_details ON xbp_bills.user_id = login_details.user_id
WHERE login_details.username = '$username'
Group By MONTH(due_date), DAY(due_date)
ORDER BY due_date ASC";
$stmt3 = $pdo->prepare($query3);
$stmt3->execute();
while($rows3 = $stmt3->fetch(PDO::FETCH_ASSOC))
{
$records3[$rows3['username3']][] = $rows3;
}
foreach($records3 as $username3 => $userRecords3)
{
$message3 = "
<h4 style='margin:40px 0 20px 0; color:#900'>Amount needed</h4>
test words test word
<table>
<tr>
<td>Due Date</td>
<td>Amount Due</td>
</tr>
";
//Iterate over all records for the user
foreach($userRecords3 as $record3)
{
$message3 .= "<tr>";
$message3 .= " <td>".$record3['due_date']."</td>";
$message3 .= " <td>".$record3['total_amt']."</td>";
$message3 .= "</tr>";
}
}
?>