Page 1 of 1

php/mysql ... and mail?

Posted: Mon Mar 07, 2005 7:41 pm
by harrisonad
hi guys,

i knew how to send email using mail() function but i am just wandering if an email can interact (eg. change data) with mysql.

to explain my problem further, let's say i am a part of a support group for a software company. people will email me asking about bugs or other technical problem they have regarding our software.

of course, i can check them and them log their problems to my mysql database, including the date of call, the problem, the sender, etc.

but what if, a php script will do the task for me. i mean automatically logging the email sent to the mysql database.

is this possible? please help me with this.

thank you in advance.

Posted: Mon Mar 07, 2005 7:46 pm
by feyd
Moved to PHP - Code.

Posted: Mon Mar 07, 2005 9:01 pm
by Burrito
sure, just have php pop to your mail server on set intervals to grab any new mail sent to a specified email address, read the mails and insert info accordingly.

we've done something very similar in the past for a helpdesk trouble ticketing system. People can send emails to pop3 account and it automatically logs a new ticket and throws the info straight into mysql.

ok...

Posted: Mon Mar 07, 2005 9:09 pm
by harrisonad
can you give me sample code..

Posted: Tue Mar 08, 2005 9:22 am
by Chris Corbyn
It would not be a ten line script dude....

Have a look through the imap_... functions on php.net, that should get you started. They have some examples of reading emails too.

IMAP functions in PHP support POP3 too.

http://www.php.net/imap/

Posted: Tue Mar 08, 2005 9:57 am
by Burrito
As d11 so elequently put it....dude it ain't 10 lines of code.

I can get you started here though...this is the function we use to pop to our mail server:

Code: Select all

function pop3_connect($popserver, $popport, $username = '', $pass = '', $apop = FALSE, $echocmd = FALSE, $echortrn = FALSE)
{
  global $errno, $errstr;
  if (empty($username) || empty($pass))
  {
    $errno = -2;
    $errstr = "Username and/or password not provided";
    return FALSE;
  }
  if ($echocmd) echo "CONNECTING TO {$popserver}:{$popport}\n";
  $handle = fsockopen($popserver, $popport, $errno, $errstr);
  if (!$handle)
  {
    if ($echocmd) echo "CONNECTION FAILED\n";
    return $handle;
  }
  stream_set_blocking($handle, TRUE);
  $response = fgets($handle, GETLENGTH);
  $response = pop3_killcrlf($response);
  if ($echortrn) echo "REPLY: {$response}\n";
  if (!pop3_isGoodReply($response))
  {
    if ($echocmd || $echortrn) echo "UNRECOGNIZED REPLY\n";
    pop3_quit($handle, $echocmd, $echortrn);
    $errno = -2;
    $errstr = "Server returned this unrecognized response: {$response}";
    return FALSE;
  }
  $confString = preg_replace("/^\+OK\s+<(.+)>/", "\\1", $response); //for apop
  
  $command = "NOOP\r\n";
  fwrite($handle, $command, strlen($command));
  if ($echocmd) echo "CMD: {$command}\n";
  $response = fgets($handle, GETLENGTH);
  $response = pop3_killcrlf($response);
  if ($echortrn) echo "REPLY: {$response}\n";
  if (pop3_isGoodReply($response))
  {
    if ($echocmd || $echortrn) echo "SERVER IS NOT RFC 1939 COMPLIANT\n";
    pop3_quit($handle, $echocmd, $echortrn);
    $errno = -2;
    $errstr = "POP3 Server did not comply with RFC 1939 for NOOP before authentication: {$response}";
    return FALSE;
  }
  
  if (!$apop)
  {
    $command = "USER {$username}\r\n";
    fwrite($handle, $command, strlen($command));
    if ($echocmd) echo "CMD: {$command}\n";
    $response = fgets($handle, GETLENGTH);
    $response = pop3_killcrlf($response);
    if ($echortrn) echo "REPLY: {$response}\n";
    if (!pop3_isGoodReply($response))
    {
      if ($echocmd || $echortrn) echo "USERNAME DID NOT WORK\n";
      pop3_quit($handle, $echocmd, $echortrn);
      $errno = -2;
      $errstr = "Username did not return OK: {$response}\n";
      return FALSE;
    }
    
    $command = "PASS {$pass}\r\n";
    fwrite($handle, $command, strlen($command));
    if ($echocmd) echo "CMD: {$command}\n";
    $response = fgets($handle, GETLENGTH);
    $response = pop3_killcrlf($response);
    if ($echortrn) echo "REPLY: {$response}\n";
    if (!pop3_isGoodReply($response))
    {
      if ($echocmd || $echortrn) echo "Password not authenticated\n";
      pop3_quit($handle, $echocmd, $echortrn);
      $errno = -2;
      $errstr = "Username or password not authenticated: {$response}\n";
      return FALSE;
    }
  }
  else //apop
  {
    $confString .= $pass;
    $confString = md5($confString);
    $command = "APOP {$username} {$confString}\r\n";
    fwrite($handle, $command, strlen($command));
    if ($echocmd) echo "CMD: {$command}\n";
    $response = fgets($handle, GETLENGTH);
    $response = pop3_killcrlf($response);
    if ($echortrn) echo "REPLY: {$response}\n";
    if (!pop3_isGoodReply($response))
    {
      if ($echocmd || $echortrn) echo "APOP LOGIN FAILED\n";
      pop3_quit($handle, $echocmd, $echortrn);
      $errno = -2;
      $errstr = "Apop login not authenticated: {$response}\n";
      return FALSE;
    }
  }
  
  //now do noop again just to be sure we're authenticated and the server works
  $command = "NOOP\r\n";
  fwrite($handle, $command, strlen($command));
  if ($echocmd) echo "CMD: {$command}\n";
  $response = fgets($handle, GETLENGTH);
  $response = pop3_killcrlf($response);
  if ($echortrn) echo "REPLY: {$response}\n";
  if (!pop3_isGoodReply($response))
  {
    if ($echocmd || $echortrn) echo "SERVER IS NOT RFC 1939 COMPLIANT\n";
    pop3_quit($handle, $echocmd, $echortrn);
    $errno = -2;
    $errstr = "POP3 Server did not comply with RFC 1939 for NOOP after authentication: {$response}";
    return FALSE;
  }
  
  set_time_limit(TIMEOUT);
  
  return $handle;
}

Posted: Tue Mar 08, 2005 7:28 pm
by harrisonad
mission update.....

mission accomplished!

thanks guys..

Posted: Tue Mar 08, 2005 7:31 pm
by Burrito
excellent, mind sharing how you got it done?