从sql查询结果创建动态html表用于与phpmailer邮件列表 [英] Create dynamic html table from sql query result to use for mailing list with phpmailer
本文介绍了从sql查询结果创建动态html表用于与phpmailer邮件列表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
按照我的原始问题，我尝试重新整理以使其更清楚：
我有这个两个表，它们是两个查询的结果;
我需要遍历第一个表中的每一行，然后创建第一个表中的每一行。一个动态html表与所有相应的行从第二个。
这是我尝试的代码，但它只给出作为结果从第二个表的第一行：
$ stmt2 用于table1和 $ stmt 是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> APP< / 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>;
？>
这两个查询：
$ stmt
<$ p <1>
$ sql $ p> $ 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 code> 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;
我需要这样做，因为我需要发送电子邮件到第一个表中的每一行，使用phpmailer的第二个信息
我希望我已经更精确和清楚了这一次：）
谢谢
解决方案
我会回答我自己的问题，因为经过几个小时的尝试，我想出了逻辑和什么是错误。 p>
这是我能够连接两个表，基本上我必须在第二个查询中插入存储第一个结果的变量，如下所示：
//表1查询
$ 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;
//我们为TABLE2创建资源
$ stmt2 = sqlsrv_query（$ conn，$ sql2）;
if（$ stmt2 === false）{
die（print_r（sqlsrv_errors（），true））;
}
// INIZIAMO IL WHILE 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] FOURNI，AD_EMAIL
FROM [INPROGRESS_WO_VIEW] A
LEFT JOIN
FOURNIS2 F
ON A.FOURNI = F.FOURNI
WHERE A.FOURNI =。 '。 $ row2 [FOURNI]。 '。 //这里我们分配可变的TABLE1，所以两个有一个对应
ORDER BY A.FOURNI ASC;
之后，我想出了如何为table1中的每个元素发送一封电子邮件，其中包含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连接不会在每次发送电子邮件后关闭，减少SMTP开销
$ mail-> SMTPSecure =ssl; //这很重要，我第一次忘了这个参数，它没有发送任何电子邮件，只是卡在循环
$ mail-> Port = 465;
$ mail->用户名='*******';
$ 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）{
echoConnection established。< br />;
} else {
echo无法建立连接。< br />;
die（print_r（sqlsrv_errors（），true））;
}
//查询CONENENO 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 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> APP。第三个第三个PROBL和第二个第三个第三个PROBL中的每一个具有相同的值。 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'无法发送消息。 < br />
echo'Mailer Error：'。 $ mail-> ErrorInfo。 < br />;
} else {
echo'Message has been sent';
}
}
？>
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';
}
}
?>
这篇关于从sql查询结果创建动态html表用于与phpmailer邮件列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
