2. PHPRunner General questions
  3. trouble emailing query
This topic is locked

trouble emailing query

 		8/21/2007 5:17:10 AM
PHPRunner General questions
M
 morpheus author

Morning folks,
This may be slightly off topic but somebody here may be able to shed ome light on the problem I'm having, I have a database written with PHPrunner (best program I've ever used for this kind of thing <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=question&id=6045&image=1&table=forumtopics' class='bbc_emoticon' alt=';)' /> ) but I need to run a query that exports certain records from the database and emails them in table format to each person in the table.
I have a manually written php page (as shown below) which cycles through the records and adds them to an html formatted email and sends them to the record owner, this seems to be working fine apart from some of the users spam filters are catching the emails because they dont have a from address????? as far as I know I've set this correctly (or have I missed something stupid????)
The server is an ubuntu server running apache2 and php5 with mysql back end, the page loads and seems to run fine, it even sends the emails according to the postfix logs yet they still keep geting "spam filtered" at the client end, I'm assuming this is because the from email address is missing????
any suggestions??
regards
Jason

<?php

// Create Connection

$dbhost = 'localhost';

$dbuser = 'stockcontrol';

$dbpass = 'stockcontrol';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');

$dbname = 'stockcontrol';

mysql_select_db($dbname);
// Get date 7 days ago

$sevendaysago = date("Y-m-d H:i:s", strtotime("-1 days"));
// Get Engineer list into array

$result = mysql_query("SELECT FROM location WHERE (status_preset = 'ALLOCATED TO ENGINEER') AND (email <> '')");
// Loop through engineers getting stock more than 7 days old

while($row = mysql_fetch_array($result))

{

$result2 = mysql_query("SELECT FROM items WHERE (currentloc = 'TRUE') AND (location = '".$row['id']."') AND (amend_date < '".$sevendaysago."') AND (status = 'AVL' OR status = 'ALL' OR status = 'ALLOCATED TO ENGINEER' OR status = 'ALLOCATED TO ENGINEE')");

$row2 = mysql_fetch_array($result2);

// Build array into readable format $message
if ($row2['id']<>""){

$table = "<html>";

$table .= "<p>Hi $row[name],</p>";

$table .= "<p>The following is a list of stock that was allocated to you 7 or more days ago.
";

$table .= "Please could you check your current stock and notify the stores with any amendments required.</p>";

$table .= "
";

$table .= "
";

$table .= "<table width=\"600\" border=\"1\">"; // Start the table

$table .= "<tr>";

$table .= "<td width=\"100\"><strong>Item No.</strong></td>";

$table .= "<td width=\"350\"><strong>Description</strong></td>";

$table .= "<td width=\"200\"><strong>Date Issued</strong></td>";

while($row2 = mysql_fetch_array($result2))

{

$table .= "<tr><td>$row2[id]</td>";

$table .= "<td>$row2[description]</td>";

$table .= "<td>$row2[amend_date]</td>";

$table .= "</tr>";

}

$table .= "</table>"; // End the table

$table .= "</html>";

echo $table;
//ini_set("SMTP","localhost");

//ini_set("sendmail_from", "admin@localhost");

//send message

$to = $row['email'];

$subject = "Weekly Stock Check";

$message = "$table";

$from = "stores@comms-care.com";
if (strtoupper(substr(PHP_OS,0,3)=='WIN'))
$eol="\r\n";
elseif (strtoupper(substr(PHP_OS,0,3)=='MAC'))
$eol="\r";
else
$eol="\n";
// Now append $eol variable with header

$headers = "From: ".stripslashes($from)." < ".stripslashes($from_email).">".$eol;
//specify MIME version 1.0
$headers .= "MIME-Version: 1.0".$eol;
//unique boundary
$boundary = md5(uniqid(time()));
//tell e-mail client this e-mail contains//alternate versions
$headers .= "Content-Type: multipart/alternative" .
"; boundary =".$boundary.$eol;
//message to people with clients who don't

//understand MIME
$headers .= "This is a MIME encoded message.".$eol;
//HTML version of message
$headers .= "--".$boundary.$eol .
"Content-Type: text/html; charset=ISO-8859-1".$eol .
"Content-Transfer-Encoding: 8bit".$eol;
if (mail(stripslashes($to), stripslashes($subject), stripslashes($message), $headers)) {

echo $row['name'];

echo("<p>Email successfully sent!</p>");

} else {

echo $row['name'];

echo("<p>Message delivery failed...</p>");

}

}
}
// close connection

$conn = mysql_close()

?>
J
 		Jane 8/21/2007

Jason,
try to do the following:

  1. add reply-to parameter to the $header variable.
  2. set up sendmail_from parameter in the php.ini file.
S
 		slobbering_dog 8/23/2007

Hi Guys,
I've borrowed your php page - for use internally. Well done - this is great work.
However, I've noticed some funny results with the tables it produces. For instance : when I cut and paste the result2 query into MySQL it returns one record, but it doesn't create that record in the table for that particular user.
Have you experienced this ?
Also, it sometimes inserts an exclamation point '!' into the dates and sometimes puts data outside the table (usually at the beginning).
It emails every body is supposed to each time, but misses data in the result2 table build....
Here is my adaptation of your email page - perhaps I have stuffed something up.
[codebox]<?php
// Create Connection

$dbhost = 'mysql_server';

$dbuser = 'user';

$dbpass = 'user';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');

$dbname = 'Purchases';

mysql_select_db($dbname);
// Get list of users affected by po date changes into array

$result = mysql_query("SELECT DISTINCT so_user_id_code, email, name FROM po_date_changes INNER JOIN usertest ON so_user_id_code = user_id");
// Loop through recipients

while($row = mysql_fetch_array($result))

{

$result2 = mysql_query("SELECT * FROM po_date_changes WHERE (so_user_id_code = '".$row['so_user_id_code']."') ORDER BY so_cust_code, stock_code");

$row2 = mysql_fetch_array($result2);

// Build array into readable format $message
if ($row2['so_user_id_code']<>""){

$table = "<html>";

$table .= "<p>Hi $row[name],</p>";

$table .= "<p><strong> THIS IS A TEST EMAIL. </strong></p>";

$table .= "<p>The following is a list of stock that has had due dates on purchase orders changed in the last two weeks.
";

$table .= "Please check your orders and notify your customers accordingly.</p>";

$table .= "
";

$table .= "
";

$table .= "<table width=\"920\" border=\"1\">"; // Start the table

$table .= "<tr>";

$table .= "<td width=\"150\"><strong>PO Change Date</strong></td>";

$table .= "<td width=\"150\"><strong>Stock Code</strong></td>";

$table .= "<td width=\"100\"><strong>Cust Code</strong></td>";

$table .= "<td width=\"100\"><strong>SO Ord No</strong></td>";

$table .= "<td width=\"20\"><strong>BO</strong></td>";

$table .= "<td width=\"100\"><strong>Old Date</strong></td>";

$table .= "<td width=\"100\"><strong>New Date</strong></td>";
while($row2 = mysql_fetch_array($result2))

{

$table .= "<tr><td>$row2[log_date]</td>";

$table .= "<td>$row2[stock_code]</td>";

$table .= "<td>$row2[so_cust_code]</td>";

$table .= "<td>$row2[so_order_no]</td>";

$table .= "<td>$row2[so_bo_suffix]</td>";

$table .= "<td>$row2[old_date]</td>";

$table .= "<td>$row2[new_date]</td>";

$table .= "</tr>";

}

$table .= "</table>"; // End the table

$table .= "</html>";

echo $table;
//ini_set("SMTP","localhost");

//ini_set("sendmail_from", "admin@localhost");

//send message

$to = $row['email'];

$subject = "Daily Purchase Order Date Changes";

$message = "$table";

$from = "me@my.email.address";

$reply_to = "me@my.email.address";
if (strtoupper(substr(PHP_OS,0,3)=='WIN'))
$eol="\r\n";
elseif (strtoupper(substr(PHP_OS,0,3)=='MAC'))
$eol="\r";
else
$eol="\n";
// Now append $eol variable with header

$headers = "From: ".stripslashes($from)." < ".stripslashes($from).">".$eol;
// Append reply-to to header

$headers .="Reply-to:".stripslashes($reply_to)." < ".stripslashes($reply_to).">".$eol;
//specify MIME version 1.0
$headers .= "MIME-Version: 1.0".$eol;

//unique boundary
$boundary = md5(uniqid(time()));
//tell e-mail client this e-mail contains//alternate versions
$headers .= "Content-Type: multipart/alternative" .
"; boundary =".$boundary.$eol;
//message to people with clients who don't

//understand MIME
$headers .= "This is a MIME encoded message.".$eol;
//HTML version of message
$headers .= "--".$boundary.$eol .
"Content-Type: text/html; charset=ISO-8859-1".$eol .
"Content-Transfer-Encoding: 8bit".$eol;
if (mail(stripslashes($to), stripslashes($subject), stripslashes($message), $headers)) {

echo $row['so_user_id_code'];

echo("<p>Email successfully sent!</p>");

} else {

echo $row['name'];

echo("<p>Message delivery failed...</p>");

}

}
}
// close connection

$conn = mysql_close()

?>

[/codebox]
Like you Jason I am not experienced in these matters, but if we work together may be we can make a good thing even better !!
Cheers, <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=20771&image=1&table=forumreplies' class='bbc_emoticon' alt=':P' />
slobbering_dog
S
 		slobbering_dog 8/24/2007

Hi again,
I think I found something that has helped a bit :
[codebox]$result2 = mysql_query("SELECT * FROM po_date_changes WHERE (so_user_id_code = '".$row['so_user_id_code']."') ORDER BY so_cust_code, stock_code");

$row2 = mysql_fetch_array($result2);

mysql_data_seek($result2, 0);

[/codebox]
This seems to have put the pointer the beginning of the recordset before it tries to loop through the detail records.
Cheers,
slobbering_dog