SQL query group specific column value to send single e-mail for each user

Question

I really need help with this, I'm trying for some hours to make my code to work properly but I can't.

I'll place some of my code and my outputs to easily understand what I need and what I'm getting.

As you can see in the output there are some users with more than one card, and what I need is, send one e-mail for each user with their cards, for example:

Email #1
To: test1@gmail.com
Text: 
Card 1

Email #2
To: test2@gmail.com
Text: 
Card 2
Card 3

Email #3
To: test3@gmail.com
Text: 
Card 1
Card 2
Card 4

I should send e-mails like above, but my code that I will post below is not doing it, it sends the e-mails but the first e-mail that it sends goes to test1@gmail.com and test2@gmail.com and the second e-mail goes to test3@gmai.com, and it sends one e-mail for each card instead one e-mail with all the client cards

if (!$stm)
{
    echo "";
}
else
{
    $last_email = null;
    while ($rows = $stm->fetch())
    {
        $CPersonUId = isset($rows['PersonUId']) ? $rows['PersonUId'] : NULL;
        $CType = isset($rows['CardType']) ? $rows['CardType'] : NULL;
        $CNr = isset($rows['CardNr']) ? $rows['CardNr'] : NULL;
        $CValFrom = isset($rows['ValidFrom']) ? $rows['ValidFrom'] : NULL;
        $CValUntil = isset($rows['ValidUntil']) ? $rows['ValidUntil'] : NULL;
        $CLTCCTime = isset($rows['LastTccTime']) ? $rows['LastTccTime'] : NULL;
        $CLTPEmail = isset($rows['Email']) ? $rows['Email'] : NULL;

        if ($CValUntil != NULL)
        {
            $time1 = strtotime($CValUntil);
            $mytime1 = date("d/m/Y", $time1);
            $CValUntil = $mytime1;
        }else{
            $mytime1 = $CValUntil;
        }

        if($CType == 6){
            $CType = 'Type 1';
        }elseif($CType == 5){
            $CType = 'Type 2';
        }elseif($CType == 1){
            $CType = 'Type 3';
        }

    $message = '<html><body>';
        $message .= "<p>Cartão nº<b>".$CNr."</b> válido até <b>".$mytime1." Tipo: ".$CType."</b></p>";

    $message .= '</body></html>';

    $mail->IsSMTP();
    $mail->SMTPDebug = 0;
    $mail->Host = "test-localhost";
    $mail->SMTPAuth = true;
    $mail->SMTPSecure = 'ssl'; 
    $mail->Port = "465";
    $mail->Username = "admin@gmail.com";
    $mail->Password = "passxxxxx";
    $mail->SetFrom('admin@gmail.com', 'admin');
    $mail->addReplyTo("admin@gmail.com");
    $mail->Subject    = "Subject";
    $mail->AltBody    = "Alt";
    $mail->MsgHTML($message);


        if ( $last_email == null ) {
        $last_email = $CLTPEmail;
        }
        if ( $CLTPEmail != $last_email ) {
            //echo $last_email . "</br>";
            $mail->addAddress($last_email);
             if(!$mail->send()) {
        echo "Mailer Error: " . $mail->ErrorInfo;
    } else {
        echo "Message sent!";
    }
            $last_email = $CLTPEmail;
        }
    }
    echo $last_email."</br>";
}

Sounds like you just need comma delimited results for the column `Card`. Possible duplicate of [Comma separated results in SQL](https://stackoverflow.com/questions/18870326/comma-separated-results-in-sql)? — Thom A, Nov 15 '18 at 16:06
[Didn't you ask something similar already?](https://stackoverflow.com/q/53318436/1415724). — Funk Forty Niner, Nov 15 '18 at 16:11
@FunkFortyNiner I asked it but was regarding my query, I've been trying to send the e-mail based on the help I got on that question but is not working properly, but they answered what I asked on that question since the problem is a little different now, I made a new question. — Anoushka Iola, Nov 15 '18 at 16:13
@Larnu, unfortunately, I could not understand how to make it on my query, but I'll keep trying — Anoushka Iola, Nov 15 '18 at 16:17
@Larnu btw I think that's not what I need, becuase I don't need to send the cards separated with commas, I need to send them all in the same e-mail for each user. — Anoushka Iola, Nov 15 '18 at 16:18
You don't have to use a `','` as a separator; it could be a line break, a pipe (`|`), or every multiple characters. I'm pretty sure it serves the purpose here. — Thom A, Nov 15 '18 at 17:13

Николай Лубышев · Accepted Answer · 2018-11-15T17:20:30.760

<?php

function sendCards( $cards, $email ) {
            if( empty( $cards ) || ! $email ) return;
            $message = '<html><body>';
            $message .= implode('', $cards);
            $message .= '</body></html>';

            $mail->IsSMTP();
            $mail->SMTPDebug = 0;
            $mail->Host = "test-localhost";
            $mail->SMTPAuth = true;
            $mail->SMTPSecure = 'ssl'; 
            $mail->Port = "465";
            $mail->Username = "admin@gmail.com";
            $mail->Password = "passxxxxx";
            $mail->SetFrom('admin@gmail.com', 'admin');
            $mail->addReplyTo("admin@gmail.com");
            $mail->Subject    = "Subject";
            $mail->AltBody    = "Alt";
            $mail->MsgHTML($message);
            $mail->addAddress($email);
            if(!$mail->send()) {
              echo "Mailer Error: " . $mail->ErrorInfo;
            } else {
              echo "Message sent!";
            }
            echo $last_email."</br>";
}

if (!$stm) {
    echo ""; 
} else {
    $last_email = null; 
    $cards = [];
    while ($rows = $stm->fetch())
    {
        $CLTPEmail = isset($rows['Email']) ? $rows['Email'] : NULL;
        if( ! $CLTPEmail ) continue;
        if( $last_email !== $CLTPEmail ) {
            sendCards( $cards, $last_email );
            $cards = [];
            $last_email = $CLTPEmail;
        }

        $CPersonUId = isset($rows['PersonUId']) ? $rows['PersonUId'] : NULL;
        $CType = isset($rows['CardType']) ? $rows['CardType'] : NULL;
        $CNr = isset($rows['CardNr']) ? $rows['CardNr'] : NULL;
        $CValFrom = isset($rows['ValidFrom']) ? $rows['ValidFrom'] : NULL;
        $CValUntil = isset($rows['ValidUntil']) ? $rows['ValidUntil'] : NULL;
        $CLTCCTime = isset($rows['LastTccTime']) ? $rows['LastTccTime'] : NULL;

        if ($CValUntil != NULL)
        {
            $time1 = strtotime($CValUntil);
            $mytime1 = date("d/m/Y", $time1);
            $CValUntil = $mytime1;
        }else{
            $mytime1 = $CValUntil;
        }

        if($CType == 6){
            $CType = 'Type 1';
        }elseif($CType == 5){
            $CType = 'Type 2';
        }elseif($CType == 1){
            $CType = 'Type 3';
        }
        $cards [] = "<p>Cartão nº<b>".$CNr."</b> válido até <b>".$mytime1." Tipo: ".$CType."</b></p>";
    }

    if( ! empty( $cards )) {
        sendCards( $cards, $last_email )
    }
}

I'm getting the following error `Parse error: syntax error, unexpected '$cards' ` right here `$cards = [];` after `sendCards( $cards, $last_email )` — Anoushka Iola, Nov 15 '18 at 17:05
Giving me this error: `Parse error: syntax error, unexpected '$cards' (T_VARIABLE) in...` — Anoushka Iola, Nov 15 '18 at 17:15
The error is in the same line after `sendCards( $cards, $last_email )` — Anoushka Iola, Nov 15 '18 at 17:17
Alright, after adding the last code, and the ; now I don't have errors but I also don«t get any message of email sent — Anoushka Iola, Nov 15 '18 at 17:27
After a couple tests I found that the code is stopping here: `if (!$stm) { echo "";` but I don't have any clue why — Anoushka Iola, Nov 15 '18 at 17:55
Nevermind you are correct, I had a problem on my query and fixed it now!! — Anoushka Iola, Nov 15 '18 at 18:06

SQL query group specific column value to send single e-mail for each user

1 Answers1