Jump to content

Send multiple rows to one email


I-AM-OBODO

Recommended Posts

 

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • 3 weeks later...

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>";
          
   }
   
}
?>
 
Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.