2. PHPRunner General questions
  3. Array of values in html email
This topic is locked

Array of values in html email

 		9/6/2012 4:58:34 PM
PHPRunner General questions
C
 copper21 author

Hello,
I am hoping I can get some help here. What I am trying to do is send 1 email to a person and within that email display multiple records; kind of what the "List" page would display. I will not be using an event from the events page, but rather just a php script using DAL that will be sent out using the Task Schduler in Windows server. I have several php scripts that work great this way, but what I would like to add to these is a list of values from several lines in the database. The way it works now is sending an email for every record I am querying. The problem is 1 person could get 100 emails at one time. I want to send 1 email listing the 100 records. Here is what I have right now:

<?php
include("include/dbcommon.php");
//Send email 2 months before recert expiration.
global $dal;

$sql = "SELECT * FROM Table1 WHERE email_notification_number = '2' ";

$rs = CustomQuery($sql);

$data = db_fetch_array($rs);
$sql = "SELECT * FROM Table2 WHERE responsible_wl LIKE '%".$data['wl']."%' ";

$rs1 = CustomQuery($sql);
while($data1 = db_fetch_array($rs1))
{
// Date formats
$recertdate = $data['recert'];
$date1 = date("l, F d, Y",strtotime($recertdate));
//email
$email= $data1["email"];
$msg="";
$msg.="<html>
<head>

<title>My Title</title>

</head>

<body> <table width=\"800\" border=\"0\" cellspacing=\"0\" cellpadding=\"10\">

<tr>

<td>

<h2>Big Title Area.</h2>
<p>".$data1["name"].",</p>
<p> Some text. </p>
<p> Some more text.</p>
<p> Even More Text: </p>
<table border=\"0\" width=\"800\" cellspacing=\"10\" cellpadding=\"0\">
//***RESULTS WOULD GO HERE***
</table>
</td>

</tr>

</table>

</body>

</html> ";
$subject="My Subject.";
$from="email@email.com";
$ret=runner_mail(array('to' => $email, 'subject' => $subject, 'htmlbody' => $msg, 'from'=>$from));
if(!$ret["mailed"])
{echo $ret["message"];}
}
?>


Right now this sends 1 email to each person from which the query gets data. I need to list the values from Table1, that being several fields per row of results.
Thanks for your help!
Brian
Admin 9/7/2012

I'll try to show the idea with this sample code. Prepare message header outside the loop, then loop through results, add message footer and send the email once.

<?php
include("include/dbcommon.php");
//Send email 2 months before recert expiration.
global $dal;

$sql = "SELECT * FROM Table1 WHERE email_notification_number = '2' ";

$rs = CustomQuery($sql);

$data = db_fetch_array($rs);
$sql = "SELECT * FROM Table2 WHERE responsible_wl LIKE '%".$data['wl']."%' ";

$rs1 = CustomQuery($sql);
$msg.="<html>
<head>

<title>My Title</title>

</head>

<body> <table width=\"800\" border=\"0\" cellspacing=\"0\" cellpadding=\"10\">

<tr>

<td>

<h2>Big Title Area.</h2>";
while($data1 = db_fetch_array($rs1))

{
// Date formats
$recertdate = $data['recert'];
$date1 = date("l, F d, Y",strtotime($recertdate));
//email
$email= $data1["email"];
$msg.="<p>".$data1["name"].",</p>
<p> Some text. </p>
<p> Some more text.</p>
<p> Even More Text: </p>
<table border=\"0\" width=\"800\" cellspacing=\"10\" cellpadding=\"0\">
//***RESULTS WOULD GO HERE***

";
}
$msg.= "</table>
</td>

</tr>

</table>

</body>

</html> ";
$subject="My Subject.";
$from="email@email.com";
$ret=runner_mail(array('to' => $email, 'subject' => $subject, 'htmlbody' => $msg, 'from'=>$from));
if(!$ret["mailed"])
{echo $ret["message"];}
}
?>
C
 		copper21 author 9/7/2012

Sergey,
Thanks for the help. I am successfully getting the email to send; 1 per time. The trouble I am having is also putting the, rows of data that is coming from "Table 1" into the email itself. At the top, I am using Table 1 to query Table 2 which generates the list of people receiving the email, but I also do need the rows of data fetched from Table 1 to be included in the email itself. So if the first query (Table 1) fetches 3 rows that have an email_notification_number of 2, I need to show those rows in the email itself. Any guidance on how I can do that? Should there be a "foreach" in there?
Thanks!
Brian
Admin 9/7/2012

There is already a 'while' loop in your code. The code inside this loop will be executed once for each record your query returns. Inside this loop you can use $data1["fieldname"] to access any specific field value.
C
 		copper21 author 9/7/2012

Thank you, I give it a try!
Brian