CODEWITHSUNDEEP
phpsqlsql-server
Anoushka Iola
Anoushka Iola

Reputation: 9

How to group equal information from query sql result in php

I have some data from two tables, and the are connected by the column PersonId and each person can have multiple cards, and I need to have one output like:

Card 1 expiry date XX-XX-XX
Card 4 expiry date XX-XX-XX
E-mail: [email protected]
Card 2 expiry date YY-YY-YY
Card 3 expiry date YY-YY-YY
E-mail: [email protected]

Instead of what I'm getting which is the email for every card.

Card 1 expiry date XX-XX-XX
E-mail: [email protected]
Card 4 expiry date XX-XX-XX
E-mail: [email protected]
Card 2 expiry date YY-YY-YY
E-mail: [email protected]
Card 3 expiry date YY-YY-YY
E-mail: [email protected]

I need it because I will send emails with the info, and I don't want to send one e-mail to each card, I will have to make one function to send all cards of the same client in one e-mail.

My sql query is the following:

SELECT Crt.PersonId, Crt.CrtType, Crt.CrtNr, Crt.CrtValidFrom, 
        Crt.CrtValidUntil, Crt.CrtLastTNr, PersonAdd.PersonId AS Expr1, 
        PersonAdd.Email, Crt.CodDate
FROM Crt 
    INNER JOIN PersonAdd ON Crt.PersonId = PersonAdd.PersonId
WHERE (Crt.CrtValidUntil <= CAST(DATEADD(DD, 7, GETDATE()) AS Date))
ORDER BY Crt.CrtNr ASC

And if necessary I'm testing the output with this code: 

while ($rows = $stm->fetch())
    {
        $CPersonUId = isset($rows['PersonId']) ? $rows['PersonId'] : NULL;
        $CType = isset($rows['CrtType']) ? $rows['CrtType'] : NULL;
        $CNr = isset($rows['CrtNr']) ? $rows['CrtNr'] : NULL;
        $CValFrom = isset($rows['CrtValidFrom']) ? $rows['CrtValidFrom'] : NULL;
        $CValUntil = isset($rows['CrtValidUntil']) ? $rows['CrtValidUntil'] : NULL;
        $CLTCCTime = isset($rows['CrtLastTNr']) ? $rows['CrtLastTNr'] : NULL;
        $CLTPEmail = isset($rows['Email']) ? $rows['Email'] : NULL;


        echo "Card nº<b>".$CNr."</b> valid until <b>".$mytime1."</b></br>";
        echo $CLTPEmail."</br>";

    }

And the results are the following:

Card nº1 valid until 15/11/2018
[email protected]
Card nº2 valid until 15/11/2018
[email protected]

Instead of:

Card nº1 valid until 15/11/2018
Card nº2 valid until 15/11/2018
[email protected]

Upvotes: 0

Views: 85

Answers (2)

RiggsFolly
RiggsFolly

Reputation: 94672

A simple variable to remember which email you are dealing with should solve this nicely

$last_email = null;

while ($rows = $stm->fetch()) {

    $CPersonUId = isset($rows['PersonId']) ? $rows['PersonId'] : NULL;
    $CType = isset($rows['CrtType']) ? $rows['CrtType'] : NULL;
    $CNr = isset($rows['CrtNr']) ? $rows['CrtNr'] : NULL;
    $CValFrom = isset($rows['CrtValidFrom']) ? $rows['CrtValidFrom'] : NULL;
    $CValUntil = isset($rows['CrtValidUntil']) ? $rows['CrtValidUntil'] : NULL;
    $CLTCCTime = isset($rows['CrtLastTNr']) ? $rows['CrtLastTNr'] : NULL;
    $CLTPEmail = isset($rows['Email']) ? $rows['Email'] : NULL;

    if ( $last_email == null ) {
        $last_email = $CLTPEmail;
    }
    if ( $CLTPEmail != $last_email ) {
        echo $last_email . "</br>";
        $last_email = $CLTPEmail;
    }

    echo "Card nº<b>".$CNr."</b> valid until <b>".$mytime1."</b></br>";

}
// put out the last email 
echo $last_email . "</br>";

Upvotes: 0

kifni41
kifni41

Reputation: 76

If you really want to keep your query, then you'll have to use php to manipulate the result and put it together inside PHP object or array.

i usually use php assoc array to do something like that 

$data = [];
while ($rows = $stm->fetch())
{
   if(isset($data[$rows['PersonId']]) {
     //use personId as the array key
     $data[$rows['PersonId']] = ['email' => $rows['Email'], 'cardList'=> [] ];
   }
   //add cardNr to CardList property wich also an array
   $data[$rows['personId']] ['cardList'] [] = $rows['CrtNr'];
}

//check using print_r
print_r($data);

But you can actually use the query to get similar result, using GROUP BY and some GROUP CONCAT to cobine value from multiple fields, in SQL server group concat is similar to STRING_AGG .

Don't take my query as it is, since i don't tested it on Sql server, don't have have, but the query should similar to this

SELECT Crt.PersonId, Crt.Email,
STRING_AGG(CONCAT(CrtNr.,'#',CrtValidFrom,'#',CrtValidUntil ), ',') as card_info
FROM Crt 
INNER JOIN PersonAdd ON Crt.PersonId = PersonAdd.PersonId
WHERE (Crt.CrtValidUntil <= CAST(DATEADD(DD, 7, GETDATE()) AS Date))
GROUP BY Crt.Email
ORDER BY Crt.CrtNr ASC

STRING_AGG is similar function to GROUP CONCAT in Mysql https://database.guide/the-sql-server-equivalent-to-group_concat/

Upvotes: 1

Related Questions