firstly you would need to edit the select statement to something like this (untested)
[php]$query = “SELECT username, trans_ref, due_date, service_provider,
service_type, amount_paid, bill_status, recurring
FROM acc_bills
JOIN acc_login_details ON acc_bills.user_id = acc_login_details.user_id
WHERE acc_bills.bill_status = :bill_status”;[/php]
then you would have to run through a loop like this
foreach ($account as $customer=>$data)
{
//code to send mail here
{
//else show errors
}
What happen is that I was able to send mails with the record correctly individually, but now i want the 3 record ( SCHEDULED, OVERDUE AND PAYMENT PLAN) all at once and that is where i was having problem: below is a different code from that above. this code works correctly individually but when i try to make it send at once it wouldnt (dunno if i should create a diff thread or edit the previous code):
##-------------------------##
## NEXT WEEK BILL REMINDER ##
##-------------------------##
$bill_status = "SCHEDULED";
$query = "SELECT username, trans_ref, due_date, service_provider,
service_type, amount_paid, bill_status, recurring
FROM acc_bills
JOIN acc_login_details ON acc_bills.user_id = acc_login_details.user_id
WHERE acc_bills.bill_status = :bill_status";
$stmt = $pdo->prepare($query);
$stmt->bindValue(':bill_status', $bill_status, PDO::PARAM_STR);
$stmt->execute();
##--------------------------------------------------##
## Loop through records and create structured array ##
##--------------------------------------------------##
while($rows = $stmt->fetch(PDO::FETCH_ASSOC))
{
$records[$rows['username']][] = $rows;
}
foreach($records as $username => $userRecords)
{
//Create message header
$message = "
<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($userRecords as $record)
{
$message .= " <tr style='height:25px; border-bottom: 1px solid #ddd; padding:15px; text-align:left;'>";
$message .= " <td style='padding:5px; text-align:left;'>".$record['trans_ref']."</td>";
$message .= " <td style='padding:5px; text-align:left;'>".$record['due_date']."</td>";
$message .= " <td style='padding:5px; text-align:left;'>".ucwords($record['service_provider'])."</td>";
$message .= " <td style='padding:5px; text-align:left;'>".ucwords($record['service_type'])."</td>";
$message .= " <td style='padding:5px; text-align:left;'>".number_format($record['amount_paid'],2)."</td>";
$message .= " <td style='padding:5px; text-align:left;'>".$record['recurring']."</td>";
$message .= " </tr>";
}
$message .= "</table>";
}
##-------------------------##
## OVERDUE REMINDER ##
##-------------------------##
$bill_status2 = "OVERDUE";
$query2 = "SELECT username, trans_ref, due_date, service_provider,
service_type, amount_paid, bill_status, recurring,
DATEDIFF(NOW(), due_date) AS days_diff
FROM acc_bills
JOIN acc_login_details ON acc_bills.user_id = acc_login_details.user_id
WHERE acc_bills.bill_status = :bill_status";
$stmt2 = $pdo->prepare($query2);
$stmt2->bindValue(':bill_status', $bill_status2, PDO::PARAM_STR);
$stmt2->execute();
##--------------------------------------------------##
## Loop through records and create structured array ##
##--------------------------------------------------##
while($rows2 = $stmt2->fetch(PDO::FETCH_ASSOC))
{
$records2[$rows2['username']][] = $rows2;
}
#echo "Debug line: Content of records variable<pre> " . print_r($records, 1) . "</pre>";
##-----------------------------------------------------##
## Iterate over records sending one email to each user ##
##-----------------------------------------------------##
foreach($records2 as $username => $userRecords2)
{
//Create message header
$message2 = "
<h4 style='margin:40px 0 20px 0; color:#900'>Overdue 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;'>Days Overdue</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;'>Bill Status</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;'>".$record2['days_diff']."</td>";
$message2 .= "<td style='padding:5px; text-align:left;'>".$record2['service_provider']."</td>";
$message2 .= "<td style='padding:5px; text-align:left;'>".$record2['service_type']."</td>";
$message2 .= "<td style='padding:5px; text-align:left;'>".$record2['amount_paid']."</td>";
$message2 .= "<td style='padding:5px; text-align:left;'>".$record2['bill_status']."</td>";
$message2 .= "<td style='padding:5px; text-align:left;'>".$record2['recurring']."</td>";
$message2 .= "</tr>";
}
$message2 .= "</table>";
}
##-------------------##
## BILL PAYMENT PLAN ##
##-------------------##
$query3 = " SELECT username, due_date,
SUM(amount_paid) as total_amt
FROM acc_bills
JOIN acc_login_details ON acc_bills.user_id = acc_login_details.user_id
WHERE acc_login_details.username = '$users'
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)
{
//Create message header
$message3 = "
<h4 style='margin:40px 0 20px 0; color:#900'>Plan My Payments</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;'>Due Date</td>
<td>Amount Due</td>
</tr>
";
//Iterate over all records for the user
foreach($userRecords3 as $record3)
{
$message3 .= "<tr style='height:25px; border-bottom: 1px solid #ddd; padding:15px; text-align:left;'>";
$message3 .= " <td style='padding:5px; text-align:left;'>".$record3['due_date']."</td>";
$message3 .= " <td style='padding:5px; text-align:left;'>".$record3['total_amt']."</td>";
$message3 .= "</tr>";
}
$message3 .= "</table>";
$footer = "
</table>
This email was intended for {$username}<br>
</div>";
$msg = $message . $message2 . $message3 . $footer;
//Debug lines
echo "Creating email for user '{$username}'<br><br>\n";
echo "Message:'<br>{$msg}<br><hr><br>\n";
}
well since you have it working individually, i would create a new function that has a loop as mentioned before, and turn each one of the emails into a different function as well. then do a query on the db
$query = "SELECT username, email, due_date, service_provider,
service_type, amount_paid, bill_status, recurring
FROM acc_bills
JOIN acc_login_details ON acc_bills.user_id = acc_login_details.user_id
WHERE acc_bills.bill_status = :bill_status";
foreach ($account as $customer=> $status)
{
if (status == this) {
run_function_$status()
{
//else show errors
}
}
As this is just an example and not actually working code i can try to “codify” it by adding comments as to what its doing.
[php]$query = “SELECT username, email, due_date, service_provider,
service_type, amount_paid, bill_status, recurring
FROM acc_bills
JOIN acc_login_details ON acc_bills.user_id = acc_login_details.user_id
WHERE acc_bills.bill_status = :bill_status”;
//Lets loop through all the accounts and associate each customer(name or id whatever you have) with a billing status
foreach ($account as $customer=> $status)
{
// if the status = this then run this function
if (status == this) {
//your function for the above status would go here
run_function_$status()
{
//otherwise display an error
//else show errors
}
}[/php]