Link to home
Start Free TrialLog in
Avatar of Benji_
Benji_Flag for United Kingdom of Great Britain and Northern Ireland

asked on

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
Avatar of arnold
arnold
Flag of United States of America image
There are open source ticketing system that process the email (pop) and add them into a database.

Avatar of Benji_
Benji_
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

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.
Avatar of rubene
rubene
Flag of Netherlands image
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);
?>
Avatar of Benji_
Benji_
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

That looks great, thank you for that. I shall test it out in a moment.
ASKER CERTIFIED SOLUTION
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Benji_
Benji_
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

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

Regards
Ben
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image
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

Avatar of Benji_
Benji_
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

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
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image
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.
Avatar of Benji_
Benji_
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Working Perfectly.
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image
Great!  Thanks for the points, ~Ray