Capturing data from e-mail into MySQL

Hello All.

I'm a complete novice with MySQL which may show. Let me paint the scene first to hopefully make things clearer.

A customer has a faulty piece of equipment and will be returning it for repair. The logistics between the end user and us is managed by a third party.

When issuing the replacement equipment to the end user, an e-mail is sent to us detailing the serial number of the faulty unit, the serial number of the replacement and the details of the end user.

The e-mail message is Pre-formatted which makes things easier, I hope.

Here is the question; how can I capture the crucial pieces of information from the e-mail content automatically?
Am I looking to employ an application to interrogate the mailbox and update the database?

Many thanks in advance, John.

 

So the 3rd party will send you an email with information, and from that email you want to extract information to your own database?

In this case, you could deploy a PHP script to read your mailbox contents into a variable. Here's how you do it: http://davidwalsh.name/gmail-php-imap. That tutorial is for Gmail, but it works for any IMAP mailbox (you probably have it). Just change the $hostname to your email server's hostname, path and port.

That script puts contents of email into a variable $output. You could then parse through $output and extract information, for example line by line, if indeed one piece of information is on each line. Here's how you do it: craiglotter.co.za/2011/02/08/php-loop-through-each-string-line-in-a-textarea/

To the foreach loop of the above script you'd then add simple SQL INSERT or UPDATE clause, that actually saves the extracted line to the database (and not only echo it, as in the example script).

Hope this helps a bit.