Page 1 of 1

store email body, title, content and attachments to MySQL

Posted: Wed Nov 08, 2006 8:21 am
by ska
Hi everyone,

I've been looking through the forums to see who else has been looking to do something like this. Thought I'd start a new thread. Here are links to relevant forum posts that I've found;

http://www.devnetwork.net/forums/viewto ... light=pop3
http://www.devnetwork.net/forums/viewto ... light=pop3
http://www.evolt.org/article/Incoming_M ... /18/27914/
http://www.weberdev.com/get_example-4015.html
http://www.devnetwork.net/forums/viewto ... light=pop3
http://www.devnetwork.net/forums/viewto ... p3&start=0

I want to be able to check an email address using PHP and then store body, title, date, sender and attachments to a database (MySQL). I've fiddled around with a few scripts and got one working with plain text emails and no attachments. But HTML and attachments make it fall over.

Has anyone written a class for this..? So I could just specify;

POP3 server
username
password

database username
database password
database host

setup a MySQL table with approriate design.

and the rest just happens... ;)

Incidentally, here is my code. It's not 100% so wouldn't advise anyone using it just yet.

Code: Select all

function get_emails()
{
require("includes/pop3.php");

  /* Uncomment when using SASL authentication mechanisms */
	/*
	require("sasl.php");
	*/

	$pop3=new pop3_class;
	$pop3->hostname="localhost";            /* POP 3 server host name              */
	$pop3->port=110;                        /* POP 3 server host port              */
	$user="xxxx";                       /* Authentication user name            */
	$password="xxxx";                   /* Authentication password             */
	$pop3->realm="";                        /* Authentication realm or domain      */
	$pop3->workstation="";                  /* Workstation for NTLM authentication */
	$apop=0;                                /* Use APOP authentication             */
	$pop3->authentication_mechanism="USER"; /* SASL authentication mechanism       */
	$pop3->debug=0;                         /* Output debug information            */
	$pop3->html_debug=1;                    /* Debug information is in HTML        */

	if(($error=$pop3->Open())=="")
	{
		if(($error=$pop3->Login($user,$password,$apop))=="")
		{
			if(($error=$pop3->Statistics($messages,$size))=="")
			{
				//echo "<PRE>There are $messages messages in the mail box with a total of $size bytes.</PRE>\n";
				$result=$pop3->ListMessages("",0);
				if(GetType($result)=="array")
				{
					for(Reset($result),$message=0;$message<count($result);Next($result),$message++)
						
					$result=$pop3->ListMessages("",1);
					if(GetType($result)=="array")
					{
						for($message=0; $message<=count($result); $message++)
						{
							if(($error=$pop3->RetrieveMessage($message,$headers,$body,-2))=="")
							{
								for($line=0;$line<count($headers);$line++)
								{
									preg_match_all('/From: .*>/Uis',$headers[$line], $matches);
									if (count($matches[0][0]==1))
									{
										preg_match_all('/<.*>/Uis',$matches[0][0], $emailmatch);
										$fullmatch=$emailmatch[0][0];
										$emailmatch[0][0]=str_replace("<","",$emailmatch[0][0]);
										$emailmatch[0][0]=str_replace(">","",$emailmatch[0][0]);
										if (HtmlSpecialChars($emailmatch[0][0])!="")
										{
											$from=HtmlSpecialChars($emailmatch[0][0]);
											$fromname=str_replace("From: ","",$matches[0][0]);
											$fromname=str_replace($fullmatch,"",$fromname);
											$fromname=str_replace('"',"",$fromname);
										}
										
									}
									
								}
								
								for($line=0;$line<count($headers);$line++)
								{
									preg_match('/Subject: (.*?)/Uis',$headers[$line], $matches);
									if (count($matches[0]==1))
									{
										//preg_match_all('/<.*>/Uis',$matches[0][0], $emailmatch);
										//$emailmatch[0][0]=str_replace("<","",$emailmatch[0][0]);
										//$emailmatch[0][0]=str_replace(">","",$emailmatch[0][0]);
										if (HtmlSpecialChars($matches[0])!="")
										{
											$subject=HtmlSpecialChars($matches[1]);
										}
										
									}
									
								}
								
								$bodyfortest="";
								$htmlis=false;
								
								// for each line of the body
								for($line=0;$line<count($body);$line++)
								{
									// convert line to special characters, eg. <
									$bodyfortest.=$body[$line];
									
									//check each line for the presence of a body tag, case insenstive
									$found=preg_match("/<body>/i", $body[$line], $matchhtml);
									
									if ($found)
									{
										$htmlis=true;
									} 
								}
								
								

								if ($htmlis)
								{
									preg_match("/<title>(.*?)<\/title>.*?<body>(.*?)<\/body>/mis",$bodyfortest,$matchhtml);
									$body=$matchhtml[2];
									//echo $matchhtml[1];
									//echo $matchhtml[0];
									
									//echo $bodyfortest;
								}
								else
								{
									$body=$bodyfortest;
									
								}
								
									
								//echo "<br>";
								
								if(($error=$pop3->DeleteMessage(1))=="")
								{
									//echo "<PRE>Marked message 1 for deletion.</PRE>\n";
									//if(($error=$pop3->ResetDeletedMessages())=="")
									//{
										//echo "<PRE>Resetted the list of messages to be deleted.</PRE>\n";
									//}
								}
								
								
							//echo "subject: $subject<br />From: $from;<br /> Body: $body;<br /><br />";
							
							
							// connect to database and check for users with email address = $from
							
							
							dbConnect();
							
							$result = mysql_query("SELECT id AS emailid, type FROM email_identities WHERE email='$from'");
	
							if (!$result)
							{
								// Display (or not) an error or do custom error handling
								echo 'Query failed. <br />Error # ', mysql_errno(), ' Error msg: ', mysql_error();
								exit; // or do: return $isAuth
							}
							
							while($output = mysql_fetch_assoc($result))
							{
								extract($output, EXTR_OVERWRITE );
							}
							
							$datestamp= date('Y')."-".date('m')."-".date('d');
							$query="INSERT INTO";
							$query .= " emails SET datestamp='$datestamp', realname='$fromname', email='$from', subject='$subject', body='$body', emailid='$emailid', type='$type'";
							
							$result=mysql_query($query);

							
							if (!$result)
							{
								// no results - display manual selection instead
								echo 'Query failed. <br />Error # ', mysql_errno(), ' Error msg: ', mysql_error();
								exit;
							}
							
							mysql_close();
							
							
							}
							
						}
						if($error=="" && ($error=$pop3->Close())=="")
						{
						
						}
							//echo "<PRE>Disconnected from the POP3 server "".$pop3->hostname."".</PRE>\n";
					
					}
					else
						$error=$result;
				}
				else
					$error=$result;
			}
		}
	}
	if($error!="" && HtmlSpecialChars($error)!="Could not retrieve the message: Invalid message number.")
	{
		echo "<H2>Error: ",HtmlSpecialChars($error),"</H2>";
	}
}
And it of course requires;

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

Any pointers appreciated.

Posted: Wed Nov 08, 2006 8:58 am
by Chris Corbyn
Wow. Do you have access to the mail server configuration? It's much easier to do this in realtime by passing the information along to a PHP script as soon as it arrives.

Posted: Thu Nov 09, 2006 6:47 am
by ska
Wow. Do you have access to the mail server configuration? It's much easier to do this in realtime by passing the information along to a PHP script as soon as it arrives.
Thanks for reply. Nope, don't have access to mail server. It's on a shared host.

Posted: Thu Nov 09, 2006 7:36 am
by printf
Can you use the IMAP extension? If you can, I might have a class that can do this. You can use the database or file storage or use both, message parts are kept in the database and attachments are stored in the file system.

printf

Posted: Thu Nov 09, 2006 8:07 am
by ska
Can you use the IMAP extension? If you can, I might have a class that can do this. You can use the database or file storage or use both, message parts are kept in the database and attachments are stored in the file system.
Thanks printf. Yes, could use IMAP I think. Sounds like it might be what I'm after....? Would you mind posting, emailing or PMing so I can try...?

Posted: Sun Nov 12, 2006 6:53 am
by ska
Hiya all, I'm still on with this. I've just found this article that looks like it might be useful;

http://www.devshed.com/c/a/PHP/PHP-Email/2/

And this one too!

http://www.linuxscope.net/articles/mail ... tsPHP.html

Just thought I'd post here to chart my progress - someone else might find all this useful at some point.

Posted: Wed Nov 15, 2006 4:24 am
by ska
More useful links continued....

http://sourceforge.net/project/showfile ... _id=104718

This one looks pretty good! ;)