• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 364
  • Last Modified:

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

0
Starquest321
Asked:
Starquest321
  • 18
  • 14
  • 5
1 Solution
 
Roger BaklundCommented:
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?
0
 
Ray PaseurCommented:
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
0
 
Ray PaseurCommented:
@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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Ray PaseurCommented:
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

0
 
Roger BaklundCommented:
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;
 
?>

Open in new window

0
 
Starquest321Author Commented:
Sorry for the late response - and thanks in advance for all the help. Here are the responses to the above comments:

1. The table of that lists the agents: contacts_agent
 
The table that lists the agents email is: member_email
 
I have attached several screen shots for you to see.

2-26-2009-11-10-36-AM.png
2-26-2009-11-12-00-AM.png
0
 
Starquest321Author Commented:
I just noticed that the screen shot didn't capture two important fields in Table1: That is Agent and Followup....:)
0
 
Roger BaklundCommented:
Are there three tables involved here? The table in your script is named "Contacts", the table in the first screenshot is named "contacts_agent", and the table in the second screenshot is named "member_email"?

How are the tables related? Which columns must be joined to fetch the agent email address? Is there a column named "Agent" in the "Contacts" table? Does it contain the agents name, or a numeric "member_id"?

What is the name of the column in the "member_email" table that stores the agent email address?
0
 
Roger BaklundCommented:
After re-reading your posts I now believe that the first screenshot is the "Contacts" table, is that correct? And the second table is named "contacts_agent"? And the column in the "contacts_agent" table that contains the email address is named "member_email"?

If so, my only remaining question is: What does the "Agent" column in the "Contacts" table contain? Is it the agents username, the agents first name, the agents last name, the agents full name or a numeric "member_id"?
0
 
Ray PaseurCommented:
I'm confused.  We have the Agent, Followup, and Email in table 1, and that table is called contacts_agent.  But you also say, "The table that lists the agents email is: member_email" -- please clarify.

What table has the AGENT EMAIL?
What table has the MEMBER EMAIL?
How do you know which MEMBERS are assigned to which AGENTS?

Please give us the table and field names.  Instead of posting screen shots, it might be more useful to post this kind of information in the code snippet.  Then we can copy the field names directly.

Thanks, ~Ray
0
 
Starquest321Author Commented:
I understand the confusion: The format I am using is Table_field...so Table is contacts, the field is agent.

Let me clarify again:

1. The agent email is in Member_email
2. Agents are members. . . so its not 2 tables.
3. The question here is which how do we know which contacts belong to which agent. That is decided by table contacts and the field agent.
 
0
 
Ray PaseurCommented:
The table is called "contacts" and inside that table there is a field called "agent" -Am I getting that right?
The table is called "member" and inside that table there is a field called "email"?

Can you please post the tables' schema in clear text in the code snippet?  Thanks.
0
 
Starquest321Author Commented:
Is there a way to do from phpmyadmin?
0
 
Roger BaklundCommented:
execute these queries:

SHOW CREATE TABLE Contacts

and

SHOW CREATE TABLE member
0
 
Roger BaklundCommented:
...or just answer this question: What does the "Agent" column in the "Contacts" table contain? Is it the agents username, the agents first name, the agents last name, the agents full name or a numeric "member_id"?
0
 
Starquest321Author Commented:
The table is called "contacts" and inside that table there is a field called "agent" -Am I getting that right?
The table is called "member" and inside that table there is a field called "email"?

YES AND YES :)
0
 
Starquest321Author Commented:
What does the "Agent" column in the "Contacts" table contain? Is it the agents username, the agents first name, the agents last name, the agents full name or a numeric "member_id"?

Answer: Agents first name.
0
 
Roger BaklundCommented:
Try this:
<?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);
    $followup_date = date('Y-m-d',$today);
 
    $mail['contact_list'] = array();
    $result = mysql_query("SELECT Contacts.*,member.email as 'member_email'
      FROM Contacts LEFT JOIN member ON member.member_firstname=Contacts.Agent 
      WHERE Followup = '$followup_date'");
    while($row = mysql_fetch_assoc($result)) {
        
        $agent = $row['member_email'];   
        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($agent>'')
              $recipient = $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;
 
?>

Open in new window

0
 
Starquest321Author Commented:
WOW: Thank you so much. Do I have to make any modifications on my side?
0
 
Starquest321Author Commented:
This is the error I got from the cron job:

<br />
<b>Warning</b>:  mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in <b>/home/israeli1/public_html/cron_followedup2.php</b> on line <b>20</b><br />
We do not have anyone to follow up on.
0
 
Roger BaklundCommented:
Run this in phpmyadmin, and post the error message:

SELECT Contacts.*,member.email as 'member_email'
      FROM Contacts LEFT JOIN member ON member.member_firstname=Contacts.Agent
      WHERE Followup = '2009-02-26'
0
 
Starquest321Author Commented:
Error
SQL query:  

SELECT Contacts . * , member.email AS 'member_email'
FROM Contacts
LEFT JOIN member ON member.member_firstname = Contacts.Agent
WHERE Followup = '2009-02-26'
LIMIT 0 , 30

MySQL said:  

#1054 - Unknown column 'member.email' in 'field list'
0
 
Starquest321Author Commented:
Should it be member_email? (What do I know...)
0
 
Starquest321Author Commented:
Here are two additional screenshots - this time from phpmyadmin. Maybe that will help.
The way Tables Contacts and Members are (or should be) connects are via the Agent field and the First name field.

Does this help?
Table-Contacts.png
Table-Member.png
0
 
Roger BaklundCommented:
Yes, the column is named "member_email", not "email".
Try this:
<?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);
    $followup_date = date('Y-m-d',$today);
 
    $mail['contact_list'] = array();
    $result = mysql_query("SELECT Contacts.*,member_email
      FROM Contacts LEFT JOIN member ON member.member_firstname=Contacts.Agent 
      WHERE Followup = '$followup_date'");
    while($row = mysql_fetch_assoc($result)) {
        
        $agent = $row['member_email'];   
        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($agent>'')
              $recipient = $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;
 
?>

Open in new window

0
 
Starquest321Author Commented:
IT WORKED!!!! You are wonderful. BUT A COUPLE OF FIXES:

1. I WOULD TO HARD CODE user@domain.com to recieve an email containing ALL FOLOWUPS regardless of the agent. (Meaning the supervisor sees all)
2. You can take off the member email showing on the button of the email.
3. Sometimes there are TWO Agents for a contact followup . . . .so BOTH agents need to get the alert. . .(I am not sure if I explained properly).
0
 
Roger BaklundCommented:
1. This can be done using a CC header for the email. I made a variable $supervisor at the start of the script.

2. Ok, this is done in the code below.

3. There is only one Agent column. Is this allways containing the name for a single person, or can it contain two names? If it is allways a single name, where is the name of the other agent stored? If it can be two names, how are they separated? (Comma, slash or something else?)
<?php
 
    $supervisor = 'user@domain.com';
 
    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);
    $followup_date = date('Y-m-d',$today);
 
    $mail['contact_list'] = array();
    $result = mysql_query("SELECT Contacts.*,member_email
      FROM Contacts LEFT JOIN member ON member.member_firstname=Contacts.Agent 
      WHERE Followup = '$followup_date'");
    while($row = mysql_fetch_assoc($result)) {
        
        $agent = $row['member_email'];   
        if(!isset($mail['contact_list'][$agent]))
          $mail['contact_list'][$agent] = '';
 
        foreach($row as $key=>$val){
            if($key=='member_email') continue;
            $mail['contact_list'][$agent] .= "<strong>". $key ."</strong>: " . $val ."<br/>";
        }
        $mail['contact_list'][$agent] .= "<hr/>";
        $count++;
    }
 
    $mail['header']  = 'CC: ' . $supervisor . "\r\n";
    $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($agent>'')
              $recipient = $agent;
          else 
              $recipient = $supervisor;  # used when the agent is unknown
          if()
          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;
 
?>

Open in new window

0
 
Roger BaklundCommented:
Sorry, line 52 must be removed.
0
 
Starquest321Author Commented:
I am posting to you an example of a record in the database - the agents are seperated by a comma: Ron, Meital...Do I have to do anything different in the code?
record.png
0
 
Roger BaklundCommented:
I think the code below should work. It sends email to both agents when two agents are associated with a contact.

Beware that this is not good db design. You should use the primary keys to join tables. This is a many-to-many relation, then there should be a third table to connect agents to contacts. What happens when you have two agents with the same first name? The way this is setup, you need to prevent that. If you have two persons named "John", you need to put "John2" in the first_name column for one of them to keep them separate, and also use "John2" in the Agent column in the Contacts table.
<?php
 
    $supervisor = 'user@domain.com';
 
    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);
    $followup_date = date('Y-m-d',$today);
 
    $mail['contact_list'] = array();
    $result = mysql_query("SELECT Contacts.*,m1.member_email as agent1,m2.member_email as agent2
      FROM Contacts 
      LEFT JOIN member m1 ON m1.member_firstname=substring_index(Contacts.Agent,',',1) 
      LEFT JOIN member m2 ON m2.member_firstname=substring_index(Contacts.Agent,',',-1)
      WHERE Followup = '$followup_date'");
    if(!$result) die(mysql_error());
    while($row = mysql_fetch_assoc($result)) {
        
        $agent1 = $row['agent1'];
        $agent2 = $row['agent2'];
        if(!isset($mail['contact_list'][$agent1]))
          $mail['contact_list'][$agent1] = '';
        if(!isset($mail['contact_list'][$agent2]))
          $mail['contact_list'][$agent2] = '';
 
        foreach($row as $key=>$val){
            if($key=='agent1') continue;
            if($key=='agent2') continue;
            $mail['contact_list'][$agent1] .= "<strong>". $key ."</strong>: " . $val ."<br/>";
            if($agent1 != $agent2)
              $mail['contact_list'][$agent2] .= "<strong>". $key ."</strong>: " . $val ."<br/>";
        }
        $mail['contact_list'][$agent1] .= "<hr/>";
        if($agent1 != $agent2)
          $mail['contact_list'][$agent2] .= "<hr/>";
        $count++;
    }
 
    $mail['header']  = 'CC: ' . $supervisor . "\r\n";
    $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($agent>'')
              $recipient = $agent;
          else 
              $recipient = $supervisor;  # 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;
 
?>

Open in new window

0
 
Starquest321Author Commented:
I agree. Please help me change that: What do you suggest I do?
0
 
Starquest321Author Commented:
ALso to make it a BCC to the suprivisor I just change it to this, right:

    $mail['header']  = 'BCC: ' . $supervisor . "\r\n";
0
 
Starquest321Author Commented:
Also I was thinking - In order to make this a better database design I want to make a new table called Agents. There I will put Agents, Emails, etc. Then we will relink the code the right way. How do you suggest I make the new table?
0
 
Starquest321Author Commented:
ALSO IF I DIDN'T MENTION IT- THANK YOU VERY MUCH. YOU REALLY HELPPED. ME. I WOULD LOVE TO HEAR MORE FROM YOU
0
 
Roger BaklundCommented:
Yes, you can change CC: to BCC: in the mail headers.

>> I want to make a new table called Agents. There I will put Agents, Emails, etc.

There is no problem with your existing tables, except for the way you now use the Contacts.Agent column. Instead of using the first names of the agents to connect to the member table, you should have a separate table to define this connection. A separate table is needed because this is a m:m relation (aka many-to-many). The new table could be something like this:

CREATE TABLE Agent_Contacts(
  member_id bigint not null,
  `index` bigint not null,
  primary key(member_id,`index`),
  unique key(`index`,member_id)
);

The column member_id would contain the member_id of the agent, and the "index" column would contain the "index" of the Contact. Note that I used backticks (`) for the column name "index" in the table, this is because this is a reserved word in mysql. I normally avoid using reserved words for column names, but in this case the primary key for the Contacts table is allready named "index". If it is not too much work because of many programs allready using this table, you should consider renaming these columns into something that is not a reserved word, for instance "contact_id".

Instead of using the Agent column an doing lookups based on the first name, you would use this new table for the lookup. The query used in the program above would be changed from this:

SELECT Contacts.*,m1.member_email as agent1,m2.member_email as agent2
      FROM Contacts
      LEFT JOIN member m1 ON m1.member_firstname=substring_index(Contacts.Agent,',',1)
      LEFT JOIN member m2 ON m2.member_firstname=substring_index(Contacts.Agent,',',-1)
      WHERE Followup = '$followup_date'

...into this:

SELECT Contacts.*,group_concat(member_email) as agent_email
      FROM Contacts NATURAL JOIN Agent_Contacts NATURAL JOIN member
      WHERE Followup = '$followup_date'
      GROUP BY Contacts.index

When two agents are associated with the same contact, instead of getting two email address columns, you would now get one column with two email addresses, separated by a comma. The script must be modified slightly to accomodate for this. You would need to change this (line 28-29):

        $agent1 = $row['agent1'];
        $agent2 = $row['agent2'];

...into this:

        @list($agent1,$agent2) = explode(',',$row['agent_email'],2);

...and lines 36-37:

            if($key=='agent1') continue;
            if($key=='agent2') continue;

...into this:

            if($key=='agent_email') continue;

The old Agent column in the Contacts table should be removed. But before you remove it, you must use it to populate the new Agent_Contacts table. Run these two queries to populate the new table:

insert into Agent_Contacts (`index`,member_id)
select `index`,member_id
from Contacts,member WHERE member_firstname=substring_index(Contacts.Agent,',',1)

insert ignore into Agent_Contacts (`index`,member_id)
select `index`,member_id
from Contacts,member WHERE member_firstname=substring_index(Contacts.Agent,',',-1)

After this is done, use the following query to get a list of all contacts and their associated agents:

select Contacts.*,member_firstname,member_email
from Contacts.NATURAL JOIN Agent_Contacts NATURAL JOIN member

This will return all contacts, and two rows for any contact that has two agents. Check that everything is correct, that the member_firstname column contains the same name as in the Agent column. When there are two names in the Agent column, there should also be two rows for that Contact, with each of the agents names in each row in the member_firstname column.

Ok, this was a rather lengthy post, this is a summary of what you need to do:

1) verify that you really want to do this, consider how many other related programs you will need to change
2) decide if you want to keep the "index" column name or change it
3) create the new table Agent_Contacts
4) populate the new table with two INSERT INTO statements
5) verify that everything is correct by using the SELECT query mentioned last above
6) modify the script:
    - change the SELECT in the script according to the first two SELECT queries mentioned above
    - change the code related to the old SELECT in the script (lines 28-29 and 36-37)
7) test the script
8) remove the Agent column from the Contacts table

If anything is unclear, don't hesitate to ask.
0
 
Roger BaklundCommented:
>> There is no problem with your existing tables

...well... there are room for improvements, but no real problems:

- rename the `index`column
- a normal integer would suffice for the keys, you don't need bigint
- use proper data types for all columns, use varchar and define maximum field lengths
- use a consistant naming convention (capital first letter in all or none of the column and table names)
- normalize:
  - city is dependant on zip and country, and should be in a separate table
  - state is dependant on zip and country, and should be in a separate table
  - member_type is repeated, it should be an integer, and the name of the type should be in a separate table
  - member_code is repeated, it should be an integer, and the name of the code should be in a separate table
  - Category is repeated, it should be an integer, and the name of the category should be in a separate table

None of these issues prevents you from having a working system. Most of them would make it easier to expand the system in the future.
0
 
Starquest321Author Commented:
You rock. . . thanks.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 18
  • 14
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now