Taking body of an email and storing it in MySQL database

PHP List,

I am working on a project now where I need to check a POP3 mail account,
download any mails there, and store the body and headers of each
individual mail as plain text in a MySQL database.

I have downloaded the POP3 class which seems to be the standard class
everyone uses for POP3 mail checking:
http://www.phpclasses.org/browse/package/2.html

I think I understand how to create an instance of the class and use it
to check the email. The part where you log into the account isn't that
complicated.

But after that it becomes a little strange. It seems that emails are not
mere text files, but have to be assembled line by line?

I thought each email, and their headers, would be something I could just
access, download, and pass along to MySQL. But it seems more complicated
than that.

Has anyone done something like this before?

If someone could let me know how it is that I can create a body of text
for each email (including the headers) so that I can then store it in
the database, that would be awesome.


Any advice would be much appreciated.

--
Dave M G

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Taking body of an email and storing it in MySQL database

Hi,

> ...

Sounds like you may have a MIME (an HTML email or one with attachments
etc) email to decode. What you can do is use the PEAR class
Mail_mimeDecode ( http://pear.php.net/package/Mail_mimeDecode ). Pass
it the entire email (headers and all) and it will try to decode it for
you.

--
Richard Heyes
HTML5 graphing: RGraph - www.rgraph.net (updated 5th September)
Lots of PHP and Javascript code - http://www.phpguru.org

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Taking body of an email and storing it in MySQL database

Richard,

Thank you for responding.

> Sounds like you may have a MIME (an HTML email or one with attachments
> etc) email to decode.

No, the mails should be in plain text format.

I probably didn't describe my issue properly because I don't understand
it fully.

If I can assume all the mails I am receiving are in plain text format -
which they should be because it is within a controlled environment - can
I use PHP to pull out each email from the POP server essentially as a
text file?

I need to find out the sender and subject line, but other than that, the
rest of it (including headers) I just want to store in MySQL.

--
Dave M G

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: Taking body of an email and storing it in MySQL database

>-----Original Message-----
>From: Dave M G [mailto:martin@autotelic.com]=20
>Sent: 07 September 2009 14:26
>To: Richard Heyes
>Cc: php-general@lists.php.net
>Subject: Re: [PHP] Taking body of an email and storing it in MySQL =
database
>
>Richard,
>
>Thank you for responding.
>
>> Sounds like you may have a MIME (an HTML email or one with =
attachments
>> etc) email to decode.=20
>
>No, the mails should be in plain text format.
>
I probably didn't describe my issue properly because I don't understand
it fully.
>
>If I can assume all the mails I am receiving are in plain text format -
>which they should be because it is within a controlled environment - =
can
>I use PHP to pull out each email from the POP server essentially as a
>text file?
>
>I need to find out the sender and subject line, but other than that, =
the
>rest of it (including headers) I just want to store in MySQL.

Dave,

Take a look on phpclasses.net, they have classes which connect via POP3 =
and give you arrays of messages back.=20

HTH
J


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Taking body of an email and storing it in MySQL database

On Mon, Sep 07, 2009 at 07:47:00PM +0900, Dave M G wrote:

> PHP List,
>
> I am working on a project now where I need to check a POP3 mail account,
> download any mails there, and store the body and headers of each
> individual mail as plain text in a MySQL database.
>
> I have downloaded the POP3 class which seems to be the standard class
> everyone uses for POP3 mail checking:
> http://www.phpclasses.org/browse/package/2.html
>
> I think I understand how to create an instance of the class and use it
> to check the email. The part where you log into the account isn't that
> complicated.
>
> But after that it becomes a little strange. It seems that emails are not
> mere text files, but have to be assembled line by line?
>
> I thought each email, and their headers, would be something I could just
> access, download, and pass along to MySQL. But it seems more complicated
> than that.
>
> Has anyone done something like this before?
>
> If someone could let me know how it is that I can create a body of text
> for each email (including the headers) so that I can then store it in
> the database, that would be awesome.

I don't know what your class is doing, but yes, emails are very simple
in structure. Everything above the first bare newline (nothing else on
that line) is header, and everything below it is body. Even mime-encoded
attachments are just part of the body and can be stored as plain text.
Your class may be parsing the emails and assigning various parts of them
to various class members. But emails are downloaded as a stream of bits,
sequentially, top to bottom. Somewhere in the class is a routine which
is sucking up that bit stream. At that point, you can capture it without
other parsing.

Paul

--
Paul M. Foster

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Taking body of an email and storing it in MySQL database

Paul,

Is there a decent tutorial anywhere on the net for the pop3.class.inc
available on phpclasses.org?

http://www.phpclasses.org/browse/package/1120.html

Maybe I'm blind, but I just can't find anywhere a decent description or
tutorial of what calls you can make to its functions. The documentation
file has two sections in German and a terse section in English.

I'm trying to figure out how to get each message and get their subject
line, from address, and then based on those two pieces of information,
I'll take the whole body and store them in the right place in a MySQL
database.

Just for reference, this is what I've built so far (I renamed
pop3.class.inc to POP3.php):

include('POP3.php');
$pop3 = new POP3();

// Connect to mail server
$do = $pop3->connect ('xxx.xxxxxxxxx.com');
if ($do == false)
{
$message = $pop3->error;
}

$do = $pop3->login ('uuuuuuuuuuu', 'ppppppppppp');

if ($do == false)
{
$message = $pop3->error;
}

$status = $pop3->get_office_status();

if ($status == false)
{
die($pop3->error);
}

$count = $status['count_mails'];


for ($i = 1; $i <= $count; $i++)
{
// Here's where I hit a wall.
// 1. Get the from address
// 2. Get the subject line
// 3. Get the header+body as one text file
// 4. Store it in the database.
}

$pop3->close();

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Taking body of an email and storing it in MySQL database

On Tue, Sep 08, 2009 at 03:27:05AM +0900, Dave M G wrote:

> Paul,
>
> Is there a decent tutorial anywhere on the net for the pop3.class.inc
> available on phpclasses.org?
>
> http://www.phpclasses.org/browse/package/1120.html
>
> Maybe I'm blind, but I just can't find anywhere a decent description or
> tutorial of what calls you can make to its functions. The documentation
> file has two sections in German and a terse section in English.
>
> I'm trying to figure out how to get each message and get their subject
> line, from address, and then based on those two pieces of information,
> I'll take the whole body and store them in the right place in a MySQL
> database.
>
> Just for reference, this is what I've built so far (I renamed
> pop3.class.inc to POP3.php):
>
> include('POP3.php');
> $pop3 = new POP3();
>
> // Connect to mail server
> $do = $pop3->connect ('xxx.xxxxxxxxx.com');
> if ($do == false)
> {
> $message = $pop3->error;
> }
>
> $do = $pop3->login ('uuuuuuuuuuu', 'ppppppppppp');
>
> if ($do == false)
> {
> $message = $pop3->error;
> }
>
> $status = $pop3->get_office_status();
>
> if ($status == false)
> {
> die($pop3->error);
> }
>
> $count = $status['count_mails'];
>
>
> for ($i = 1; $i <= $count; $i++)
> {
> // Here's where I hit a wall.
> // 1. Get the from address
> // 2. Get the subject line
> // 3. Get the header+body as one text file
> // 4. Store it in the database.

I'm not sure why you're attempting to do the above. According to the
documentation, you should simply be able to do:

for ($i = 1; $i <= $count; $i++) {
$msg = $pop3->get_mail($i);
// parse the message as you like
$pop3->save2file($msg, 'myfilename.txt');
// or
$pop3->save2mysql(a bunch of parameters);
}

The $msg is returned as an array of strings. You could use the PHP
implode() function to reconstruct the array as a single string. But the
POP3::save2file() function will do this and save it to a text file if
you like.

It appears the class doesn't provide any support for parsing the message
internally. So that part is up to you. However, you could set up a loop
like this:

for ($j = 0; $j < count($msg); $j++) {
if (strpos($msg[$i], 'Subject: ') == 0)
// got the subject line
if (strpos($msg[$i], 'From: ') == 0)
// got the From: address line
}

Parsing the subject line and From: address is up to you. But there are
plenty of PHP functions to search and parse strings.

Paul

--
Paul M. Foster

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Taking body of an email and storing it in MySQL database

Paul,

Thank you very much for responding.


> I'm not sure why you're attempting to do the above. According to the
> documentation, you should simply be able to do
> ...
> $pop3->save2mysql(a bunch of parameters);

I'm not sure the parameters on the save2mysql() function give me the
flexibility I need. I have to save to particular tables and change other
fields in the database, determined by user data in the database and the
from and subject field in the email.

The save2mysql class seems to assume a certain database structure.
Although, again, I could be wrong about that because documentation is
lacking.

> However, you could set up a loop
> like this:
>
> for ($j = 0; $j < count($msg); $j++) {
> if (strpos($msg[$i], 'Subject: ') == 0)
> // got the subject line
> if (strpos($msg[$i], 'From: ') == 0)
> // got the From: address line
> }
>
> Parsing the subject line and From: address is up to you. But there are
> plenty of PHP functions to search and parse strings.

Thanks very much for the code you provided. I think that should be
enough of a boost forward to get me to a new stage, and hopefully even
take me to the end of this current problem.

I'll see what I can do with it and then hopefully report back success.

--
Dave M G

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Taking body of an email and storing it in MySQL database

Hello,

on 09/07/2009 07:47 AM Dave M G said the following:
> I am working on a project now where I need to check a POP3 mail account,
> download any mails there, and store the body and headers of each
> individual mail as plain text in a MySQL database.
>
> I have downloaded the POP3 class which seems to be the standard class
> everyone uses for POP3 mail checking:
> http://www.phpclasses.org/browse/package/2.html
>
> I think I understand how to create an instance of the class and use it
> to check the email. The part where you log into the account isn't that
> complicated.
>
> But after that it becomes a little strange. It seems that emails are not
> mere text files, but have to be assembled line by line?
>
> I thought each email, and their headers, would be something I could just
> access, download, and pass along to MySQL. But it seems more complicated
> than that.
>
> Has anyone done something like this before?
>
> If someone could let me know how it is that I can create a body of text
> for each email (including the headers) so that I can then store it in
> the database, that would be awesome.

If you look at that same package there is a script named
browse_mailbox.php that demonstrates how to use the POP3 class to pull
messages from the POP3 server and automatically decode them so you can
process the messages very easily according to your needs.

It returns arrays that list all message parts, including any
attachments. From then on, you need to decide which parts of the
messages are relevant to you to perform the necessary database INSERT
queries to store the message text, HTML, images, attachments, etc..

For this you also need the MIME parser class that is available in the
same site:

http://www.phpclasses.org/mimeparser

You may also want to read this blog post to learn how to use these
classes to process the messages conveniently:

http://www.phpclasses.org/blog/package/2/post/1-Process-inco ming-email-messages-using-PHP.html


--

Regards,
Manuel Lemos

Find and post PHP jobs
http://www.phpclasses.org/jobs/

PHP Classes - Free ready to use OOP components written in PHP
http://www.phpclasses.org/

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php