I had a problem where I needed to parse emails and insert them into a MySQL database in real time as they are received. This is a relatively easy problem to figure out, but I wanted to outline the steps I took to tackle this in case anyone else wanted simple, step by step instructions on how to solve this problem.
First of all, this solution uses Linux, specifically I like to use Ubuntu. I also used Postfix, an open source mail server. Finally, the mail script is based on Eric London's mail parsing script so he did all the mail heavy lifting. I simply added some database stuff to insert it into a MySQL table using PDO. Props to Eric for a great script that simplified this task for me.
To start, you will need a linux server. I use Amazon EC2 instances in the cloud so I just fire up one of alestic's AMIs and bam, server. I make sure the security preferences allow incoming on port 25 (SMTP). (Note: if you are having issues, it could be a security / firewall issue.) For testing purposes I use the micro AMI because they are inexpensive.
Once I have SSH-ed into the box, I run a few commands to make sure everything is up to date and that I have all the tools and software I need installed.
apt-get update && apt-get upgrade -y
tasksel install lamp-server
apt-get install postfix php5-cli
You don't actually need Apache for what we are doing here but I am installing the entire lamp stack for ease and simplicity and I will be using it later.
Let's create our database called email and the mail table where we will store the incoming email:
mysql -p
create database email
CREATE TABLE `mail` (
`mid` int(10) unsigned NOT NULL AUTO_INCREMENT,
`created` int(10) unsigned NOT NULL DEFAULT '0',
`body` longtext,
`return_path` varchar(255) DEFAULT NULL,
`x_original_to` varchar(255) DEFAULT NULL,
`delivered_to` varchar(255) DEFAULT NULL,
`mailto` varchar(255) DEFAULT NULL,
`subject` varchar(255) DEFAULT NULL,
`message_id` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
`maildate` int(11) DEFAULT NULL,
`mailfrom` varchar(255) DEFAULT NULL,
`added` tinyint(3) unsigned NOT NULL,
`content_transfer_encoding` varchar(255) DEFAULT NULL,
PRIMARY KEY (`mid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='incoming mail';
Its important that the default charset is UTF8 because this can be a problem if you are in the UK or other countries where character set issues can happen.
Now we are going to make a folder in our home directory and put our mail script in there:
mkdir /home/default/scripts
I have attached the mail.php script file to this post so grab it and put it in your scripts directory that you created above. (Its named mail.php_.txt, you you will need to rename it to mail.php.)
You will need to edit the configuration variables at the top of the file to connect to the database properly and send emails to your email address if there are errors. Another thing to keep in mind is that the script uses PDO so your version of PHP needs to support that (PHP 5.1 or greater).
Now we edit our Postfix aliases file to pipe mail from a specific account to a script that we will be added later:
nano /etc/aliases
Add a line at the bottom of the file:
import: "|php -q /home/default/scripts/mail.php"
Import is the name of the account so if you want the beginning of the email address (before the @) to be something different, change this.
Finally, you are going to need to setup some DNS entries to route mail to your linux server: an A and MX record. There are lots of resources on how to do this on the Internet. Lets say the email address you were using for this was import@server.domain.com, you would need two records to make this work:
server.domain.com. IN A 50.50.50.50
server.domain.com. IN MX 10 server.domain.com.
If you don't know what any of this means, hopefully you have access to a web interface that can set this up for you.
Assuming everything went well, you should be able to send email to your linux server and it will be inserting the mail into your database table. Enjoy!
