> What I want to do is, check my POP3 server for messages, download those
> messages into my MySQL DB. I want to retrieve the FROM, SUBJECT, HEADER
and
> BODY from the messages and place them in the corresponding mysql fields.
This script I wrote last summer may help you some what.. it handles
attachments too.
#!/usr/local/bin/php -q
<?
// Written By Jon Farmer <[EMAIL PROTECTED]>
// A script which dowmloads the support email and processes it
$dbhost = "localhost";
$dbuser = "user";
$dbpassword = "password";
$dbname = "support";
$time = time();
mysql_pconnect($dbhost, $dbuser, $dbpassword);
mysql_select_db($dbname);
//Open the mailbox and check if mail waiting
$mailstream = imap_open("{localhost:143}INBOX","username","password");
if (!$mailstream) {
mysql_close();
exit;
}
$email = imap_headers($mailstream);
if (count($email) == 0) {
//echo "Nothing to process ... \n";
imap_expunge($mailstream);
imap_close($mailstream);
mysql_close();
exit;
}
//echo count($email) ." \n";
for ($i=1; $i<=count($email); $i++) {
;
$thisheader = imap_header($mailstream, $i);
$subject = $thisheader->subject;
$from = $thisheader->from;
$name = $from[0]->personal;
$fromaddr = sprintf("%s@%s",$from[0]->mailbox,$from[0]->host);
//echo $subject . " " . $fromaddr . " " . $name . "\n";
$struc = imap_fetchstructure($mailstream, $i, FT_UID);
//echo "type: " . $struc->bytes . "\n";
if (empty($struc->bytes)) {
//echo "Type: " . $struc->type . "\n";
//echo "Sub-type: " . $struc->subtype . "\n";
$mailtext = trim(imap_fetchbody($mailstream, $i, 1));
//echo "Parts Count: " . Count($struc->parts) . "\n";
for ($z = 1; $z < Count($struc->parts) + 1; $z++) {
//echo "Part No. " . $z . ": " . $struc->parts[$z]->type . "\n";
if ($struc->parts[$z]->ifparameters) {
$parameters = $struc->parts[$z]->parameters;
$resarray = get_object_vars($parameters[0]);
$filename[] = $resarray["value"];
$filesrc[] = addslashes(imap_fetchbody($mailstream, $i, $z + 1));
switch ($struc->parts[$z]->type) {
case 0:
$type = "text";
case 1:
$type = "multipart";
case 2:
$type = "message";
case 3:
$type = "application";
case 4:
$type = "audio";
case 5:
$type = "image";
case 6:
$type = "video";
case 7:
$type = "other";
default:
$type = "text";
}
$encode = $struc->parts[$z]->encoding . "";
settype($encode, "integer");
switch ($encode) {
case 0:
$encoding = "7Bit";
break;
case 1:
$encoding = "8Bit";
break;
case 2:
$encoding = "Binary";
break;
case 3:
$encoding = "Base64";
break;
case 4:
$encoding = "Quoted-Printable";
break;
case 5:
$encoding = "Other";
break;
}
//echo $encoding . "\n";
$filetype[] = addslashes($type . "/" .
strtolower($struc->parts[$z]->subtype));
$fileencoding[] = $encoding;
if ($type . "/" . strtolower($struc->parts[$z]->subtype) == "text/html") {
if (eregi("\.htm", $resarray["value"]) || eregi("\.html",
$resarray["value"])) {
} else {
$HTML = TRUE;
}
}
$attachments = TRUE;
}
}
} else {
$mailtext = trim(imap_body($mailstream, $i));
$attachments = FALSE;
}
//echo "$mailtext\n";
if ($HTML) {
//echo "html\n";
imap_delete($mailstream, $i);
$bodytext = "Dear Customer\n\n";
$bodytext .= "Thank you for contacting Customer Support.\n\n";
$bodytext .= "Unfortunately our automatic ticketing system for support
requests\n";
$bodytext .= "is not compatible with HTML email. Please resend your support
request\n";
$bodytext .= "in plaintext format.\n\n";
$bodytext .= "Alternatively call 09066 xxxxxx (call charged at 50p/min at
all times)";
//$fromaddr
mail ($from, "RE: " . $subject, $bodytext, "From: Customer Support
<support@localdomain>");
continue;
}
if (eregi("SUP[0-9][0-9][0-9][0-9][0-9][0-9]", $subject)) {
//Existing support call;
//Get ticket details from db
$location = strpos($subject, "SUP");
$ticket = trim(substr($subject, $location, 10));
$sSQL = "select * from supportrequests where ticketnumber = '$ticket'";
$result = mysql_db_query($dbname, $sSQL);
if (mysql_num_rows($result) == 0) {
//echo "Couldn't find ticket number $ticket" . "\n";
continue;
}
$row = mysql_fetch_array($result);
$supportrequestid = $row[0];
$emailtext = trim(imap_fetchbody($mailstream, $i, 1));
$sSQL = "insert into supportdetail (supportrequestid, fromname, email, text,
datereceived) values (" . $supportrequestid . ", '" . $name . "', '" .
$fromaddr . "', '" . $mailtext . "', " . $time . ")";
$result = mysql_db_query($dbname, $sSQL);
if ($attachments) {
$sSQL = "select supportdetailid from supportdetail where datereceived = " .
$time . " and supportrequestid = " . $supportrequestid;
//echo $sSQL . "\n";
$result = mysql_db_query($dbname, $sSQL);
$row = mysql_fetch_array($result);
$supportdetailid = $row["supportdetailid"];
$numatt = count($filesrc);
for ($h =0; $h<$numatt; $h++) {
$sSQL = "insert into attachments (supportdetailid, filename, attachment,
filetype, encoding) values ($supportdetailid, '$filename[$h]', '" .
$filesrc[$h] . "', '$filetype[$h]', '$fileencoding[$h]')";
$result = mysql_db_query($dbname, $sSQL);
}
}
$sSQL = "update supportrequests set datelastactioned = $time where
supportrequestid = $supportrequestid";
$result = mysql_db_query($dbname, $sSQL);
} else {
//echo "New Support Call\n";
//get next support number
$sSQL = "select * from ticketnumbers";
$result = mysql_db_query($dbname, $sSQL);
$row = mysql_fetch_array($result);
$thisnumber = $row[0];
$nextnumber = $thisnumber;
$thisnumber++;
$sSQL = "update ticketnumbers set ticketnumber = " . $thisnumber;
//echo $sSQL . "\n";
$result = mysql_db_query($dbname, $sSQL);
//add new support request to db
$ticket = "SUP" . sprintf("%07d", $nextnumber);
$sSQL = "insert into supportrequests (ticketnumber, dateopened,
datelastactioned, origemail, ContactType) values ('$ticket', $time, $time,
'$fromaddr', 'E');";
//echo $sSQL1 . "\n";
mysql_query($sSQL);
//echo $result1 . " " . mysql_errno() . ":" . mysql_error() . "\n";
//get new supportrequestid
$sSQL = "select * from supportrequests where ticketnumber = '" . $ticket .
"'";
$result = mysql_db_query($dbname, $sSQL);
$row = mysql_fetch_array($result);
$supportid = $row[0];
$emailtext = trim(imap_body($mailstream, $i));
$sSQL = "insert into supportdetail (supportrequestid, fromname, email, text,
datereceived) values (" . $supportid . ", '" . $name . "', '" . $fromaddr .
"', '" . $mailtext . "', " . $time . ")";
//echo $sSQL . "\n";
mysql_query($sSQL);
usleep(1000);
//get supportdetailid for this email
if ($attachments) {
$sSQL = "select supportdetailid from supportdetail where datereceived = " .
$time . " and supportrequestid = " . $supportid;
//echo $sSQL . "\n";
$result = mysql_db_query($dbname, $sSQL);
$row = mysql_fetch_array($result);
$supportdetailid = $row["supportdetailid"];
$numatt = count($filesrc);
for ($h =0; $h<$numatt; $h++) {
$sSQL = "insert into attachments (supportdetailid, filename, attachment,
filetype, encoding) values ($supportdetailid, '$filename[$h]', '" .
$filesrc[$h] . "', '$filetype[$h]', '$fileencoding[$h]')";
//echo $sSQL;
$result = mysql_db_query($dbname, $sSQL);
}
}
$bodytext = "Thank you for your support request\n\n";
$bodytext = $bodytext . "We have assigned the following ticket number to
this request " . $ticket . "\n";
if ($attachments) {
$bodytext = $bodytext . "\nWe received the following attachments from
you\n";
for ($u=0; $u<$numatt; $u++) {
$bodytext = $bodytext . "File: " . $filename[$u] . " type: " . $filetype[$u]
. "\n";
}
}
$bodytext = $bodytext . "\nPlease make sure that the ticket number is quoted
in all correspondence\n"; $bodytext = $bodytext . "and that it appears in
the subject line of all emails\n";
$bodytext = $bodytext . "you send regarding this matter\n\n";
$bodytext = $bodytext . "Customer Service Team\n";
$bodytext .= "To speak to someone about this call 09066 xxxxxx quoting
$ticket\n";
$bodytext .= "Calls cost 50p per min at all times";
$bodytext .= "\n\n--Original Message Follows--\n\n";
$origtext = explode("\n", $mailtext);
$origcount = count($origtext);
for ($o=0; $o<$origcount; $o++) {
$origtextnew .= ">" . $origtext[$o] . "\n";
}
$bodytext .= $origtextnew;
//$fromaddr
mail($from, "RE: " . $subject . " " . $ticket, $bodytext, "From:Customer
Support support@localdomain");
}
imap_delete($mailstream, $i);
}
//Close the mail box like a good little boy
imap_expunge($mailstream);
imap_close($mailstream);
mysql_close();
--
--
Jon Farmer
Systems Programmer, Entanet www.enta.net
Tel 01952 428969
PGP Key available, send email with subject: Send PGP Key
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
