-1

I have a mysql db with stored a table with some columns about person informations as

  • object1 (p.k)
  • username
  • email
  • service
  • sent

With a php code for phpmailer I can connect to db, extract informations that I need from a sql query and sent email for every person through a foreach function, after every email is sent correctly, data for column "sent" is update from 0 to 1... all works fine, but there's possibility there're some people with same "emails" for multiple own "object1" and in this case, for every rows will be sent an email to them...

For example if one person with email aa@aa.it has 30 "object1", he'll receive 30 different emails.

How can I combine work with data from query and do all of these object1 associated to one email are sent in only one email with list all of theme as content?

I think that I need to complete script with a if/else istructions, but I don't know how manage it.

Below part of script to do it:

   <?
 $mail = new PHPMailer;

    $result = mysqli_query($mysql, 'SELECT * FROM db WHERE service LIKE \'%something%\' AND sent = 0 ORDER BY object1 LIMIT 0 , 30');

    foreach ($result as $row) { 

        $mail->addAddress($row['email'], $row['object1']);

        /* i use it to use an external .html file to send email and replace vairables in it */

        $message = file_get_contents('template/temaplate.html');

        $message = str_replace('%email%', $row['email'], $message);
        $message = str_replace('%object1%', $row['object1'], $message);


        //Set the message
        $mail->MsgHTML($message);
        $mail->AltBody = strip_tags($message);


        if (!$mail->send()) {
            echo "Mailer Error (" . str_replace("@", "&#64;", $row["email"]) . ') ' . $mail->ErrorInfo .;
            break; //Abandon sending
        } else {
            echo "Message sent to :" . $row['object1'] . ' (' . str_replace("@", "&#64;", $row['email']) .;

            //Mark it as sent in the DB
            mysqli_query(
                $mysql,
                "UPDATE db SET sent = true WHERE object1 = '" .
                mysqli_real_escape_string($mysql, $row['object1']) . "'"
            );
        }
        // Clear all addresses and attachments for next loop
        $mail->clearAddresses();
        $mail->clearAttachments();
    }
?>

I hope to have explained correctly my needs and ask you some helps and suggestions for it.

RBT
  • 24,161
  • 21
  • 159
  • 240
Zarbat
  • 457
  • 1
  • 4
  • 14
  • Some sensible code indentation would be a good idea. It help us read the code and more importantly it will help **you debug your code** [Take a quick look at a coding standard](http://www.php-fig.org/psr/psr-2/) for your own benefit. You may be asked to amend this code in a few weeks/months and you will thank me in the end. – RiggsFolly Sep 04 '16 at 18:33
  • Your script is at risk of [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Have a look at what happened to [Little Bobby Tables](http://bobby-tables.com/) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) – RiggsFolly Sep 04 '16 at 18:36
  • It does not look like you have made any attempt to write this code yourself. **SO != Free coding resources** – RiggsFolly Sep 04 '16 at 18:39

1 Answers1

0

Instead of order by object1, you can order by email, so that rows with the same email will come out together.

$prev_email = null;
foreach ($result as $row) {
    if ($row['email'] === $prev_email) {
        // skip, but may print some warning etc.
        // or check other conditions about object1
        continue;
    }
    ....
    $prev_email = $row['email'];
}

This way, each email only got one message. But you may need to fine tune the logic if you care which object1 to use among those emails.

Don Flash
  • 11
  • 3