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
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';
}
}
?>