Get Contents From E-Mail And Write It To Mysql Table

demo · 发表于 2014-2-11 22:47:41

How can I make it so when I e-mail for e.g. news@example.com text/information of the e-mail is written into a mysql table? It would extract the contents of a new email and write them into MySQL table. This table manages news articles on the website, so in the MySQL columns:
"content" is the body of the message
"caption" is the title of the news article
"snippet" is the title that will appear in a "Latest News" block
"uri"
"date" is the date the article is posted.
"

$subject = caption
   = uri

$message = content
   = snippet(first two lines of content)

$date    = date (in Unix timestamp)

The table is named bx_news_entries manages the news articles visibly.
I researched and saw a user who posted this on stackoverflow who's code looked like so:----------------------

Here's an example of the script I use to check email:

<?php
$now = time(); // current time
$mailbox = '{192.168.150.11:143/imap/novalidate-cert}'; // see http://www.php.net/manual/en/function.imap-open.php
$mbox = imap_open($mailbox, 'username', 'password'); // log in to mail server
if (!$mbox)
echo ('Failed opening mailbox<br>' . print_r(imap_errors(), true)); // remove the print_r for production use
else
{
$box = imap_check($mbox); // get the inbox
for ($imap_idx = 1; $imap_idx <= $box->Nmsgs; $imap_idx++) // loop through the messages
{
$headers = imap_headerinfo($mbox, $imap_idx); // http://www.php.net/manual/en/function.imap-headerinfo.php
$raw_headers = imap_fetchheader($mbox, $imap_idx); // http://www.php.net/manual/en/function.imap-fetchheader.php
$selected_headers = '';
$text_part = '';
$html_part = '';
$original_message = imap_body($mbox, $imap_idx); // save the copy of the entire thing, attachments and all
// build selected headers string
for ($ii = 0; $ii < count($headers->from); $ii++)
$selected_headers .= 'From: ' . $headers->from[$ii]->mailbox . '@' . $headers->from[$ii]->host . "\n";
for ($ii = 0; $ii < count($headers->to); $ii++)
$selected_headers .= 'To: ' . $headers->to[$ii]->mailbox . '@' . $headers->to[$ii]->host . "\n";
for ($ii = 0; $ii < count($headers->cc); $ii++)
$selected_headers .= 'Cc: ' . $headers->cc[$ii]->mailbox . '@' . $headers->cc[$ii]->host . "\n";
for ($ii = 0; $ii < count($headers->bcc); $ii++)
$selected_headers .= 'Bcc: ' . $headers->bcc[$ii]->mailbox . '@' . $headers->bcc[$ii]->host . "\n";
if (!empty($headers->date))
$selected_headers .= 'Date: ' . $headers->date . "\n";
if (!empty($headers->subject))
$selected_headers .= 'Subject: ' . $headers->subject . "\n";
// see below; getMsg uses global variables
getMsg($mbox, $imap_idx);
$text_part = $plainmsg; // text portion of the email
$html_part = $htmlmsg; // html portion of the email
// check for text portion first
$msg_text = trim(strip_tags($plainmsg, '<a>'));
if ($msg_text == '')
{
// text portion is empty, check html portion
$msg_text = trim($htmlmsg);
if ($msg_text == '')
{
// no text or html portion auto-detected, check manually
$msg_text = imap_body($mbox, $imap_idx); // get the entire raw message
// check for quoted-printable encoding with possible boundary markers and headers at the top
$chunks = explode("\n", trim($msg_text));
if (count($chunks) > 1) // if there are multiple lines
{
$quoted_printable = false;
if (strpos($chunks[0], '--') === 0) // if the first line is a boundary marker (starts with '--')
{
array_shift($chunks); // remove the first line
if (strpos($chunks[count($chunks) - 1], '--') === 0) // check the last line
{
array_pop($chunks); // remove that too
}
}
if (strpos(strtolower($chunks[0]), 'content-type') === 0)
array_shift($chunks); // remove the first line if it's a content-type header
if (strpos(strtolower($chunks[0]), 'content-transfer-encoding') === 0)
{
if (strpos(strtolower($chunks[0]), 'quoted-printable'))
$quoted_printable = true; // this email was sent using quoted-printable encoding
array_shift($chunks); // remove the content-transfer-encoding header
}
$msg_text = implode("\n", $chunks); // put the remaining lines back together
if ($quoted_printable) $msg_text = quoted_printable_decode($msg_text);
$msg_text = utf8_decode($msg_text);
}
}
}
$from = trim($headers->from[0]->mailbox . '@' . $headers->from[0]->host);
$msgId = isset($headers->message_id) ? trim($headers->message_id) : '';
$time = strtotime($headers->date);
if ($time == 0)
$time = $now;
/******************************************************
At this point:
$headers: the object returned from imap_headerinfo
$selected_headers: text of some headers to display to a user checking mail (subject, from, etc)
$text_part: the text portion, if found
$html_part: the html portion, if found
$msg_text: either the text part, html part, or manually-decoded part (this is the variable to use as email body)
$original_message: the entire unprocessed email body, includingall parts and any attachments
$from: From address
$msgId: message ID from the headers
$time: email delivery time, as a Unix timestamp
$attachments: array of attachments (see below)
******************************************************/
// process attachments
foreach ($attachments as $filename => $data)
{
// e.g. file_put_contents('attachments/' . $filename, $data);
}
// flag the email for deletion
imap_delete($mbox, $imap_idx);
}
// delete emails and close the mailbox
imap_expunge($mbox);
imap_close($mbox);
}
function getMsg($mbox,$mid) {
// input $mbox = IMAP stream, $mid = message id
// output all the following:
global $htmlmsg,$plainmsg,$charset,$attachments;
// the message may in $htmlmsg, $plainmsg, or both
$htmlmsg = $plainmsg = $charset = '';
$attachments = array();
// HEADER
$h = imap_header($mbox,$mid);
// add code here to get date, from, to, cc, subject...
// BODY
$s = imap_fetchstructure($mbox,$mid);
if (empty($s->parts)) // not multipart
getMsgPart($mbox,$mid,$s,0); // no part-number, so pass 0
else { // multipart: iterate through each part
foreach ($s->parts as $partno0=>$p)
getMsgPart($mbox,$mid,$p,$partno0+1);
}
}
function getMsgPart($mbox,$mid,$p,$partno) {
// $partno = '1', '2', '2.1', '2.1.3', etc if multipart, 0 if not multipart
global $htmlmsg,$plainmsg,$charset,$attachments;
// DECODE DATA
$data = ($partno)?
imap_fetchbody($mbox,$mid,$partno): // multipart
imap_body($mbox,$mid); // not multipart
// Any part may be encoded, even plain text messages, so check everything.
if ($p->encoding==4)
$data = quoted_printable_decode($data);
elseif ($p->encoding==3)
$data = base64_decode($data);
// no need to decode 7-bit, 8-bit, or binary
// PARAMETERS
// get all parameters, like charset, filenames of attachments, etc.
$params = array();
if ($p->ifparameters)
foreach ($p->parameters as $x)
$params[ strtolower( $x->attribute ) ] = $x->value;
if ($p->ifdparameters)
foreach ($p->dparameters as $x)
$params[ strtolower( $x->attribute ) ] = $x->value;
// ATTACHMENT
// Any part with a filename is an attachment,
// so an attached text file (type 0) is not mistaken as the message.
if (!empty($params['filename']) || !empty($params['name'])) {
// filename may be given as 'Filename' or 'Name' or both
$filename = (!empty($params['filename']))? $params['filename'] : $params['name'];
// filename may be encoded, so see imap_mime_header_decode()
$attachments[$filename] = $data; // this is a problem if two files have same name
}
// TEXT
elseif ($p->type==0 && $data) {
// Messages may be split in different parts because of inline attachments,
// so append parts together with blank row.
if ($p->ifsubtype && strtolower($p->subtype)=='plain')
$plainmsg .= trim($data) ."\n\n";
else
$htmlmsg .= $data ."<br><br>";
$charset = $params['charset']; // assume all parts are same charset
}
// EMBEDDED MESSAGE
// Many bounce notifications embed the original message as type 2,
// but AOL uses type 1 (multipart), which is not handled here.
// There are no PHP functions to parse embedded messages,
// so this just appends the raw source to the main message.
elseif ($p->type==2 && $data) {
$plainmsg .= trim($data) ."\n\n";
}
// SUBPART RECURSION
if (!empty($p->parts)) {
foreach ($p->parts as $partno0=>$p2)
getMsgPart($mbox,$mid,$p2,$partno.'.'.($partno0+1)); // 1.2, 1.2.1, etc.
}
}
?>

复制代码

Print out the $text_part, $html_part, and $msg_text variables in that loop to figure out which one you want to use. Maybe the email has a text portion that is easier to process.

Once you get the text you're looking for, you need to use strip_tags to remove all of the HTML from that text if you're going to display it on a page. You don't need people emailing you Javascript code or Flash movies that you happily save and display for your users.

demo · 发表于 2014-2-11 22:51:27

I am at a point in applying a test for the overall code before incorporating the email script justsomeguy provided.
Haven't really create my own function before, so what i am attempting is to start the scripts below the line "function NewsUpdate" but i want them to be apart of the function. In the if statement, when the script finds the string "News Update" in the 'email message' it will start those scripts if to (the else statement all the way at the bottom) it will fail and say "News Update not found in e-mail message"

//Test code for email message.
$open_email_msg = file_get_contents('emailmessage.html');
//A script that searches the e-mail for the string News Update, and if it is found it will start the function NewsUpdate
if(strpos($open_email_msg,"News Update"))
function NewsUpdate($open_email_msg) {
//Login to MySQL Datebase
$hostname = "localhost";
$db_user = "user";
$db_password = "pass";
$database = "tablename";
$db_table = "bx_news_entries";
$db = mysql_connect($hostname, $db_user, $db_password);
mysql_select_db($database,$db);
$subject = 'Test News Article';
$tags = str_replace(' ',',',$subject); //DDIE
$uri = str_replace(' ','-',$subject); //DDIE
$when = strtotime("now"); //date article was posted
$categories = 'Events';
$content = 'Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur.';
$snippet = 'Lorem ipsum dolor sit amet.';
//$snippet = explode(".", $content, -1);
# THIS CODE WILL TELL MYSQL TO INSERT THE DATA FROM THE EMAIL INTO YOUR MYSQL TABLE
$sql = "INSERT INTO $db_table(`caption`,`snippet`,`content`,`when`,`uri`,`tags`,`categories`,`DATE`) values ('$subject','$snippet','$content','$when','$uri','$tags','$categories','$when')";
if($result = mysql_query($sql ,$db)) {
} else {
echo "ERROR: ".mysql_error();
}
echo "<h1>News Article added!</h1>";
}
else {
echo "<h3>'News Update</h3>not found in e-mail message!";
}
?>

复制代码

账号		自动登录	找回密码
密码			注册

Get Contents From E-Mail And Write It To Mysql Table

浏览过的版块