[RESOLVED] PHP IMAP Email Cron Job Save Email to MS SQL Database & Attachments to Disk

farmerj2000 · Mar 29, 2012

Folks, I'm new to PHP and having a little trouble understanding how to save attachments from a email. The following script works great for saving to the database. I just need to add a section to save attachments to disk. I have searched for the past few days. I have found plenty of examples. I guess I just don't understand PHP well enough.

Basically I want to run a daily cron job, grab all the emails with ### in the subject. Insert that information into a database. Then grab the file attachments and enter the name of the files into the database and save them to disk. the first part is working.

Can some please demonstrate this with the code below.
PHP Version 5.3.10 running on IIS 6 windows 2003

<?php
$save_msg_to_db = TRUE; // Save e-mail body to DB?

$db_host = 'somename';
$db_un = 'sa';
$db_pass = 'somepassword';

/* Authenticate to Mailserver */
$mailserver = "{smtp.something.com:143}";
$mailuser = "something";
$mailpass = "something";

/* Open Mailbox Stream */
$mbox = imap_open($mailserver, $mailuser, $mailpass) or die ("Couldn't connect to $mailserver");

/* Search For Message Criteria  See:  http://us.php.net/manual/en/function.imap-search.php */
$crit = "SUBJECT \"###\"";
$header = imap_search($mbox, $crit);

/* Return Results Of Search */
foreach ($header as $val) {
	$id="";

/* Retrieve Body Of Message */
$body = imap_fetchbody($mbox, $val, 1 , FT_PEEK);

/* Retrieve Details From Header Of Message */
// [subject] [from] [to] [date] [message_id] [size] [uid] [msgno] [recent] [flagged] [answered] [deleted [seen] [draft]

$v = imap_fetch_overview($mbox, $val);
foreach ($v as $ov) {
	$strsubject = $ov->subject;  // subject
	$strfrom = $ov->from;  // from
	$strto = $ov->to;  // to
	$strdate = $ov->date;  // date
	$messageid = $ov->message_id;  // Message ID

}

$JOBNUM = str_replace(' ', '', $strsubject);
$JOBNUM = substr(ltrim($JOBNUM),3,6);
	// SAVE OR PARSE THESE VARIABLES INTO MySQL

   //$b = body;
   //$s = subject;
   //$f = from;
   //$t = to;
   // $d = date;




//echo "found 1<br>";
echo "<br>From: " . $strfrom . "<br>To: " . $strto . "<br>Date: " . $strdate . "<br>". "Subject: " . $strsubject . "<br>";
echo "Job Number: " . $JOBNUM. "<br>";
echo "MessageID: " . $messageid. "<br><br>";
echo nl2br($body)."<br><br>";
echo "---------------------------------------------------------------------------------------------------<br>";
echo "---------------------------------------------------------------------------------------------------<br>";
echo "<br><br>";	
//imap_delete($mbox, $val);  // delete the e-mail
//DO DB insert here and save Files to Disk
$conn = odbc_connect($db_host,$db_un,$db_pass);
//mysql_select_db($db_name); 

 $body =  str_replace("'",''',$body);
 $body =  str_replace("\"",'"',$body);
 $body =  str_replace("\\",'\',$body);
 $strsubject =  str_replace("'",''',$strsubject);
 $strsubject =  str_replace("\"",'"',$strsubject);
 $strsubject =  str_replace("\\",'\',$strsubject);

$stmt = odbc_prepare($conn, "INSERT INTO tblEmail (MailFrom, Subject, Body, JOBNUM, EMAILID) VALUES('" . $strfrom . "','" . $strsubject . "','" . $body . "','" .$JOBNUM. "','" . $messageid . "');");
//$q = "INSERT INTO tblEmails (MailFrom, Subject) VALUES ('test','test')";

 if (!odbc_execute($stmt)) { 
    /* error  */ 
    echo "Whoops"; 

} 
	odbc_close ($conn);
	imap_delete($mbox, $val);  // delete the e-mail
}


/* Delete Messages */
imap_expunge($mbox);

/* Close Mailbox Stream */
imap_close($mbox);

?>

bradgrafelman · Mar 29, 2012

The basic process is to use [man]imap_fetchstructure/man on a given message number to retrieve a (detailed) object describing the entire message - including all of its parts (such as the body and any attachments). You then use [man]imap_fetchbody/man to retrieve one or more of the other "parts" of the message (other than the body of the e-mail itself).

Some relevant tutorials/guides I found (complete with plenty of example code) are here and here.

farmerj2000 · Mar 29, 2012

bradgrafelman;11000324 wrote:
The basic process is to use [man]imap_fetchstructure/man on a given message number to retrieve a (detailed) object describing the entire message - including all of its parts (such as the body and any attachments). You then use [man]imap_fetchbody/man to retrieve one or more of the other "parts" of the message (other than the body of the e-mail itself).

Some relevant tutorials/guides I found (complete with plenty of example code) are here and here.

Thanks for the response. after looking at the two guides I try to use with my code.

I am finding the file and able to write the file to disk. I dont think I am getting the entire data stream though because the file is 0kb and corrupt. Again very new to PHP please bare with me


<?php

function flattenParts($messageParts, $flattenedParts = array(), $prefix = '', $index = 1, $fullPrefix = true) {

    foreach($messageParts as $part) {
        $flattenedParts[$prefix.$index] = $part;
        if(isset($part->parts)) {
            if($part->type == 2) {
                $flattenedParts = flattenParts($part->parts, $flattenedParts, $prefix.$index.'.', 0, false);
            }
            elseif($fullPrefix) {
                $flattenedParts = flattenParts($part->parts, $flattenedParts, $prefix.$index.'.');
            }
            else {
                $flattenedParts = flattenParts($part->parts, $flattenedParts, $prefix);
            }
            unset($flattenedParts[$prefix.$index]->parts);
        }
        $index++;
    }

    return $flattenedParts;	             
}

function getPart($connection, $messageNumber, $partNumber, $encoding) {

    $data = imap_fetchbody($connection, $messageNumber, $partNumber);
    switch($encoding) {
        case 0: return $data; // 7BIT
        case 1: return $data; // 8BIT
        case 2: return $data; // BINARY
        case 3: return base64_decode($data); // BASE64
        case 4: return quoted_printable_decode($data); // QUOTED_PRINTABLE
        case 5: return $data; // OTHER
    }


}

function getFilenameFromPart($part) {

    $filename = '';

    if($part->ifdparameters) {
        foreach($part->dparameters as $object) {
            if(strtolower($object->attribute) == 'filename') {
                $filename = $object->value;
            }
        }
    }

    if(!$filename && $part->ifparameters) {
        foreach($part->parameters as $object) {
            if(strtolower($object->attribute) == 'name') {
                $filename = $object->value;
            }
        }
    }

    return $filename;

}




$db_host = 'something';
$db_un = 'something';
$db_pass = 'something';

/* Authenticate to Mailserver */
$mailserver = "{smtp.something.com:143}";
$mailuser = "something";
$mailpass = "something";



/* Open Mailbox Stream */
$mbox = imap_open($mailserver, $mailuser, $mailpass) or die ("Couldn't connect to $mailserver");

/* Search For Message Criteria  See:  http://us.php.net/manual/en/function.imap-search.php */
$crit = "SUBJECT \"###\"";
$header = imap_search($mbox, $crit);

/* Return Results Of Search */
foreach ($header as $val) {
	$id="";

/* Retrieve Body Of Message */
$body = imap_fetchbody($mbox, $val, 1.1 , FT_PEEK);

/* Retrieve Details From Header Of Message */
// [subject] [from] [to] [date] [message_id] [size] [uid] [msgno] [recent] [flagged] [answered] [deleted [seen] [draft]

$v = imap_fetch_overview($mbox, $val);
foreach ($v as $ov) {
	$strsubject = $ov->subject;  // subject
	$strfrom = $ov->from;  // from
	$strto = $ov->to;  // to
	$strdate = $ov->date;  // date
	$messageid = $ov->message_id;  // Message ID
	$msgno = $ov->msgno;  // Message #

}

$JOBNUM = str_replace(' ', '', $strsubject);
$JOBNUM = substr(ltrim($JOBNUM),3,6);



echo "<br>From: " . $strfrom . "<br>To: " . $strto . "<br>Date: " . $strdate . "<br>". "Subject: " . $strsubject . "<br>";
echo "Job Number: " . $JOBNUM. "<br>";
echo "MessageID: " . $messageid. "<br><br>";
echo nl2br($body)."<br><br>";
echo "---------------------------------------------------------------------------------------------------<br>";
echo "---------------------------------------------------------------------------------------------------<br>";
echo "<br><br>";	

//DO DB insert here and save Files to Disk
$conn = odbc_connect($db_host,$db_un,$db_pass);
//mysql_select_db($db_name); 

 $body =  str_replace("'",''',$body);
 $body =  str_replace("\"",'"',$body);
 $body =  str_replace("\\",'\',$body);
 $strsubject =  str_replace("'",''',$strsubject);
 $strsubject =  str_replace("\"",'"',$strsubject);
 $strsubject =  str_replace("\\",'\',$strsubject);


$stmt = odbc_prepare($conn, "INSERT INTO tblEmail (MailFrom, Subject, Body, JOBNUM, EMAILID) VALUES('" . $strfrom . "','" . $strsubject . "','" . $body . "','" .$JOBNUM. "','" . $messageid . "');");
if (!odbc_execute($stmt)) { 
    /* error  */ 
    echo "Whoops"; 

} 
//get attachments

$structure = imap_fetchstructure($mbox, $msgno);
$flattenedParts = flattenParts($structure->parts);

foreach($flattenedParts as $partNumber => $part) {

    switch($part->type) {

        case 0:
            // the HTML or plain text part of the email
            $message = getPart($mbox, $msgno, $partNumber, $part->encoding);

            // now do something with the message, e.g. render it
        break;

        case 1:
            // multi-part headers, can ignore

        break;
        case 2:
            // attached message headers, can ignore
        break;

        case 3: // application
        case 4: // audio
        case 5: // image
        case 6: // video
        case 7: // other
            $filename = getFilenameFromPart($part);
            if($filename) {
                // it's an attachment
            $attachment = getPart($mbox, $msgno, $partNumber, $part->encoding);
                // now do something with the attachment, e.g. save it somewhere
			$tFileHandle = fopen('F:/Interoffice_uploads/uploads/' . 'JF-' . $filename, "w");
			$fileContent = imap_fetchbody($mbox, $msgno, 2);
            fwrite($tFileHandle, imap_base64($fileContent));
            fclose ($tFileHandle);	

            }
            else {
                // don't know what it is
            }
        break;

    }

}


//send email	
  //$to = $strfrom;
 // $newmsg = "Thanks! I just uploaded the following ";
  //mail($to,$strsubject,$newmsg);

odbc_close ($conn);
//imap_delete($mbox, $val);  // delete the e-mail
}


/* Delete Messages */
//imap_expunge($mbox);

/* Close Mailbox Stream */
imap_close($mbox);

?>

farmerj2000 · Mar 29, 2012

After a little more testing I was able to get this to work. Thank you.

[RESOLVED] PHP IMAP Email Cron Job Save Email to MS SQL Database & Attachments to Disk

Ffarmerj2000

Bbradgrafelman

Ffarmerj2000

Ffarmerj2000