Email to a Web Application?

Can someone tell me how I can do the following:

My company built its own project management system with Linux, Apache, MySQL and PHP. In the web application's database, there is a table called t_project that stores information on all the company's projects. My boss wants me to devise a solution where if someone sends an email to (t_project.project_id)@mysite.com, the web application will write the email to a database table called t_project_message.

How do I solve this problem? What do I need to learn?

Additional notes I just noticed that the text in my question dropped "(t_project.project_id)@mysite.com" which I've added back. So does this mean i will need as many email accounts as there are projects in my database? That could be tens of thousands...

What email server are you running on the PHP host? For several PHP applications I am responsible for I use Exim, and with Exim I setup a transport&router. This means Exim will accept the message and then it will run a PHP script and pass the message to the PHP script STDIN. The PHP script then does things based on the contents of the message.

The below router/config will accept messages to [email protected], [email protected], and tckt-{id#}@helpdesk.example.org. With Exim the transport and router configuration is extremely flexible, the below is just one possible way that you might configure this. I doubt what I have configured would apply directly to your project, you probably would need to read up on Exim. I am also pretty sure other mail servers are also able to do the same thing. I am just not familiar with them.

### router/050_helpdesk_router
#################################
helpdesk_router:
  driver = accept
  domains = helpdesk.example.org
  caseful_local_part = true
  local_parts = lsearch;/etc/exim4/helpdesk
  local_part_suffix_optional
  local_part_suffix = -*
  transport = helpdesk_transport


### transport/20_helpdesk_router
#################################

helpdesk_transport:
  driver = pipe
  command = /usr/bin/php ${lookup{$local_part}lsearch{/etc/exim4/helpdesk}} $local_part '${if def:local_part_suffix {$local_part_suffix}}'
  # return the data from the script on a failure
  return_fail_output = true
  # temporary error
  temp_errors = 10
  # set a timeout for 30 seconds
  timeout = 30s
  current_directory = /home/helpdeskadmin/
  home_directory = /home/helpdeskadmin/
  user = helpdeskadmin
  group = helpdeskadmin

### /etc/exim4/helpdesk
#################################
helpdesk:   /srv/www/helpdesk.example.org/www/helpdesk/mail/recv.php
tckt:       /srv/www/helpdesk.example.org/www/helpdesk/mail/recv.php

To give a starting point on the mail parsing portion of the question. I use Zend_Mail for parsing the incoming message, and sending mail.

Would you have time to also tell me advantages/disadvantages of your method vs. Ben's method?

With a hook into the MTA like I describe.

• There is no polling inteval. When the sending mail server connects your process will immediatly be started to accept the message. With a mailbox and polling you will have to wait until the next polling cycle.
• You can reject or respond to invalid messages immediatly and not risk the potential of your application being a source of back-scatter. Perhaps you may want to bounce the message if the address is not quite right.
• You said you had lots of projects. With a transport you don't have to create lots of mailboxes, and poll each mailbox seperately, if you define your transport properly you can get one transport configuration to handle everything.

Typically with PHP one does mailbox polling from cron. From cron you would have to be very careful to make sure that your polling does some form of locking so that you don't respond to or process the same message twice.

The way I've done this frequently in the past is to run fetchmail every X minutes via cron. Fetchmail will retrieve the mail via IMAP or POP3 and can then pipe the mail through a handler/parser script of some type (usually written in perl, python, etc.). The parser script would pull out any data you need out of the message and then insert that information into the appropriate places in your database.

Dependant on your mail server, you could write a PHP script that will poll a mailbox on the server with IMAP and take actions accordingly.

There is an IMAP extension available for PHP which can get email messages from the server, as well as move them around on the server or delete them completely when you've committed it to the database.

You can schedule this as appropriate with cron.