Need PHP Script to Receive E-mail Forwarded by Outlook Users, and the PHP Script Will Save the E-mail to a Database

I've seen some posts on EE about pipe scripts that can receive an e-mail and saved it to a database, but I have not been able to understand exactly how to do it.  I'm using PHP 5 and MySQL 5 on a Unix (FreeBSD) server.
    Here's the scenario of what I need to do:  

        The user receives an e-mail in Outlook. It relates to a certain sales account, so he wants to store it in a MySQL database record relating to that sales account.
     He clicks "forward" and puts in a special e-mail address (that leads to the PHP script).  And he puts the account number as the first thing in the subject line.
     When the forwarded mail reaches that special PHP-connected address, the PHP script reads the account number from the subject line, so it knows what account to save it to.
     PHP saves the message body and any attachments into MySQL (presumably the message body would be saved to a text field, and any attachments would be saved to BLOB fields?).
     
     This is a standard Unix server with PHP as an Apache module. It is a commercially-hosted shared server, so I don't have a lot of control over special settings.

      Please explain _in detail_ how to accomplish this.  I'm offering maximum points, as I need a full, detailed explanation and would appreciate sample code.
FrankTechAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
nplibConnect With a Mentor Commented:
Well, unless you have direct access to the email sub systems, such as the sendmail command on the freeBSD server, or if you have direct access to the SMTP server, then you can do it.

Other wise, you can't.

you can ask your hosting company to forward all emails going to any email address you choose like php@yourcompany.com to your email2php.php script, you will need to provide them with the absolute path however.

check out http://www.evolt.org/article/Incoming_Mail_and_PHP/18/27914/ at step 3 how to setup up the php script.

once you get it into variables, you can do what you want with the variables, like put them into a database.
0
 
nplibCommented:
As far as I know, this can't be done.
0
 
FrankTechAuthor Commented:
Thanks, but I recall reading some posts (which I can't seem to find today) stating that it can be done, by piping the e-mail to the PHP script (?)
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
FrankTechAuthor Commented:
Here's an example of the kind of thing I remember seeing, but it's too vague for me to implement: http://xujinjun.spaces.live.com/Blog/cns!A0B1BA972617D489!136.entry
0
 
GawaiCommented:
yeah it cant be ...but u can achieve it in a diff way.

The user receives an e-mail in Outlook. It relates to a certain sales account, so he wants to store it in a MySQL database record relating to that sales account.

>>to store in a database, you need  form ...preserv all important informations in hidden fields

     He clicks "forward" and puts in a special e-mail address (that leads to the PHP script).  And he puts the account number as the first thing in the subject line.
     
>>provide a link/submit button on email so that users will click on that..instead of forwarding option.


rest will be done easily
 
0
 
nplibCommented:
You  can only do it at the server level.

are these emails coming from a website?
from any old person?
do you have access to the server?
web or email?
0
 
FrankTechAuthor Commented:
Also this one talks about it but doesn't explain how to do it in php:
   http://www.modwest.com/help/kb9-275.html
0
 
FrankTechAuthor Commented:
Also just ran across this: http://www.email2php.com/Homepage and this: http://www.email2php.com/HowItWorks - just to show that it can be done, including attachments.
    But I want to use my existing server, instead of buying a separate service (which has some tight limitations on transfer quotas, etc.).
    Anyone know how to do this?
0
 
nplibCommented:
if you answer the questions I asked, I can give you a solution.
But depends on the questions I've asked.

oh and I forgot to ask, is this a Linux or a Windows server.
0
 
FrankTechAuthor Commented:
One of the pages linked above states:
   "You might also want to deploy a Perl or PHP script on a regular schedule that examines your mailbox and manipulates your email in any way you desire, such as adding messages to a MySQL database . . . ."
    So how would that be done?
0
 
nplibCommented:
It all depends on the answers I need.
Answer my questions or I stop helping.
0
 
FrankTechAuthor Commented:
nplib,
   It is a Unix (FreeBSD) server which is a shared commercial host.
   >> are these emails coming from a website?
No, they are coming from employees who would be authorized to forward (to the PHP script) certain e-mails that they had received in Outlook, related to some specific sales account.

>> from any old person?
No, only from employees who would be trained to put the account # as the 1st thing in the Subject line of the e-mail, and to forward it to a special address.

>>do you have access to the server?
Yes, to some degree; it's a shared host, where I can do a lot but cannot manipulate the finer settings of PHP, etc.

>>web or email?
Users need to forward email messages (including attachments) that they had received in Outlook. For example, user A receives an Outlook message about Account 555555555.  He clicks "forward" in Outlook, puts "555555555" in the subject line, and clicks Send.
     When the PHP script on my server receives it, it should save the message body to a text field in MySQL, and save any attachments to the appropriate kind of field (and also save data such as the sender's name to a "sender's name" field, the time it was forwarded, etc.--by parsing info from the message).
0
 
FrankTechAuthor Commented:
nplib,
   Sorry; I wasn't ignoring your questions; I just didn't see them yet while I was posting other links.
   In case my response above is not clear, it's a UNIX FreeBSD server hosted by a commercial web hosting company as a shared host. Thanks.
0
 
FrankTechAuthor Commented:
   Yes, I think I have access to the sendmail command. I've certainly used sendmail to send e-mail from Perl and PHP scripts. And I have shell access of some kind (I can telnet into the Unix prompt of my hosting account).
    From the article you posted above, it sounds PHP must be running as a CGI program, rather than an Apache module; and my host has both (PHP4 is CGI, whereas PHP5 is an Apache module).
     Does it sound like I have all the right equipment to try this?
    Also, what about attachments? A user note after the article hints that it can handle attachments but does not explain exactly how to do it.  Can you explain it?
0
 
nplibCommented:
if you can get access to the .forward file on your FreeBSD server
here is how to make the piping
http://activecampaign.com/support/tt/index.php?action=kb&article=145

the .forward file will be a hidden file, and it's usually somewhere in /etc or lower
0
 
FrankTechAuthor Commented:
The previous article and the one you just sent both say the .forward file should go into the "main home directory."  Does that mean the www directory where index.html is?
    I have tried that, and I'm not getting any results; no error messages, nothing.  I followed the steps in the previous article.  I hope the pipe function is not disabled on my shared host (like it is on many shared hosts, apparently).
    If I can't get this to work, I may need to take the alternative approach of having a cron job run a script that will check the special mail box that I established on my shared hosting server.  I saw a Perl script that can do that.  How would PHP do it?
   
    I will be out at a meeting for a couple of hours. When I get back I'll follow up further. Thanks.
0
 
nplibCommented:
I think you just have to edit the once that exists.

it would be in the root users home dir.

thus I don't think you can do it with your current setup.
0
 
FrankTechAuthor Commented:
Update: My hosting company says the .forward file goes in the directory that I see when I first FTP into the account (it's the directory above the Web pages directory).  
     I've tried all kinds of things that I read about in other questions here on EE, still, nothing happens.  There is no error message, or anything. I'll give another update later today.
0
 
FrankTechAuthor Commented:
I've concluded there must be something about my host's server configuration that just isn't allowing this to work.  
     So I'm going to try on another host (A2), where the company specifically says it will work: https://support.a2hosting.com/index.php?_m=knowledgebase&_a=viewarticle&kbarticleid=427&nav=0,25
    I'll report back in a few hours after I get it set up and test it.

0
 
FrankTechAuthor Commented:
nplib,
     Hooray!  It works at A2hosting . Their support page (linked above) pretty much tells how to set it up.  For this operation, it makes a big difference which host one uses.
    I was able to use a modified version of the php script from the article that you posted (http://www.evolt.org/article/Incoming_Mail_and_PHP/18/27914/ ), to parse the e-mail and put it into a database.  Here's the mailhandler.php :  

    * How would I modify it to extract (any and all) attachments and save those attachments to the database?
#!/usr/local/bin/php -q
<?php
 
$raw = "";
$handle = fopen("php://stdin", "r");
$raw = stream_get_contents($handle);
fclose($handle);
 
$lines = explode("\n", $raw);
 
$sender = "";
$subject = "";
$headers = "";
$message = "";
$splittingheaders = true;
 
 
for ($i=0; $i < count($lines); $i++) {
    if ($splittingheaders) {
        // this is a header
        $headers .= $lines[$i]."\n";
 
        // look out for special headers
        if (preg_match("/^Subject: (.*)/", $lines[$i], $matches)) {
            $subject = $matches[1];
        }
 
        if (preg_match("/^From: (.*)/", $lines[$i], $matches)) {
            $sender = $matches[1];
        }
 
        if (preg_match("/^Date: (.*)/", $lines[$i], $matches)) {
            $date = $matches[1];
        }
 
    } else {
        // not a header, but message
        $message .= $lines[$i]."\n";
    }
 
    if (trim($lines[$i])=="") {
        // empty line, header section has ended
        $splittingheaders = false;
    }
}
 
$h = 'data.example.com';
$u = 'username';
$p = 'password';
$d = 'database';
 
 $db = mysql_connect($h, $u, $p); 
  mysql_select_db($d, $db) or die(mysql_error());
 
$sqlinsert = "INSERT INTO outlookmail
(raw,
sender,
subject,
date,
body)
VALUES
('$raw',
'$sender',
'$subject',
'$date',
'$message')";
 
mysql_query($sqlinsert) or die(mysql_error());
 
?>

Open in new window

0
 
nplibCommented:
you could try this to see anything pops out to you, if not
post the results for me and I should be able to help.

$lines = explode("\n", $raw);
print_r($lines);

Though this is now outside of my experience.
remember to send an email with an attachment with to this script.
0
 
FrankTechAuthor Commented:
nplib,
   Thanks. I've actually developed a different script (based on one I found here on EE) that uses the PEAR mail_Decode package.  It does a better job of parsing the message; and it's supposed to parse attachments too.
     I set it up to save only the 1st attachment.  (It recognizes and saves a list of names of all the attachments, but I'm just trying to save the 1st attachment, if any.)

     It's working fine except for the attachment file. Seems like its saving a copy of the body, rather than the file attachment, to the variable $attachmentfile .  Maybe you can help me figure out why?
#!/usr/local/bin/php -q
<?php
 
include_once('Mail/mimeDecode.php');
 
$message = "";
$handle = fopen("php://stdin", "r");
$message = stream_get_contents($handle);
fclose($handle);
 
 
    if(get_magic_quotes_gpc()) {
        $message = stripslashes($message);
    }
 
 
    $decoder =& new Mail_mimeDecode($message);
    $decoded = $decoder->decode(array(
        'include_bodies' => TRUE,
        'decode_bodies' => TRUE,
        'decode_headers' => TRUE
    ));
 
    $subject = trim($decoded->headers['subject']);
    $to = trim($decoded->headers['to']);
    $from = trim($decoded->headers['from']);
    $date = trim($decoded->headers['date']);
        $date = trim(substr($date,0,25));
            $date = preg_replace('/^[a-zA-Z]{3}, /', '', $date);
             list($day, $month, $year, $time) = explode(' ', $date);
 
             if ($month == 'Jan') {$month = '01';}
		elseif ($month == 'Feb') {$month = '02';}
		elseif ($month == 'Mar') {$month = '03';}
		elseif ($month == 'Apr') {$month = '04';}
		elseif ($month == 'May') {$month = '05';}
		elseif ($month == 'Jun') {$month = '06';}
		elseif ($month == 'Jul') {$month = '07';}
		elseif ($month == 'Aug') {$month = '08';}
		elseif ($month == 'Sep') {$month = '09';}
		elseif ($month == 'Oct') {$month = '10';}
		elseif ($month == 'Nov') {$month = '11';}
		elseif ($month == 'Dec') {$month = '12';}		   	   		    		   
 
           list($hours, $minutes, $seconds) = explode(':', $time);
           
           if ($hours < 12) {$ampm = 'AM';} else {$ampm = 'PM';}
		   
           $date = "$year-$month-$day $time $ampm";
     
           $date = date("Y-m-d H:i A", strtotime("$date"));
 
    $text = trim(extract_text($decoded));
    
	$attachments = extract_attachments($decoded);
 
 
function extract_text(&$decoded) {
    $text = '';
 
    if(isset($decoded->parts)) {
        foreach($decoded->parts as $part) {
            $text .= extract_text($part);
        }
    } else {
        if($decoded->ctype_primary == 'text'
        and $decoded->ctype_secondary == 'plain') {
            $text .= $decoded->body;
        }
    }
 
    return $text;
}
 
function extract_attachments(&$decoded) {
    $attachments = array();
 
    if(isset($decoded->parts)) {
        foreach($decoded->parts as $part) {
            $attachments = array_merge($attachments, extract_attachments($part));
        }
    } else {
        if($decoded->ctype_primary !== 'image') {
            $attachment = array();
 
          if(isset($decoded->ctype_parameters['name'])) {
   $attachmentname = $decoded->ctype_parameters['name'];
  $attachment['name'] = "$attachmentname ";
            }
      $attachment['size'] = strlen($decoded->body);
      $attachment['data'] = $decoded->body;
 
            $attachments[] = $attachment;
        }
    }
 
    return $attachments;
}
 
 
$sender = addslashes(htmlspecialchars($from));
    $sendername = preg_match('/&quot;(.*?)&quot;/', $sender, $match2);
    $sendername = $match2[0];
    $sendername = str_replace('&quot;', '', $sendername);
 
$to = htmlspecialchars($to);
$subject = htmlspecialchars($subject);
     $filenum = preg_match('/^[0-9]{4,6} /', $subject, $match);
     $filenum = $match[0];
$message = addslashes(nl2br(htmlspecialchars($text)));
 
 
if(count($attachments) > 0) {
      foreach($attachments as $attachment) {
              
             // $attachmentsizes .= ($attachment['size']);
         $attachmentnames .= ($attachment['name']);
      } // end foreach
} else {
   $attachmentnames = '(None)';
}
$attachmentnames = trim($attachmentnames);
 
 
if(count($attachments) > 0) { // save only 1st attachment to var $attachmentfile
      $rownum = 0;
      foreach($attachments as $attachment) {
         if ($rownum == 0) {
           $attachmentfile = ($attachment['data']);
		   $attachmentfile = addslashes($attachmentfile);
		 }
	   $rownum++;
      } // end foreach
} 
 
 
$h = 'data.example.com';
$u = 'username';
$p = 'password';
$ds = 'database';
 
 $db = mysql_connect($h, $u, $p); 
  mysql_select_db($ds, $db) or die(mysql_error());
 
$sqlinsert = "INSERT INTO outlookmail
(sender,
subject,
date,
filenum,
body,
attachmentnames,
attachmentfile)
VALUES
('$sendername',
'$subject',
'$date',
'$filenum',
'$message',
'$attachmentnames',
'$attachmentfile')";
 
mysql_query($sqlinsert) or die(mysql_error());
 
?>

Open in new window

0
 
FrankTechAuthor Commented:
See lines 75-98 and 125-134 ($attachmentfile is assigned on 130).
0
 
FrankTechAuthor Commented:
By the way, the correct name of the PEAR function is
Mail_mimeDecode::decode()
 http://pear.php.net/manual/en/package.mail.mail-mimedecode.decode.php
     According to the solution at this other question (http:Q_21721792.html), attachments['data'] is supposed to be the attachments file.
0
 
nplibCommented:
what is missing is you need a file size column, and you need the file size data from the attachment.
in mysql the filesize column needs to be a integer
and the attachmentfile column type needs to be longblob.


also I would actually create a seperate table just for attachments, and give it a column that has some kind of id that can match up to the email tables column,
you can only have 1 file in a longblob field.
0
 
FrankTechAuthor Commented:
Yes, I agree with the separate table idea.  I'll be out for the rest of the today, but I'll get back to it this weekend and see what I can figure out. I'll let you know how it goes, probably on Sunday. Thanks.
0
 
FrankTechAuthor Commented:
nplib,
  I've tried "everything" but the script just won't separate the attachment. It is just putting the whole body into the attachment variable, instead of only the attachment. I tried a different script also, and it is not handling attachments right, either. For now I'm just going to put the message text into the database.
   I may open another question sometime to see if anyone has had experience with attachments using Mail_mimeDecode::decode() .  But for now I'll just deal with the message text.
   I'm going to accept your comment ID:20834314 as the answer because you pointed out an online article (http://www.evolt.org/article/Incoming_Mail_and_PHP/18/27914/ ) that showed me how to at least parse the headers and message body. Thanks.
0
All Courses

From novice to tech pro — start learning today.