Email > Database?

[furious5]

I was wondering if anyone can help me, I have been asked to write a script which will allow a user to send an e-mail to a specified address and the content will be added directly to a database.

I've written a script to connect to a pop mail box, however I am unsure how I can extract the content to add in to the db.

Anyone got any ideas??

[Erkilite]

there may be a class/library already written somwhere. but as far as i know the only way to check email would be to use something like fsocketopen(). You would use the pop server address and connect on port 110. You then have to know a little about the pop protocol. try to telnet to you email server on port 110 then type

USER username [return]
PASS password [return]
LIST [return]

this should give you a list of current emails. then you can use the RETR (retrieve) command or the DELE (delete) command. Do a google for the POP Email RFC and it will explain all the commands for you.

also check here for an extension that will allow you to marse an email message easier http://pecl.php.net/package/mailparse.

just a side note: why not use java, the Mail API in java would be soooo much easier to use.

[leatherback]

Hi,

Use this to startfrom:

<?php
session_start();

   require("../includes/settings.php");   

   mysql_connect($db_host, $db_user, $db_password)
       or die ("<b>Couldn't connect to server</b>");
   mysql_select_db($db_name)
       or die ("<b>Error, no database</b>");

$name=          htmlspecialchars($_POST[name]);
$email=         htmlspecialchars($_POST[email]);

$query = "
  insert into YouTable
  (
YourColumNamesHere
)  

  values

(
TheValuesHere
)";


  $results = mysql_query($query)
       or ($error = mysql_error());

  if($results)
    {

 $mailbody .= "Some mail here";

  mail($email, "SomeSubject", $mailbody);

  }

[furious5]

Thanks for the code leatherback but I think we are at cross purposes here.

Basically I need to connect to a pop mail box and manipulate an e-mail, taking out the content and then inserting this in to a db.

Erkilite I have already found a script to connect to the pop mail box and am currently returning the number of messages, my problem is how can I strip the content as required.

As for using Java, I've never written any code in Java, so it probably wouldn't be that simple to do.

[Erkilite]

can you show me the script?

[furious5]

I've attached the files, they are from Manuel Lemos's PHP POP3 class

[Erkilite]

it looks pretty straight forward.

looking at his test_pop3.php

start at line 37 and you can modify his code to insert the info to the db instead of displaying it to the screen. it looks like everything is stored in the two variables $body and $headers. you may have to parse these to get what you are really looking for but it's there.

[furious5]

I agree it does look pretty straightforward, I'm just struggling getting my head around the concept of extracting the neccesarry parts from the e-mail and entering these in to the db.

[Erkilite]

what does the email look like?

better yet what does the db look like?

[furious5]

The email is simply a plain text e-mail at the moment.

The table has the following fields:

title
description

[Erkilite]

what information are you trying to save from the email?

[furious5]

I'm trying to get the information contained within the e-mail relating to the title and description.

[Erkilite]

is it all in the body or is it in the subject and body, what exactly does the email look like? has the format for that been decided?

[furious5]

It will all be in the body, however the format hasn't been decided as yet.

[Erkilite]

in that case

test_pop3.php would look like

starting at line 37

...
$record = new array();

$record = parse_body($body);

if(save_record($record)){
echo 'record saved';
} else {
echo 'error';
}
...

functions to write:

function parse_body($body){
$title='';
$description='';
//parse body of message, until you decide on a format you can't write this function

return array('title'=>$title,'description'=>$description);

}

function save_record($record){

if($dblink=mysql_connect('localhost','user','password')){
if(mysql_query('use '.$dbname.';',$dblink)){
$query='insert into sometable values(\''.$record['title'].'\',\''.$record['description'].'\');';
if(mysql_query($query,$dblink)){
return true;
} else {
echo 'error:'.mysql_error();
return false;
}
}
}

}

[furious5]

Hey Erkilite thanks for this.

Out of interest what format would you suggest the e-mail should be in, if you were to be doing this??

[weekender]

you don't have to access a POP box - on evolt.org there's an article that explains how you can have an email address forward incoming mail direct to a CLI php script. Then use the script to put the incoming mail into your db. Has the advantage of not having to manually/cron run the script, as it will run whenever an email arrives to that address.

http://www.evolt.org/article/Incoming_Mail_and_PHP/18/27914/index.html

adam