Hi, I have a table which have id, item and status. I want to send email if all item in id has “COMPLETE” status. I
My table look like this
For example in table, the email content should be display id for CUST_02 and CUST_03 as both have all complete status for all item.
I tried my code and manage to send email but the content is blank.
So what does $mail->Body actually contain? check with var_dump. Is you mailclient showing HTML mails anyway?
the mail body contain form_id
If the total_item doesn’t equal the total complete, you don’t appear to be adding a mail body.
if they are not equal, then the email would not be send
Based on the picture, rather than actual code, that isn’t what it says.
please help to guide me. I’m very suck in php.
This is my result on email
So, post code not an image
This is my code
<?php
require 'notifyUser/PHPMailerAutoload.php';
$mail = new PHPMailer;
// Connect with server DB
$serverName = "Server1"; //serverName\instanceName=
$connectionInfo = array( "Database"=>"Customer", "UID"=>"user", "PWD"=>"password");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn ) {
echo "Connection established. \n";
}else{
echo "Connection could not be established. \n";
die( print_r( sqlsrv_errors(), true));
}
$sql = "SELECT * FROM Report_List";
$query = sqlsrv_query( $conn, $sql);
$Form_IdList = array();
$total_item = array();
$total_complete = array();
while ($row = sqlsrv_fetch_array($query))
{
$Form_IdList = $row["Form_Id"];
$sql1 = "SELECT COUNT(item_id) as total_item
FROM tbl_detailitem
WHERE Form_Id='$Form_IdList'";
$query1 = sqlsrv_query($conn, $sql1);
while($row2 = sqlsrv_fetch_array($query1))
{
$total_item = $row2['total_item'];
}
$sql2 = "SELECT COUNT(item_id) as total_complete
FROM tbl_detailitem
WHERE Form_Id='$Form_IdList'
AND item_status= 'Complete'";
$query2 = sqlsrv_query($conn, $sql2);
while($row3 = sqlsrv_fetch_array($query2))
{
$total_complete = $row3['total_complete'];
}
if($total_item == $total_complete)
{
$html = '<table border="1">';
$html .= '<thead>';
$html .= '<tr>';
$html .= '<th>Form </th>';
$html .= '<tr>';
$html .= '<thead>';
$html .= '<tbody>';
while($row4 = sqlsrv_fetch_array($result))
{
$form = $row4['Form_Id'];
$html .= '<tr>';
$html .= '<td>' . $form . '</td>';
$html .= '</tr>';
}
$html .= '</tbody>';
$html .= '</table>';
$table_CK = $html;
}
}
try
{
//Server settings
$mail->isSMTP(); // Set mailer to use SMTP
$mail->Host = 'smtp1.example.com'; // Specify main and backup SMTP servers
$mail->SMTPAuth = false; // Enable SMTP authentication
$mail->Username = '[email protected]'; // SMTP username
$mail->Password = ''; // SMTP password
$mail->SMTPSecure = 'tls'; // Enable TLS encryption, `ssl` also accepted
$mail->Port = 25; // TCP port to connect to
//Recipients
$mail->setFrom('[email protected]');
$mail->addAddress('[email protected]');
// Content
$mail->isHTML(true);
$mail->Subject = 'Report Complete';
$mail->Body = '<p>Below report(s) have completed for their finding. </p>'.$table_CK.' ';
$mail->send();
echo 'Message has been sent';
}
catch (Exception $e)
{
echo "Message could not be sent. Mailer Error: {$mail->ErrorInfo}";
}
sqlsrv_close($conn);
$db = null;I’ve reformatted your code to make it cleaner to see what it is doing.
Where is the query for this loop?
while($row4 = sqlsrv_fetch_array($result))
{
$form = $row4['Form_Id'];
$html .= '<tr>';
$html .= '<td>' . $form . '</td>';
$html .= '</tr>';
}
This can be cleaned, I just don’t have enough information on the data model. A summary guess, something along these lines,
SELECT DISTINCT
form_id
, COUNT(case when item_status = 'COMPLETE' then 1 END) OVER(PARTITION BY item_id) as total_complete
,COUNT(item_id) OVER(PARTITION BY item_id) as total_item
FROM detailitem
This will give you a complete working record set. However, if you are just after the completed = total, that should be specified in the where clause and you will only iterate over those, making the performance improve drastically depending on your table size.
The result of sql result is like this right?
then how i suppose to know if the total_complete = total item?
Verify it a few times to see. The query should give the total items and the total complete so you get a breakdown. Based on the result image, it needs tweaking, but I said I don’t know your data model.