Going Blind with PHP Email & SQL While Variables

Email While Variables

[pcguysam]

I have a site with multiple virtual domains. I am trying to write a script that will go across those domains, pick out user preferences where users want a daily email appointment reminder and email that users daily appointments to them in one email.

Here is what I have currently:
<?php

//define the path as relative
date_default_timezone_set('America/Chicago');
$path = "/some/path/to";

//using the opendir function
$dir_handle = @opendir($path) or die("Unable to open $path");

//running the while loop
while ($file = readdir($dir_handle))
{
if($file!="." && $file!="..") {
include ('../some/path/'.$file.'');

$today = date("Y-m-d");

$getemail = "Select users.username, users.UserID as UID, UserSettings.dailyemail FROM users INNER JOIN UserSettings on users.UserID = UserSettings.UserID WHERE Date='$today' and UserSettings.dailyemail=1";
$showemail = mysql_query($getemail) or die (mysql_error());
while ($row = mysql_fetch_assoc($showemail)) { {
$email = $row['username'];
$ID = $row['UID'];
}
$to = $email;

$getinteractions = "SELECT Interactions.*, Companies.*, Contacts.*, users.username, users.UserID as UID FROM ((Interactions INNER JOIN Contacts ON Interactions.ContactID = Contacts.ContactID) INNER JOIN Companies ON Interactions.CompanyID = Companies.CompanyID) INNER JOIN users ON Interactions.AddedBy=users.UserID WHERE Interactions.AddedBy = $ID";
$interactions = mysql_query($getinteractions) or die(mysql_error());

while ($row2 = mysql_fetch_assoc($interactions)) { {
$emails = $row2['username'];
$time = $row2['Time'];
$type = $row2['Type'];
$location = $row2['Location'];
$attendees = $row2['Attendees'];
$notes = $row2['Notes'];
$CID = $row2['CompanyID'];
$CONID = $row2['ContactID'];
$CN = $row2['CompanyName'];
$ADDR = $row2['Address1']."-".$row2['Address2'];
$CSZ = $row2['City'].",".$row2['State']." ".$row2['Zip'];
$Phone1=$row2['Phone'];
if ($Phone1 != NULL) {
$CPH="(".substr($Phone1,0,3).") ".substr($Phone1,3,3)."-".substr($Phone1,6,4);
} else {
$CPH=NULL; }
$ConName = $row2['FirstName']." ".$row2['LastName'];
$Phone1=$row2['Phone1'];
if ($Phone1 != NULL) {
$ConPH="(".substr($Phone1,0,3).") ".substr($Phone1,3,3)."-".substr($Phone1,6,4);
} else {
$ConPH=NULL; }
$Phone2=$row2['Phone2'];
if ($Phone2 != NULL) {
$ConPH2="(".substr($Phone2,0,3).") ".substr($Phone2,3,3)."-".substr($Phone2,6,4);
} else {
$ConPH2=NULL; }
$ConEM = $row2['Email1'];
}
$body = "$time<br>$CN<br>$ADDR<br>$CSZ<br>$CPH<br><br>$ConName - $ConPH - <a href='mailto:$ConEM'>$ConEM</a><br>$location<br>$notes<br><hr><br>";

date_default_timezone_set('America/Chicago');
$dte = date("m/d/Y");
$subject = "Interactions for Today $dte";
$random_hash = md5(date('r', time()));
$headers = "From: [e-mail]\r\nReply-To: [e-mail]";
//add boundary string and mime type specification
$headers .= "\r\nContent-Type: multipart/alternative; boundary=\"PHP-alt-".$random_hash."\"";

//define the body of the message.
ob_start(); //Turn on output buffering
?>

--PHP-alt-<?php echo $random_hash; ?>
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

<?php echo $body; ?>

--PHP-alt-<?php echo $random_hash; ?>--
<?
//copy current buffer contents into $message variable and delete current output buffer
$message = ob_get_clean();

mail($to,$subject,$message,$headers);
}

}}
closedir($dir_handle);

?>

I can't get it to loop quite right. I either get the same email going out to everyone, or I get multiple emails going to the correct person. All I want is for each person's appointments to go to them in one email.

[pcguysam]

Figured it out myself. I had to move the $body loop inside the while statement for the $email loop. I was thinking about it backwards.

<?php
//define the path as relative
date_default_timezone_set('America/Chicago');
$path = "/some/dir/admin";

//using the opendir function
$dir_handle = @opendir($path) or die("Unable to open $path");

//running the while loop
while ($file = readdir($dir_handle))
{
if($file!="." && $file!="..") {
include ('some/dir/'.$file.'');

$today = date("Y-m-d");

$getemail = "Select users.username, users.UserID as UID, UserSettings.dailyemail FROM users INNER JOIN UserSettings on users.UserID = UserSettings.UserID WHERE UserSettings.dailyemail=1";
$showemail = mysql_query($getemail) or die (mysql_error());
while ($row = mysql_fetch_assoc($showemail)) { {
$email = $row['username'];
$ID = $row['UID'];
$getinteractions = "SELECT Interactions.*, Companies.*, Contacts.*, users.username, users.UserID as UID FROM ((Interactions INNER JOIN Contacts ON Interactions.ContactID = Contacts.ContactID) INNER JOIN Companies ON Interactions.CompanyID = Companies.CompanyID) INNER JOIN users ON Interactions.AddedBy=users.UserID WHERE Date='$today' and Interactions.AddedBy = $ID";
$interactions = mysql_query($getinteractions) or die(mysql_error());
$body = null;
if (mysql_num_rows($interactions) < 1) { $body = "You have no interactions scheduled for today.";}
while ($row2 = mysql_fetch_assoc($interactions)) { {
$emails = $row2['username'];
$time = $row2['Time'];
$type = $row2['Type'];
$location = $row2['Location'];
$attendees = $row2['Attendees'];
$notes = $row2['Notes'];
$CID = $row2['CompanyID'];
$CONID = $row2['ContactID'];
$CN = $row2['CompanyName'];
$ADDR = $row2['Address1']."-".$row2['Address2'];
$CSZ = $row2['City'].",".$row2['State']." ".$row2['Zip'];
$Phone1=$row2['Phone'];
if ($Phone1 != NULL) {
$CPH="(".substr($Phone1,0,3).") ".substr($Phone1,3,3)."-".substr($Phone1,6,4);
} else {
$CPH=NULL; }
$ConName = $row2['FirstName']." ".$row2['LastName'];
$Phone1=$row2['Phone1'];
if ($Phone1 != NULL) {
$ConPH="(".substr($Phone1,0,3).") ".substr($Phone1,3,3)."-".substr($Phone1,6,4);
} else {
$ConPH=NULL; }
$Phone2=$row2['Phone2'];
if ($Phone2 != NULL) {
$ConPH2="(".substr($Phone2,0,3).") ".substr($Phone2,3,3)."-".substr($Phone2,6,4);
} else {
$ConPH2=NULL; }
$ConEM = $row2['Email1'];
}
if ($body == null) {
$body = "$file<br>$time<br>$CN<br>$ADDR<br>$CSZ<br>$CPH<br><br>$ConName - $ConPH - <a href='mailto:$ConEM'>$ConEM</a><br>$location<br>$notes<br><hr><br>";
}
$body .= "$file<br>$time<br>$CN<br>$ADDR<br>$CSZ<br>$CPH<br><br>$ConName - $ConPH - <a href='mailto:$ConEM'>$ConEM</a><br>$location<br>$notes<br><hr><br>";
}

$to = $email;
date_default_timezone_set('America/Chicago');
$dte = date("m/d/Y");
$subject = "Interactions for Today $dte";
$random_hash = md5(date('r', time()));
$headers = "From: [e-mail]\r\nReply-To: [e-mail]";
//add boundary string and mime type specification
$headers .= "\r\nContent-Type: multipart/alternative; boundary=\"PHP-alt-".$random_hash."\"";

//define the body of the message.
ob_start(); //Turn on output buffering
?>

--PHP-alt-<?php echo $random_hash; ?>
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

<?php echo $body; ?>

--PHP-alt-<?php echo $random_hash; ?>--
<?
//copy current buffer contents into $message variable and delete current output buffer
$message = ob_get_clean();

mail($to,$subject,$message,$headers);
}
}

}}
closedir($dir_handle);

?>

[coopster]

Glad you got it sorted and welcome to WebmasterWorld, pcguysam!