Send multiple rows to one email

I-AM-OBODO · May 5, 2017

1. Why are you using LEFT JOIN? Are there bills with no associated users? If so, there would be no email address to send anyway. Don't use LEFT/RIGHT JOINs unless you need to. They are inefficient.

2. No need to create a prepared query if there are no variables to be applied

3. There is a "class" used in one of the divs. I don't see how it would have a purpose in an email

4. $row_sent has no purpose

5. Don't use SELECT * - only select the data you need

6. You have invalid HTML in the output. The following exists between TR elements

<h3>Yours<br>
Coy Name</h3>

There's a random "</h4>" tag

There's opening tags without corresponding closing tags

Etc.

Pro Tip: Create organized code that separates different logic so it is easy to maintain.

##--------------------------##
## 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 = 'mails@mail.com';     // 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 = 'no_reply@noreply.com';
$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 = ?"
$st = $pdo->prepare($query);
$st->execute([$bill_status]);
 
##--------------------------------------------------##
## 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>".$rows['trans_ref']."</td>";
        $message .= "    <td>".$rows['due_date']."</td>";
        $message .= "    <td>".$rows['days_diff']."</td>";
        $message .= "    <td>".$rows['service_provider']."</td>";
        $message .= "    <td>".$rows['service_type']."</td>";
        $message .= "    <td>".$rows['amount_paid']."</td>";
        $message .= "    <td>".$rows['bill_status']."</td>";
        $message .= "    <td>".$rows['recurring']."</td>";
        $message .= "</tr>";    
    }
    //Add message closing tags
    $message .= "</table>";
    $message .= "</div>";
    $message .= "</div>";
    
    //Send the email to the current user
    $mail->addAddress($username);
    $mail->Body = $message;
    $mail->send();
    $mail->ClearAddresses();
}

Good day.

Hope all is well with you. I've been able to get the values from the database but the problem now is that the values are not well formatted. only the first item is inside the table and subsequent value are not aligned inside the table?

Any help will do.

Thanks

Psycho · May 9, 2017

Good day.

Hope all is well with you. I've been able to get the values from the database but the problem now is that the values are not well formatted. only the first item is inside the table and subsequent value are not aligned inside the table?

Any help will do.

Thanks

Since you chose not to provide the code you are currently using, I'm not sure what help can be provided.

I-AM-OBODO · May 10, 2017

Since you chose not to provide the code you are currently using, I'm not sure what help can be provided.

Sorry for that. Here is the code:

##--------------------------##
## 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 = 'mails@mail.com';     // 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 = 'no_reply@noreply.com';
$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>";
    
    /*
    //Send the email to the current user
    $mail->addAddress($username);
    $mail->Body = $message;
    $mail->send();
    $mail->ClearAddresses();
    */
    echo "Creating email for user '{$username}'<br><br>\n";
    echo "Message:'<br>{$message}<br><hr><br>\n";
}

Thanks

Psycho · May 10, 2017

I see nothing obvious in that code that would cause the content of the first record to be displayed different than the subsequent records. Are you sure that there isn't content in the records causing issues in how the data is displayed?

Try viewing the HTML source of the output to see where the problem may be.

I-AM-OBODO · May 11, 2017

I see nothing obvious in that code that would cause the content of the first record to be displayed different than the subsequent records. Are you sure that there isn't content in the records causing issues in how the data is displayed?

Try viewing the HTML source of the output to see where the problem may be.

Yes I am sure. I've inspected and found nothing!

I-AM-OBODO · May 11, 2017

I see nothing obvious in that code that would cause the content of the first record to be displayed different than the subsequent records. Are you sure that there isn't content in the records causing issues in how the data is displayed?

Try viewing the HTML source of the output to see where the problem may be.

Oops! Thanks. I've seen where the problem lies. It's one of the html markups. Thanks a zillion dozen times

I-AM-OBODO · May 11, 2017

Thank all for the help. Finally its working as expected.

I-AM-OBODO · May 28, 2017

I see nothing obvious in that code that would cause the content of the first record to be displayed different than the subsequent records. Are you sure that there isn't content in the records causing issues in how the data is displayed?

Try viewing the HTML source of the output to see where the problem may be.

I tried merging the results and mail to the different users but it gets to only one user and it reveals other persons details. How can i merge the result and send to each user. 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 = 'mails@mail.com';     // 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 = 'no_reply@noreply.com';
$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>";
          
   }
   
}
?>

Sign In

Send multiple rows to one email

Recommended Posts

I-AM-OBODO

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

I-AM-OBODO

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

I-AM-OBODO

Link to comment

Share on other sites

I-AM-OBODO

Link to comment

Share on other sites

I-AM-OBODO

Link to comment

Share on other sites

I-AM-OBODO

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information