store email body, title, content and attachments to MySQL

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
ska
Forum Commoner
Posts: 41
Joined: Mon Sep 05, 2005 4:54 pm

store email body, title, content and attachments to MySQL

Post 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.
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post 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.
ska
Forum Commoner
Posts: 41
Joined: Mon Sep 05, 2005 4:54 pm

Post 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.
printf
Forum Contributor
Posts: 173
Joined: Wed Jan 12, 2005 5:24 pm

Post 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
ska
Forum Commoner
Posts: 41
Joined: Mon Sep 05, 2005 4:54 pm

Post 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...?
ska
Forum Commoner
Posts: 41
Joined: Mon Sep 05, 2005 4:54 pm

Post 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.
ska
Forum Commoner
Posts: 41
Joined: Mon Sep 05, 2005 4:54 pm

Post by ska »

More useful links continued....

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

This one looks pretty good! ;)
Post Reply