following original question, try rephrase make more clear:
i have two tables result of 2 queries ;
the tables have first column in common each row in first table can have more 1 correspondence in second.
i need iterate through each row in first table , create dynamic html table correspondent row(s) second.
this code tried gives result first row second table:
$stmt2 table1 , $stmt table2
$row2 = sqlsrv_fetch_array($stmt2, sqlsrv_fetch_assoc); foreach ($row2 $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 2 queries:
1 - $sql goes $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 [inprogress_wo_view] left join fournis2 f on a.fourni = f.fourni a.fourni <> 'null' order a.fourni asc"; 2 - $sql2 goes stmt2:
$sql2 = "select distinct a.fourni fournis2 left join [inprogress_wo_view] b on a.fourni = b.fourni [nu_int] <> 'null' order a.fourni"; i need to because need send email every row in first table information of second phpmailer
i hope i've been more precise , clear time :)
thank you
i answer own question because after hours of trying figured out logic , wrong.
this how able connect 2 tables, had insert in second query variable stored result first, this:
// table 1 query $sql2 = "select distinct a.fourni fournis2 left join [inprogress_wo_view] b on a.fourni = b.fourni [nu_int] <> 'null' order a.fourni"; // make resource 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], a.fourni, ad_email [inprogress_wo_view] left join fournis2 f on a.fourni = f.fourni a.fourni = " . "'" . $row2["fourni"] . "'" . // here assign variable table1, 2 have correspondence " order a.fourni asc"; after figured how send email every element in table1 correspondent result 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 not close after each email sent, reduces smtp overhead $mail->smtpsecure = "ssl"; //this important, forgot parameter first time , didn't send email, stuck in 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 not 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 fournis2 left join [inprogress_wo_view] b on a.fourni = b.fourni [nu_int] <> 'null' order a.fourni"; // creiamo la risorsa 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 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 [inprogress_wo_view] left join fournis2 f on a.fourni = f.fourni a.fourni = " . "'" . $row2["fourni"] . "'" . //assegniamo la varibile fornitore " order 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 not sent. <br />'; echo 'mailer error: ' . $mail->errorinfo . "<br />"; } else { echo 'message has been sent'; } } ?> 
Comments
Post a Comment