Send multiple rows to one email

[I-AM-OBODO]

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

}

[Barand]

Don't post multiple copies of the same problem

[I-AM-OBODO]

Don't post multiple copies of the same problem

 

the problem was not solved and it was not the same. the initial thread was diff but i just added it to find two solutions without opening two threads. can you help with it though. would be glad if u can.

 

thanks

[Barand]

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

[Psycho]

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

[I-AM-OBODO]

 

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

[I-AM-OBODO]

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

[I-AM-OBODO]

 

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

 

 

           

[I-AM-OBODO]

Oops! sorry all. Mine did not work either, it sends to all users

[I-AM-OBODO]

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

Mine still didnt work

[Psycho]

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.

[I-AM-OBODO]

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 February 2, 2017 by Mr-Chidi

[Psycho]

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.

[I-AM-OBODO]

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

[Barand]

Looks like a typo

 

while($row = $st->fetch(PDO::FETCH_ASSOC))

then

$message .= "    <td>".$rows['trans_ref']."</td>";

[I-AM-OBODO]

Looks like a typo

 

while($row = $st->fetch(PDO::FETCH_ASSOC))

then

$message .= "    <td>".$rows['trans_ref']."</td>";

I noticed that from the beginning and corrected the typo. Don't think its the problem

[I-AM-OBODO]

 

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

[Barand]

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.

[I-AM-OBODO]

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

[Barand]

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

[I-AM-OBODO]

 

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;
 

[ginerjm]

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 May 3, 2017 by ginerjm

[I-AM-OBODO]

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.

[ginerjm]

Add some echo statements just before the mail call and see that all the values are correct. Debug, Debug, Debug!!! Be sure that php error checking is turned as well. (see my signature)

[ginerjm]

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.