Jump to content

Send multiple rows to one email


I-AM-OBODO
 Share

Go to solution Solved by Psycho,

Recommended Posts

Hi all

Please, i have a script that send email to customers. The problem with the script is that it sends multiple mails but all i want is for it to send one mail containing details of multiple rows. i.e all the bill details should be in a table.

below is my script

Thanks

 

 

$bill_status = "OVERDUE";

$st = $pdo->prepare("SELECT *, DATEDIFF(NOW(), due_date) AS days_diff FROM bills
LEFT JOIN login_details ON bills.user_id = login_details.user_id
WHERE bills.bill_status = '$bill_status'");
$st->execute();
$row_sent = $st->rowCount();


//$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';


while($rows = $st->fetch(PDO::FETCH_ASSOC)){
    $username = $rows['username'];
    
$message= "
<div class='messages'>
<h3><img src='cid:my_logo'> </h3>
<br>
<div style='font-size:15px;'>Email Notification on Bill Due for Payment </h4>
<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>

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

$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>";

$mail->addAddress($username);     // Add a recipient
$mail->Body    = $message;
$mail->send();
$mail->ClearAddresses();

}
Link to comment
Share on other sites

The process is simply ot loop through the results, check for a a change in username and send email when it changes.

 

Here's a pseudocode description of the process

set currentcust = ''
set email content = ''
while fetch next row
    if username != currentcust                  // have we a new username?
        if currentcust != ''                    // don't sent to empty customer
            send email to currentcust
        endif
        set currentcust = username              // save new username
        set emailcontent = ''
    endif
    append billing detail to email content      // build email content
endwhile
send email to currentcust                       // don't forget to sent to last customer in data
Link to comment
Share on other sites

  • Solution

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();
}
  • Like 2
Link to comment
Share on other sites

 

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();
}

Thanks but didnt work. Yours sends empty message without the bills details. I like the way you structure your code and learnt a thing or two form it.

Thanks a great deal

Link to comment
Share on other sites

Thanks all. I have been able to see the reason why my script did not work as intended but i have fixed it and its working perfectly now. I removed the send message from the loop and it did the magic.

 

I really dont know if theres anything wrong with my style but it did the magic for me. Would like a pointer to a good direction if need be on my script. Like psych noted out concerning my unclosed html tags

 

Thanks all

Link to comment
Share on other sites

 

The process is simply ot loop through the results, check for a a change in username and send email when it changes.

 

Here's a pseudocode description of the process

set currentcust = ''
set email content = ''
while fetch next row
    if username != currentcust                  // have we a new username?
        if currentcust != ''                    // don't sent to empty customer
            send email to currentcust
        endif
        set currentcust = username              // save new username
        set emailcontent = ''
    endif
    append billing detail to email content      // build email content
endwhile
send email to currentcust                       // don't forget to sent to last customer in data

in between, will try to work something based on your directions and see what i come up with. I tried my working script to send mails manually it worked but still wont work on cron. I dont want to open another thread cos i might be violating the rules of the forum which i wouldnt want to do.

this is my command line for the cron:

Thanks

0 0 * * *

/usr/local/bin/php /home/user/public_html/mydirectory/cronjobs/cron-reminder.php

 

 

           
Link to comment
Share on other sites

Didn't work does not provide any details as to what the problem may be. You didn't even provide the modified code that you are currently using.

 

Not trying to be rude, but you need to learn how to debug code. My signature includes the statement "I do not always test the code I provide, so there may be some syntax errors.". I expect the person receiving the code to do the testing. I am providing the "logic".

 

So, going back to my code, the first debugging step I would add would be to generate some output on the web page to SEE what values are being generated instead of waiting for emails.

 

 

    //Send the email to the current user
//    $mail->addAddress($username);
//    $mail->Body = $message;
//    $mail->send();
//    $mail->ClearAddresses();
 
//Debug lines
echo "Creating email for user '{$username}'<br><br>\n";
echo "Message:'<br>{$message}<br><hr><br>\n";

 

Change the last part of the code I gave you with that above, run it, and inspect the output to see if it is correct.

Link to comment
Share on other sites

Didn't work does not provide any details as to what the problem may be. You didn't even provide the modified code that you are currently using.

 

Not trying to be rude, but you need to learn how to debug code. My signature includes the statement "I do not always test the code I provide, so there may be some syntax errors.". I expect the person receiving the code to do the testing. I am providing the "logic".

 

So, going back to my code, the first debugging step I would add would be to generate some output on the web page to SEE what values are being generated instead of waiting for emails.

    //Send the email to the current user
//    $mail->addAddress($username);
//    $mail->Body = $message;
//    $mail->send();
//    $mail->ClearAddresses();
 
//Debug lines
echo "Creating email for user '{$username}'<br><br>\n";
echo "Message:'<br>{$message}<br><hr><br>\n";

Change the last part of the code I gave you with that above, run it, and inspect the output to see if it is correct.

Thanks. And sorry for not being elaborate in my reply. I meant that a mail was sent but it was empty. The details of the bill was not sent along. Even after adding your debugging code, it brought out same empty message. I did a little alteration though cos the code gave an error and i noticed it was mere omission here:

 

 

##----------------------##
## 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]);

 

I replaced it with:

 

##----------------------##
## 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();

 

And

##--------------------------------------------------##
## Loop through records and create structured array ##
##--------------------------------------------------##
while($row = $st->fetch(PDO::FETCH_ASSOC))
{
    $records[$rows['username']][] = $row;
}

I added s o the row.

 

Thanks

Edited by Mr-Chidi
Link to comment
Share on other sites

As I was stating before, you need to learn how to debug. You state that you were not getting any output when adding the echo statements. Were you getting NO output or were getting the following with no data:

 

 

Creating email for user 

Message:

 

If you are getting NO output at all, then either there are no records returned from the query or there is some other error causing the script to exit prematurely. If you are getting the above message with no content, then either the field returned from the query are empty or the wrong field names are referenced in the logic.

 

Since you did not provide explicit details on what exactly was returned, I can't tell you what you should do next. But, for example, if you had NO output at all, then you could do the following:

 

while($row = $st->fetch(PDO::FETCH_ASSOC))
{
    $records[$rows['username']][] = $row;
}
//Add the following
echo "Debug line: Content of records variable<pre> " . print_r($records, 1) . "</pre>";

 

If that line is not displayed at all, you likely have some type of critical error occurring earlier in the script. If it is displayed with an empty array, then the issue is probably with the query or there are no records matching the condition. If that line is displayed with records in the array, then the issue is likely later in the script in using that data.

 

When you have an issue and have no idea where the actual problem is, pick a point in the logic and generate some output with relevant variables so you can inspect what the values are. You can then make a determination if the problem is created before or after that point. Then, based on the results, pick a different point in the logic to inspect. Rinse and repeat till you find the problem. As you get more experienced, you can add debugging logic into your code from the very beginning that you can turn on/off or which is logged. But, you should never create such debugging code that will be presented to the user.

Link to comment
Share on other sites

As I was stating before, you need to learn how to debug. You state that you were not getting any output when adding the echo statements. Were you getting NO output or were getting the following with no data:

 

 

 

If you are getting NO output at all, then either there are no records returned from the query or there is some other error causing the script to exit prematurely. If you are getting the above message with no content, then either the field returned from the query are empty or the wrong field names are referenced in the logic.

 

Since you did not provide explicit details on what exactly was returned, I can't tell you what you should do next. But, for example, if you had NO output at all, then you could do the following:

while($row = $st->fetch(PDO::FETCH_ASSOC))
{
    $records[$rows['username']][] = $row;
}
//Add the following
echo "Debug line: Content of records variable<pre> " . print_r($records, 1) . "</pre>";

If that line is not displayed at all, you likely have some type of critical error occurring earlier in the script. If it is displayed with an empty array, then the issue is probably with the query or there are no records matching the condition. If that line is displayed with records in the array, then the issue is likely later in the script in using that data.

 

When you have an issue and have no idea where the actual problem is, pick a point in the logic and generate some output with relevant variables so you can inspect what the values are. You can then make a determination if the problem is created before or after that point. Then, based on the results, pick a different point in the logic to inspect. Rinse and repeat till you find the problem. As you get more experienced, you can add debugging logic into your code from the very beginning that you can turn on/off or which is logged. But, you should never create such debugging code that will be presented to the user.

 

Hello and thanks for the response.

when i added the following:

 

 

echo "Debug line: Content of records variable<pre> " . print_r($records, 1) . "</pre>";

It echoed the result in array.

Maybe I am not making my self clear enough but what I am saying is that it echoed the message with the table headings but no details. I have tried so many debugging that i could muster but all to no avail.

 

It echoes:

 

 

$message = "
        <div class='messages'>
        <h3><img src='cid:my_logo'></h3><br>
        <div style='font-size:16px; font-weight:bold; text-align=center; margin-bottom:10px;'>Email Notification on Bill Due for Payment</div>
        <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>";

I think the problem should be withing this area cos thats whats not echoeing:

 

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>";    
    }

I have learnt debugging skill from your debug method which i have applied to some other areas in my app.

 

Hope i am clearer now.

 

Thanks

Link to comment
Share on other sites

 

The process is simply ot loop through the results, check for a a change in username and send email when it changes.

 

Here's a pseudocode description of the process

set currentcust = ''
set email content = ''
while fetch next row
    if username != currentcust                  // have we a new username?
        if currentcust != ''                    // don't sent to empty customer
            send email to currentcust
        endif
        set currentcust = username              // save new username
        set emailcontent = ''
    endif
    append billing detail to email content      // build email content
endwhile
send email to currentcust                       // don't forget to sent to last customer in data

 

And after following your guide, I was able to get the result on the debug mode but the message and the table headings did not display. I even tried to use it to test it live but did not get any mail.

 

 

$currentcust = '';
$emailcontent="
<div class='messages'>
<h3><img src='cid:my_logo'></h3>
<br>
<h4><div style='font-size:15px;'>Email Notification </h4>
<br>
<p>Dear: Client</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>

";
while($rows = $st->fetch(PDO::FETCH_ASSOC)):
    $username = $rows['username'];
    if($username != $currentcust):
    
        if($currentcust != ''):
        #$mail->addAddress($currentcust);
        echo "Creating email for user '{$currentcust}'<br><br>\n";
        echo "Message:'<br>{$emailcontent}<br><hr><br>\n";

        endif;
        $currentcust = $username;
        $emailcontent = '';
        endif;
        
        $emailcontent .= " <tr>";
        $emailcontent .= " <td>".$rows['trans_ref']."</td>";
        $emailcontent .= " <td>".$rows['due_date']."</td>";
        $emailcontent .= " <td>".$rows['days_diff']."</td>";
        $emailcontent .= " <td>".ucwords($rows['service_provider'])."</td>";
        $emailcontent .= " <td>".ucwords($rows['service_type'])."</td>";
        $emailcontent .= " <td>".number_format($rows['amount_paid'],2)."</td>";
        $emailcontent .= " <td>".$rows['bill_status']."</td>";
        $emailcontent .= " <td>".$rows['recurring']."</td>";
        $emailcontent .= " <td>".$rows['bank_url']."</td>";
        $emailcontent .= " </tr>";
        
    endwhile;
    #$mail->addAddress($currentcust);
    
    echo "Creating email for user '{$currentcust}'<br><br>\n";
    echo "Message:'<br>{$emailcontent}<br><hr><br>\n";
Link to comment
Share on other sites

Before you start the loop, you set the email content to the message and table headings.

 

However, within the loop, after sending an email, you set the content back to an empty string. You need to set it to the message and headings again. You then add the rows for the next customer.

 

Hello and thanks for the response. I have tried different ways from what i understand you said and all to no avail. If you can, use my code to clarify me pls. Thanks

Link to comment
Share on other sites

If  by that you mean "will I write it for you?" then no, but I'll alter the pseudocode

set currentcust = ''
set email content = 'message and table headings'                                               // this you are doing OK
while fetch next row
    if username != currentcust                  // have we a new username?
        if currentcust != ''                    // don't sent to empty customer
            send email to currentcust
        endif
        set currentcust = username              // save new username
        set emailcontent = 'message and table headings'                                        // this you need to do
    endif
    append billing detail to email content      // build email content
endwhile
send email to currentcust                       // don't forget to sent to last customer in data
Link to comment
Share on other sites

  • 2 months later...

 

If  by that you mean "will I write it for you?" then no, but I'll alter the pseudocode

set currentcust = ''
set email content = 'message and table headings'                                               // this you are doing OK
while fetch next row
    if username != currentcust                  // have we a new username?
        if currentcust != ''                    // don't sent to empty customer
            send email to currentcust
        endif
        set currentcust = username              // save new username
        set emailcontent = 'message and table headings'                                        // this you need to do
    endif
    append billing detail to email content      // build email content
endwhile
send email to currentcust                       // don't forget to sent to last customer in data

 

Hello. Congrats on ur 1k likes!

 

I am still battling with this though. I have tried everything i could but the new problem is that, instead of sending the bills all at once, it sends them multiple times. eg lets say i have 7 bills, it sends  4 different mails some containing 3 bills, some containing 1bill and some containing 2 bills. All i want is all the bills associated to a use be sent to the user all at once. this is my current code:

 

 

 
$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 table1
            JOIN table2 ON table1.user_id = table2.user_id
            WHERE table1.bill_status = :bill_status";

$st = $pdo->prepare($query);
$st->bindValue(':bill_status', $bill_status, PDO::PARAM_STR);
$st->execute();

$mail = new PHPMailer;
$mail->isSMTP();
$mail->Host = 'localhost';
$mail->SMTPAuth = true;
$mail->Username = 'mails@mail.zap';
$mail->Password = 'password';
$mail->SMTPSecure = 'ssl';
$mail->Port = 465;
$mail->From = 'no_reply@zap.zap';
$mail->FromName = 'Coy Cor';
$mail->WordWrap = 587;
$mail->AddEmbeddedImage("inc/logo.png", "my_logo");
$mail->isHTML(true);
$mail->Subject = 'Email Notification on OVERDUE Bill';


$currentcust = '';                                            
while($rows = $st->fetch(PDO::FETCH_ASSOC)):
$username = $rows['username'];
   if($username != $currentcust):                  // have we a new username?
        if($currentcust != ''):                  // don't sent to empty customer
      
$msg = $emailcontent . $footer;
$mail->addAddress($currentcust);
$mail->Body    = $msg;
$mail->send();
$mail->ClearAddresses();

endif;
$currentcust = $username;   
$emailcontent="
<h3><img src='cid:my_logo'></h3>
<br>
<h2 style='text-align:center; color:#900'>Email Notification on OVERDUE Bill(s)</h2>
<br>
<p>Dear: Customer</p>
<br>
<h4 style='margin:40px 0 20px 0; color:#900''>
The following bills are overdue:
</h4>
<br>
<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>
";

$footer = "
<div style='background:#0070c0; color:#fff; padding:15px 0 15px 0; margin-top:50px; text-align:center;'>
© Coy 2018
</div>";
                                     
endif;

$emailcontent .= " <tr>";
$emailcontent .= " <td>".$rows['trans_ref']."</td>";
$emailcontent .= " <td>".$rows['due_date']."</td>";
$emailcontent .= " <td>".$rows['days_diff']."</td>";
$emailcontent .= " <td>".ucwords($rows['service_provider'])."</td>";
$emailcontent .= " <td>".ucwords($rows['service_type'])."</td>";
$emailcontent .= " <td>".number_format($rows['amount_paid'],2)."</td>";
$emailcontent .= " <td>".$rows['bill_status']."</td>";
$emailcontent .= " <td>".$rows['recurring']."</td>";
$emailcontent .= " </tr>";

endwhile;
 
Link to comment
Share on other sites

Don't know that I did much but I cleaned up a few things. I also added a 'close table' tag which you needed.

//********************************
$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 table1
JOIN table2 ON table1.user_id = table2.user_id
WHERE table1.bill_status = :bill_status";

$st = $pdo->prepare($query);
$st->bindValue(':bill_status', $bill_status, PDO::PARAM_STR);
$st->execute();
// setup email
$mail = new PHPMailer;
$mail->isSMTP();
$mail->Host = 'localhost';
$mail->SMTPAuth = true;
$mail->Username = 'mails@mail.zap';
$mail->Password = 'password';
$mail->SMTPSecure = 'ssl';
$mail->Port = 465;
$mail->From = 'no_reply@zap.zap';
$mail->FromName = 'Coy Cor';
$mail->WordWrap = 587;
$mail->AddEmbeddedImage("inc/logo.png", "my_logo");
$mail->isHTML(true);
$mail->Subject = 'Email Notification on OVERDUE Bill';

// begin loop on all outstanding bills
$footer = "
<div style='background:#0070c0; color:#fff; padding:15px 0 15px 0; margin-top:50px; text-align:center;'>
© Coy 2018
</div>";
$prevcust = '';
while($rows = $st->fetch(PDO::FETCH_ASSOC))
{ // use of {} instead of : endxxx if SO Much Clearer!
$username = $rows['username'];
if($username != $prevcust) // have we a new username?
{
// send mail to prev customer but not if first time
if($prevcust != '')
{
$emailcontent .= "</table>";
$msg = $emailcontent . $footer;
$mail->addAddress($prevcust);
$mail->Body = $msg;
$mail->send(); // send the email
$mail->ClearAddresses();
}
// Begin a new email message
$prevcust = $username; // save cust name
$emailcontent = "
<h3><img src='cid:my_logo'></h3>
<br>
<h2 style='text-align:center; color:#900'>
Email Notification of OVERDUE Bill(s)
</h2>
<br>
<p>Dear: $username</p>
<br>
<h4 style='margin:40px 0 20px 0; color:#900;'>
The following bills are overdue:
</h4>
<br>
<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>
";
}
// output the current bill data
$emailcontent .= "<tr>";
$emailcontent .= "<td>".$rows['trans_ref']."</td>";
$emailcontent .= "<td>".$rows['due_date']."</td>";
$emailcontent .= "<td>".$rows['days_diff']."</td>";
$emailcontent .= "<td>".ucwords($rows['service_provider'])."</td>";
$emailcontent .= "<td>".ucwords($rows['service_type'])."</td>";
$emailcontent .= "<td>".number_format($rows['amount_paid'],2)."</td>";
$emailcontent .= "<td>".$rows['bill_status']."</td>";
$emailcontent .= "<td>".$rows['recurring']."</td>";
$emailcontent .= "</tr>";
}
// Loop is done - be sure to send out the last email that was built
// send mail to prev customer but not if first time
$emailcontent .= "</table>";
$msg = $emailcontent . $footer;
$mail->addAddress($prevcust);
$mail->Body = $msg;
$mail->send(); // send the last email
$mail->ClearAddresses();

 

PS - I HATE how the forum amends my nicely formatted posts every time!

Edited by ginerjm
Link to comment
Share on other sites

Don't know that I did much but I cleaned up a few things. I also added a 'close table' tag which you needed.

//********************************
$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 table1
JOIN table2 ON table1.user_id = table2.user_id
WHERE table1.bill_status = :bill_status";

$st = $pdo->prepare($query);
$st->bindValue(':bill_status', $bill_status, PDO::PARAM_STR);
$st->execute();
// setup email
$mail = new PHPMailer;
$mail->isSMTP();
$mail->Host = 'localhost';
$mail->SMTPAuth = true;
$mail->Username = 'mails@mail.zap';
$mail->Password = 'password';
$mail->SMTPSecure = 'ssl';
$mail->Port = 465;
$mail->From = 'no_reply@zap.zap';
$mail->FromName = 'Coy Cor';
$mail->WordWrap = 587;
$mail->AddEmbeddedImage("inc/logo.png", "my_logo");
$mail->isHTML(true);
$mail->Subject = 'Email Notification on OVERDUE Bill';

// begin loop on all outstanding bills
$footer = "
<div style='background:#0070c0; color:#fff; padding:15px 0 15px 0; margin-top:50px; text-align:center;'>
© Coy 2018
</div>";
$prevcust = '';
while($rows = $st->fetch(PDO::FETCH_ASSOC))
{ // use of {} instead of : endxxx if SO Much Clearer!
$username = $rows['username'];
if($username != $prevcust) // have we a new username?
{
// send mail to prev customer but not if first time
if($prevcust != '')
{
$emailcontent .= "</table>";
$msg = $emailcontent . $footer;
$mail->addAddress($prevcust);
$mail->Body = $msg;
$mail->send(); // send the email
$mail->ClearAddresses();
}
// Begin a new email message
$prevcust = $username; // save cust name
$emailcontent = "
<h3><img src='cid:my_logo'></h3>
<br>
<h2 style='text-align:center; color:#900'>
Email Notification of OVERDUE Bill(s)
</h2>
<br>
<p>Dear: $username</p>
<br>
<h4 style='margin:40px 0 20px 0; color:#900;'>
The following bills are overdue:
</h4>
<br>
<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>
";
}
// output the current bill data
$emailcontent .= "<tr>";
$emailcontent .= "<td>".$rows['trans_ref']."</td>";
$emailcontent .= "<td>".$rows['due_date']."</td>";
$emailcontent .= "<td>".$rows['days_diff']."</td>";
$emailcontent .= "<td>".ucwords($rows['service_provider'])."</td>";
$emailcontent .= "<td>".ucwords($rows['service_type'])."</td>";
$emailcontent .= "<td>".number_format($rows['amount_paid'],2)."</td>";
$emailcontent .= "<td>".$rows['bill_status']."</td>";
$emailcontent .= "<td>".$rows['recurring']."</td>";
$emailcontent .= "</tr>";
}
// Loop is done - be sure to send out the last email that was built
// send mail to prev customer but not if first time
$emailcontent .= "</table>";
$msg = $emailcontent . $footer;
$mail->addAddress($prevcust);
$mail->Body = $msg;
$mail->send(); // send the last email
$mail->ClearAddresses();

 

PS - I HATE how the forum amends my nicely formatted posts every time!

 

Thanks.

But yours did not send any mail.

Link to comment
Share on other sites

Be sure that you have php error checking on. And....

Try using this line to do your send:

if(!$mail->send())
{
    echo "Mailer Error: " . $mail->ErrorInfo;
}



It is always good practice to CHECK the results of things outside the scope of your code such as a call to a mail program that may not work as expected.

Link to comment
Share on other sites

This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • 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.