从sql查询结果创建动态html表，用于phpmailer的邮件列表

Question

following my original question, I try to rephrase it to make it more clear:

I have this two tables that are the result of two queries ;

The tables have the first column in common but each row in the first table can have more than one correspondence in the second.

I need to iterate through each row in the first table and create a dynamic html table with all the correspondent row(s) from the second.

This is the code I tried but it only gives as result the first row from the second table:

$stmt2 is for table1 and $stmt is for table2

$row2 = sqlsrv_fetch_array($stmt2, SQLSRV_FETCH_ASSOC);
foreach ($row2 as $fornitori) {
    $fornitore = $row2['FOURNI'];

    //Intestazione della tabella uguale per tutti

    echo "<table><tr><th>ODL</th><th>INV</th><th>APP.</th><th>SERIALE</th><th>MODELLO</th><th>MARCA</th></tr>";
    while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
        if ($fornitore == $row['FOURNI']) {
                echo "<tr><td>".$row['NU_INT']."</td><td>".$row['NU_IMM']."</td><td>".$row['NOM_EQP']."</td><td>".$row['N_SERI']."</td><td>".$row['TYP_MOD']."</td><td>".$row['MARQUE']."</td></tr>";
            }
        }
    }
    echo "</table>";
?>

These are the two queries:

1 - $sql that goes to $stmt

$sql = "SELECT [NU_INT],[NU_IMM],[N_SERI],[NOM_EQP],[TYP_MOD],[MARQUE],NOM_UF],[NOM_ETAB],[DA_AP],[OBSERV],[OBSERV2],[LIB_STATUT], A.FOURNI, AD_EMAIL    
FROM [INPROGRESS_WO_VIEW] A    
LEFT JOIN    
FOURNIS2 F    
ON A.FOURNI = F.FOURNI    
WHERE A.FOURNI <> 'NULL'    
ORDER BY A.FOURNI ASC";

2 - $sql2 that goes to stmt2:

$sql2 = "SELECT DISTINCT A.FOURNI
FROM FOURNIS2 A
LEFT JOIN 
[INPROGRESS_WO_VIEW] B
ON A.FOURNI = B.FOURNI
WHERE [NU_INT] <> 'NULL'
ORDER BY A.FOURNI";

I need to to this because I need to send an email to every row in the first table with the information of the second with phpmailer

I hope I've been more precise and clear this time :)

Thank you

Answer 1

I will answer my own question because after hours of trying I figured out the logic and what was wrong.

This is how I was able to connect the two tables, basically I had to insert in the second query a variable that stored the result from the first, like this:

// TABLE 1 QUERY
$sql2 = "SELECT DISTINCT A.FOURNI
FROM FOURNIS2 A
LEFT JOIN 
[INPROGRESS_WO_VIEW] B
ON A.FOURNI = B.FOURNI
WHERE [NU_INT] <> 'NULL'
ORDER BY A.FOURNI";
// WE MAKE THE RESOURCE FOR TABLE2
$stmt2 = sqlsrv_query($conn, $sql2);
if( $stmt2 === false ) {
     die( print_r( sqlsrv_errors(), true));
}
// INIZIAMO IL WHILE A PARTIRE DALLO $STMT2
while ($row2 = sqlsrv_fetch_array($stmt2, SQLSRV_FETCH_ASSOC)) {            
            $sql = "SELECT [NU_INT],[NU_IMM],[N_SERI],[NOM_EQP],[TYP_MOD],[MARQUE],[NOM_UF],
            [NOM_ETAB],[DA_AP],[OBSERV],[OBSERV2],[LIB_STATUT], A.FOURNI, AD_EMAIL
            FROM [INPROGRESS_WO_VIEW] A
            LEFT JOIN
            FOURNIS2 F
            ON A.FOURNI = F.FOURNI
            WHERE A.FOURNI = " . "'" . $row2["FOURNI"] . "'" . // HERE WE ASSIGN THE VARIABLE FROM TABLE1, SO THE TWO HAVE A CORRESPONDENCE
            " ORDER BY A.FOURNI ASC";

After that I figured how to send an email for every element in table1 with the correspondent result from table2:

<?php

error_reporting(E_STRICT | E_ALL);

date_default_timezone_set('Etc/UTC');

require 'PHPMailerAutoload.php';

$mail = new PHPMailer;



$mail->isSMTP();
$mail->Host = '*********';
$mail->SMTPAuth = true;
$mail->SMTPKeepAlive = true; // SMTP connection will not close after each email sent, reduces SMTP overhead
$mail->SMTPSecure = "ssl"; //This is important, I forgot this parameter the first time and it didn't send any email, just stuck in a loop
$mail->Port = 465;
$mail->Username = '*******';
$mail->Password = '*******';
$mail->setFrom('******');
$mail->addReplyTo('******');




// DATI CONNESSIONE DATABASE
$serverName = "******"; //serverName\instanceName
$connectionInfo = array( "Database"=>"******", "UID"=>"******", "PWD"=>"******");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
// CONTROLLA SE LA CONNESSIONE AVVIENE CON SUCCESSO
if( $conn ) {
     echo "Connection established.<br />";
}else{
     echo "Connection could not be established.<br />";
     die( print_r( sqlsrv_errors(), true));
}

// QUERY CON ELENCO FORNITORI CHE HANNO ATTIVO UNO O PIU' INTERVENTI IN CORSO
$sql2 = "SELECT DISTINCT A.FOURNI
FROM FOURNIS2 A
LEFT JOIN 
[INPROGRESS_WO_VIEW] B
ON A.FOURNI = B.FOURNI
WHERE [NU_INT] <> 'NULL'
ORDER BY A.FOURNI";
// CREIAMO LA RISORSA A CUI POI ATTINGERA' IL PRIMO WHILE
$stmt2 = sqlsrv_query($conn, $sql2);
if( $stmt2 === false ) {
     die( print_r( sqlsrv_errors(), true));
}
// INIZIAMO IL WHILE A PARTIRE DALLO $STMT2
while ($row2 = sqlsrv_fetch_array($stmt2, SQLSRV_FETCH_ASSOC)) {
            // IMPOSTIAMO LA SECONDA QUERY $SQL ED ASSEGNIAMO IL FORNITORE A QUELLO CORRISPONDENTE NEL PRIMO WHILE
            $sql = "SELECT [NU_INT],[NU_IMM],[N_SERI],[NOM_EQP],[TYP_MOD],[MARQUE],[NOM_UF],
            [NOM_ETAB],[DA_AP],[OBSERV],[OBSERV2],[LIB_STATUT], A.FOURNI, AD_EMAIL
            FROM [INPROGRESS_WO_VIEW] A
            LEFT JOIN
            FOURNIS2 F
            ON A.FOURNI = F.FOURNI
            WHERE A.FOURNI = " . "'" . $row2["FOURNI"] . "'" . //ASSEGNIAMO LA VARIBILE FORNITORE
            " ORDER BY A.FOURNI ASC";

            $stmt = sqlsrv_query($conn, $sql);
            $mail->Subject = "URGENTISSIMO: SOLLECITO INVIO RT CHIUSURA INTERVENTI APERTI - " . $row2["FOURNI"];

            // HEADER TABELLA UGUALE PER TUTTI
            $body = "<html><head><style>
                     table, tr, td, th {
                        border: solid 1px;
                     }
                     th {
                        background-color: yellow;
                     }
                     </style>
                     </head>
                     <body>                      
                     <table><tr><th>ODL</th><th>INV</th><th>APP.</th><th>SERIALE</th><th>MODELLO</th><th>MARCA</th><th>PROBL.</th><th>JOB</th><th>DATA RICH.</th><th>REPARTO</th><th>PRESIDIO</th></tr>";

            while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {

                $body .= "<tr><td>".$row["NU_INT"]."</td><td>".$row["NU_IMM"]."</td><td>".$row["NOM_EQP"]."</td><td>".$row["N_SERI"]."</td><td>".$row["TYP_MOD"]."</td><td>".$row["MARQUE"]."</td><td>".$row["OBSERV"]."</td><td>".$row["OBSERV2"] . "</td><td>".$row["DA_AP"]. "</td><td>".$row["NOM_UF"]. "</td><td>".$row["NOM_ETAB"]."</td></tr>";
                $mail->addAddress($row["AD_EMAIL"]);
            }

            $body .= "</table>                        
                      </body>";

            $mail->msgHTML($body);

            if(!$mail->send()) {
                echo 'Message could not be sent. <br />';
                echo 'Mailer Error: ' . $mail->ErrorInfo . "<br />";
                } else {
                    echo 'Message has been sent';
                    }
}

?>