• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 563
  • Last Modified:

Using a linux shell script and or PHP: Import mail to MySQL

Hello, I have been spending a lot of time trying to work out how to create a system capable of importing emails from a POP/IMAP server, and loading them into our database. We only need data like date and time, subject, sender and body. There doesn't seem to be any scripts out there that can get the job done without me having to manually write one. I am not 100% experienced with PHP and I was wondering if there were any simple ways of getting emails that are sent to an address and loading them into a database.

The system can be done on the shell of a Linux box, or in PHP, or both.

Thanks
0
Benji_
Asked:
Benji_
1 Solution
 
arnoldCommented:
There are open source ticketing system that process the email (pop) and add them into a database.

0
 
Benji_Author Commented:
I am aware of this, but looking at these systems, it seems almost impossible to implement them into a separate system. The rationale is that it's easier to make from scratch than to strip down something that exists.

Thanks for the advice though.
0
 
rubeneCommented:
Hi Benji,

If you don't want to use third-party third party software, it shouldn't be too hard to do it with some standard PHP. Using the imap_* functions you could do something like this:

<?php
$resImap = imap_open("{your.mailserver.com:143/novalidate-cert}INBOX", "yourusername", "yourpassword");

$headers = imap_check($resImap);
$result = imap_fetch_overview($resImap, "1:" . $headers->Nmsgs, 0);

// ... loop through the messages in the $result here and insert them into your database

imap_close($resImap);
?>
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Benji_Author Commented:
That looks great, thank you for that. I shall test it out in a moment.
0
 
Ray PaseurCommented:
You can use a PHP "pipe" script.  The script will be started for each email message received.  Here is an example of one that I have used as a basis for other email processing applications.  Insert your unique code around line 55.

HTH, ~Ray
#!/usr/bin/php -q
<?php // /email_pipe/index.php


// THIS IS AN EMAIL PIPE SCRIPT.
// THIS SCRIPT IS STARTED AUTOMATICALLY FOR EACH MESSAGE.
// NOTE THAT THIS SCRIPT IS ABOVE THE /public_html/ DIRECTORY TO PREVENT ACCIDENTAL EXECUTION


// --> HOW DO WE KNOW WHICH EMAIL MESSAGES GET SENT HERE?
// THIS SCRIPT RECEIVES MESSAGES SENT TO email_pipe@your.org
// CREATE AN EMAIL MAILBOX EXCLUSIVELY FOR AUTOMATED PROCESSING.
// SET UP AN EMAIL FORWARD FOR THAT MAILBOX IN cPANEL->EMAIL LIKE THIS:
// 1...5...10...15...20...25...
// |/home/{account}/email_pipe/index.php


// --> WHEN YOU UPLOAD, THIS SCRIPT WILL BE MARKED RW-R-R BUT THAT IS WRONG
// THIS SCRIPT MUST BE MARKED EXECUTABLE x0755
// YOU CAN USE FTP SOFTWARE TO CHMOD TO RWX-RX-RX


// --> NOTE THE FIRST LINE OF THIS SCRIPT MUST SAY #!/usr/bin/php -q STARTING IN COLUMN ONE
// 1...5...10...15...20...25...
// #!/usr/bin/php -q
// <?php ... PROGRAM CODE FOLLOWS


error_reporting(E_ALL);

// USE THE OUTPUT BUFFER - THIS DOES NOT HAVE BROWSER OUTPUT
ob_start();

// COLLECT THE INFORMATION HERE
$raw_email = '';


// TRY TO READ THE EMAIL FROM STDIN
if (!$stdin = fopen("php://stdin", "R"))
{
    echo "ERROR: UNABLE TO OPEN php://stdin \n";
}

// ABLE TO READ THE MAIL
else
{
    while (!feof($stdin))
    {
        $raw_email .= fread($stdin, 4096);
    }
    fclose($stdin);
}


// REMOVE MULTIPLE BLANKS - AND OTHER PROCESSING AS MIGHT BE NEEDED
$raw_email = preg_replace('/ +/', ' ', $raw_email);

// SPEW WHAT WE GOT, IF ANYTHING, INTO THE OUTPUT BUFFER
var_dump($raw_email);

// CAPTURE THE OUTPUT BUFFER AND SEND IT TO SOMEONE ELSE VIA EMAIL
$buf = ob_get_contents();
mail ('you@your.org', 'EMAIL PIPE DATA', $buf);
ob_end_clean();

Open in new window

0
 
Benji_Author Commented:
Thank you for the input so far, how would we be able to handle attachments? By storing them as blobs

Regards
Ben
0
 
Ray PaseurCommented:
I would think you might want to store the attachments in ways that made sense for the file type of the attachment.  Typically attachments are a type of file that has been base64 encoded.  So you might decode the attachments and store them in the file system.  You can use a little junction table to coordinate the emails to the list of URLs that point to the attachments.  You can store the attachments in the form of blobs, but if you store them as blobs you can expect your data base to get slow and bloated.  And eventually someone will write the inevitable SELECT * query.  When that happens, your data base engine will have to read every single attachment from every single email just to complete the query.

When you get attachments, the body of the email will look something like this (it is a CSV file for import into a data base).  Your script would isolate this:

boundary=0016364d1d69d4562804a3322b3c

Using that you would be able to find the start and end of the attachment string.
Content-Type: multipart/mixed; boundary=0016364d1d69d4562804a3322b3c

--0016364d1d69d4562804a3322b3c
Content-Type: multipart/alternative; boundary=0016364d1d69d4562104a3322b3a

--0016364d1d69d4562104a3322b3a
Content-Type: text/plain; charset=ISO-8859-1



--0016364d1d69d4562104a3322b3a
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

<p>=A0</p>

--0016364d1d69d4562104a3322b3a--
--0016364d1d69d4562804a3322b3c
Content-Type: application/octet-stream; name="Daily Pump Sales csv.CSV"
Content-Disposition: attachment; filename="Daily Pump Sales csv.CSV"
Content-Transfer-Encoding: base64
X-Attachment-Id: 90e0fb2e3e3dbbe5_0.1

U2l0ZSBDb2RlLFNpdGUgTmFtZSxTYWxlcyBEYXRlLFByb2R1Y3QgQ29kZSxQcm9kdWN0IE5hbWUs
Vm9sdW1lDQoxODUxNjQsIFNUUkFURk9SRCwxMy8wNS8yMDExIDA6MDAsMzIwMDIsOTEsMTI1MjEu
NzQNCjE4Mzk3MiwgUEFMTSBCRUFDSCwxMy8wNS8yMDExIDA6MDAsMzIwMDIsOTEsMTYwMjIuMTUN
CjEwODA0NiwgQ1VSTEVUVFMgUk9BRCwxMy8wNS8yMDExIDA6MDAsMzIwMDIsOTEsMTU3MzguMzgN
CjEwNTE0OCwgTUlSQU1BUiwxMy8wNS8yMDExIDA6MDAsMzIwMDIsOTEsMTExMjEuMDkNCjEwMDIz
MiwgUklWRVJIRUFELDEzLzA1LzIwMTEgMDowMCwzMjAwMiw5MSw0Nzk2Ljc2DQosLCwsLA0KLCws
LCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCws
LA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCws
DQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwN
CiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0K
LCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQos
LCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiws
LCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCws
LCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCws
LA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCws
DQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwN
CiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0K
LCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQos
LCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiws
LCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCws
LCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCws
LA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCws
DQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwN
CiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0K
LCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQos
LCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiws
LCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCws
LCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCws
LA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCws
DQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwN
CiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0K
LCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQos
LCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiws
LCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCws
LCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCws
LA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCws
DQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwN
CiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0K
LCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQos
LCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiws
LCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCws
LCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCws
LA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCws
DQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwN
CiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0K
LCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQos
LCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiws
LCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCws
LCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCws
LA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCws
DQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwN
CiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0K
LCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQos
LCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiws
LCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCws
LCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCws
LA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCws
DQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwN
CiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0K
LCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQos
LCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiws
LCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCws
LCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCws
LA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCws
DQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwN
CiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0K
LCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQos
LCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiws
LCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCws
LCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCws
LA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCws
DQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwN
CiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0KLCwsLCwNCiwsLCwsDQosLCwsLA0K
LCwsLCwNCiwsLCwsDQo=
--0016364d1d69d4562804a3322b3c--

Open in new window

0
 
Benji_Author Commented:
Hi Ray,
Thanks for your advise on this, what im trying to achive is a way of easily backing the files up because this is going to be a multi location system which heartbeats between locations,  for high availability.

So i was presuming the database would be the easiest option to transport these files, generally the files are only around 5kb each.

Regards
Ben
0
 
Ray PaseurCommented:
The file size is one of the factors in the design.  The number of files is a multiplier.  

Email is a good way of transporting messages from one human being to another, but it might not be the design pattern I would use for data base replication or duplication.  Some of the factors to consider include (1) arrival rate of data base transactions, (2) timeliness of data.  A bespoke system that made a set of POST requests to each node of the data base network might be a better design.  Not sure without looking into the app design at a fairly detailed level.  It might be good to have a DBA involved in the design of this kind of system.
0
 
Benji_Author Commented:
Working Perfectly.
0
 
Ray PaseurCommented:
Great!  Thanks for the points, ~Ray
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now