asked on

Modify file

I have an excellent cron job php file which will email a record based on a "followup" database field for that record. I would like to modify that file so it will do so based on the AGENT - so if the agent is Debbie, email that record to Debbie, if John, then to John and so on. Please help.

<?php
 
    error_reporting(E_ALL ^ E_NOTICE);
    mysql_connect("localhost","xxxx","xxx");
    mysql_select_db("xxxx");
    mysql_query("SET NAMES 'utf8'");
 
    $count = 0;
 
    $today = time();
    $year = date("Y",$today);
    $month = date("m",$today);
    $day = date("j",$today);
    $mail_day = date("j F Y",$today);
 
 
    $mail['contact_list'] = "";
    $result = mysql_query("SELECT * FROM Contacts WHERE Followup <> ''");
    while($row = mysql_fetch_assoc($result)){
 
        $date = explode("-",$row['Followup']);
 
        if(($date[0] == $year) && ($date[1] == $month) && ($date[2] == $day)){
 
            foreach($row as $key=>$val){
                $mail['contact_list'] .= "<strong>". $key ."</strong>: " . $val ."<br/>";
            }
            $mail['contact_list'] .= "<hr/>";
            $count++;
        }
    }
 
    $mail['header']  = 'MIME-Version: 1.0' . "\r\n";
    $mail['header'] .= 'Content-type: text/html; charset=utf-8' . "\r\n";
    $mail['message'] = "
        <h1>Contacts FOLLOWUP</h1>
        <h3>Date: ". $mail_day ."</h3>
        <hr/>
        ". $mail['contact_list'] ."
    ";
 
    if($count != 0){
        $content = $count . " mails to sent.";
        mail("user@user.com","Contacts FOLLOWUP",$mail['message'],$mail['header']);
    } else {
        $content = "We do not have anyone to follow up on. ";
    }
 
    header ('Content-type: text/html; charset=utf-8');
    echo $content;
 
?>

Open in new window

Roger Baklund

Are the agent email addresses stored in the database? If so, what is the table and column names, and how are those records related to the Contacts table?

Ray Paseur

CRON jobs can't get input from any place but the system they are running on, so you must have (at least) the AGENT email address in the table you query.

Where the code now says, mail("user@user.com",... You will substitute the value from, for example, $row["agent_email_address"] into the mail command in place ofuser@user.com"

Does that make sense? ~Ray

Ray Paseur

@Starquest321: I started looking at this code and I'm wondering... Do you really have a data base table that has a date in it and a variable number of fields with email addresses? If so, you might want to consider changing it right now before you get too far down the design path. You would want to have two data fields (and perhaps a foreign key) - one for date and one for email address. Then your code and your future code will be easier to understand and implement.

Also, sending HTML email is a complicated task. If you start by writing this script to send plain-text email and then add the HTML part of it later, you will probably wind up with a more modular and easy-to-maintain code set.

Best regards, ~Ray

Ray Paseur

A simplified version. Now we just need to figure out where that pesky AGENT identification will come from!

Cheers...

<?php
// ASSUME A TABLE ORGANIZED LIKE THIS
/* *
 * key          AUTO_INCREMENT
 * ClientName   varchar
 * EmailAddress varchar
 * Followup     DATE
*/
 
// SEE ALL MESSAGES 
error_reporting(E_ALL ^ E_NOTICE);
 
// GET NEXT TO THE DB
mysql_connect("localhost","xxxx","xxx");
mysql_select_db("xxxx");
mysql_query("SET NAMES 'utf8'");
 
// SET LOCAL DATE VARS
$iso_date = date('Y-m-d');
$mail_day = date("j F Y");
 
// INITIALIZE THE CONTACT LIST 
$contact_list = array();
 
// CONSTRUCT THE QUERY
$sql = "SELECT * FROM Contacts WHERE Followup = '$iso_date'";
 
// RUN QUERY AND TEST FOR RESULTS
if (!$result = mysql_query($sql))
{
   $ermsg = 'QUERY FAIL: ' . "$qry\n";
   $ermsg .= mysql_errno();
   $ermsg .= mysql_error()'
   $ermsg .= '\n\n';
   mail ('you@your.org', 'ERROR IN CRON JOB', $ermsg);
   die();
}
 
// ITERATE OVER THE QUERY RESULTS AND GATHER THE CLIENT NAME + EMAIL
while($row = mysql_fetch_assoc($result))
{
   $nom = $row["ClientName"];
   $contact_list["$nom"] = $row["EmailAddress"];
}
 
// CONSTRUCT THE MESSAGE
$message  = '';
$message .= "CONTACTS FOLLOWUP FOR $mail_day\n\n";
foreach ($contact_list as $name => $email)
{
   $message .= "  $name $email \n";
}
// IF THERE WAS ANYTHING FOUND
if(count($contact_list) == 0)
{
   $message .= "We do not have anyone to follow up on. ";
}
 
// SEND MAIL
mail("[--AGENT--]","Contacts FOLLOWUP",$message);
 
?>

Open in new window

Roger Baklund

I am only guessing here, but maybe the below script would work. You must enter the email adresses of the agents at the start of the script.

<?php
 
    error_reporting(E_ALL ^ E_NOTICE);
    mysql_connect("localhost","xxxx","xxx");
    mysql_select_db("xxxx");
    mysql_query("SET NAMES 'utf8'");
 
    $count = 0;
    
    $agent_email = array(
      'Debbie' => 'debbie@company.com',
      'John' => 'john@company.com'
    );
 
    $today = time();
    $year = date("Y",$today);
    $month = date("m",$today);
    $day = date("j",$today);
    $mail_day = date("j F Y",$today);
    $followup_date = date('Y-m-d',$today);
 
    $mail['contact_list'] = array();
    $result = mysql_query("SELECT * FROM Contacts WHERE Followup = '$followup_date'");
    while($row = mysql_fetch_assoc($result)) {
        
        $agent = $row['agent'];   # is 'agent' the name of the agent column?
        if(!isset($mail['contact_list'][$agent]))
          $mail['contact_list'][$agent] = '';
 
        foreach($row as $key=>$val){
            $mail['contact_list'][$agent] .= "<strong>". $key ."</strong>: " . $val ."<br/>";
        }
        $mail['contact_list'][$agent] .= "<hr/>";
        $count++;
    }
 
    $mail['header']  = 'MIME-Version: 1.0' . "\r\n";
    $mail['header'] .= 'Content-type: text/html; charset=utf-8' . "\r\n";
    $mail['message'] = "
        <h1>Contacts FOLLOWUP</h1>
        <h3>Date: ". $mail_day ."</h3>
        <hr/>";
 
    if($count != 0){
        $content = $count . " followups sent to ".count($mail['contact_list'])." agents.";
        foreach($mail['contact_list'] as $agent=>$message) {
          if(isset($agent_email[$agent]))
              $recipient = $agent_email[$agent];
          else 
              $recipient = 'user@company.com';  # used when the agent is unknown
          mail($recipient,"Contacts FOLLOWUP",
               $mail['message'].$message,
               $mail['header']);
        }
    } else {
        $content = "We do not have anyone to follow up on. ";
    }
 
    header ('Content-type: text/html; charset=utf-8');
    echo $content;
 
?>