Update database with message sent over email

Hello Guys,

Some online help desks provide a facility for users to reply back to a message over email. I mean a user can simply “reply” back to the notification email and the online database gets updated automatically.

Is there some shell script that tracks the email server, and then update the database with the email message? How can this be achieved?

Any suggestions would be very helpful.

Thank you.


PS: The emails have this sort of text and if we write anything above this line, it gets recorded in the database.

## In replies all text above this line is added to the ticket ##


you can update your database if the email is viewed by someone.
You can place an 1px image tag in your email.

<img src=“http://yoursite.com/update.php?status=opened” height=“1” width=“1”>
then you can simply read the status using GET method and update the database.

Note the email must be in html format.

Hi Bishwa,

Thanks for your message however I am looking to record the entire message in database just like most of the online helpdesks / ticket solutions work.

Any suggestions please…


They tend to use the ticket number (or identifier) in the subject line, and maybe the recipients email address too for sanity.

Once a response is received, parse out the information and update the ticket.

Bear mind though, that many clients may send the response in RTF, Plain text or even HTML.

All of which will need to be handled different.

Hi Anthony,

Thanks for your message. I think the process of parsing email text is automatic as the online ticket gets updated instantly whenever an email is sent.

I was wondering if there is some Linux shell script that triggers database update whenever the email server receives some message. I am not sure if there is some other process. Can anybody please suggest a solution?

Thank you.

Either a CRON job will run on a frequent basis and scan the mailbox for new entries in invoke the update process, or there will be a hook in the mail server software to do the invoking upon receipt.


I’ve always wondered how they achieved this as well. How do they use PHP to access emails on the mail server, read the headers and scrape the important information? The database interaction isn’t hard to figure out.

I found this via Google, but it’s from 2004…

And possibly this from PHPClasses.org

Anyone have any ‘best practices’ example code?


What you’re looking for is called email piping. It means that incoming email messages are passed to a script (PHP or other) rather than (or and) delivered to inbox.

Google email piping for details.


Taking your advice, I found a very nice and simple tutorial…

It even involves cPanel directions…which makes life even easier for me.

Thanks again,

No way, man, that is fraught with loads of problems.

Try using the imap functions.

Here is the basis of some stuff I’m currently working on. This script is to be used to take email attachments from certain customers that are allowed to send us their inventories via email. I take the attachment, save it in a directory and then insert a record into a database so another cron job can process the attachment.


  include "libs/database/db_mysql.php";
  $db = new Database;

  $mailbox=imap_open($mailhost,$mailuser,$mailpass) or die("&lt;br /&gt;\
FAILLED! ".imap_last_error());

  // The IMAP.xml file contains the email address and user_id of the users that we accept 
  // their files via email 
  $xml = simplexml_load_string(file_get_contents('IMAP.xml'));
  $result = $xml-&gt;xpath('item');
  while(list( , $node) = each($result)) {
    $email   = $node-&gt;LI_email;
    $user_id = $node-&gt;LI_user_id;
    $search  = "RECENT FROM \\"$email\\"";

    $result2 = imap_search($mailbox, $search);
    if($result2) {
      $index = $result2[0];
      $structure = imap_fetchstructure($mailbox, $index);
      $attachments = array();
      if(isset($structure-&gt;parts) && count($structure-&gt;parts)) {
        for($i = 0; $i &lt; count($structure-&gt;parts); $i++) {
          $attachments[$i] = array(
            'is_attachment' =&gt; false,
            'filename' =&gt; '',
            'name' =&gt; '',
            'attachment' =&gt; '');
          if($structure-&gt;parts[$i]-&gt;ifdparameters) {
            foreach($structure-&gt;parts[$i]-&gt;dparameters as $object) {
              if(strtolower($object-&gt;attribute) == 'filename') {
                $attachments[$i]['is_attachment'] = true;
                $attachments[$i]['filename'] = $object-&gt;value;
          if($structure-&gt;parts[$i]-&gt;ifparameters) {
            foreach($structure-&gt;parts[$i]-&gt;parameters as $object) {
              if(strtolower($object-&gt;attribute) == 'name') {
                $attachments[$i]['is_attachment'] = true;
                $attachments[$i]['name'] = $object-&gt;value;
          if($attachments[$i]['is_attachment']) {
            $attachments[$i]['attachment'] = imap_fetchbody($mailbox, $index, $i+1, FT_PEEK);
            if($structure-&gt;parts[$i]-&gt;encoding == 3) { // 3 = BASE64
              $attachments[$i]['attachment'] = base64_decode($attachments[$i]['attachment']);
            elseif($structure-&gt;parts[$i]-&gt;encoding == 4) { // 4 = QUOTED-PRINTABLE
              $attachments[$i]['attachment'] = quoted_printable_decode($attachments[$i]['attachment']);
        } // for($i = 0; $i &lt; count($structure-&gt;parts); $i++)
      } // if(isset($structure-&gt;parts) && count($structure-&gt;parts))

      // Now add a record into the file_upload table
      for($i = 0; $i &lt; count($attachments); $i++) {
        if (strlen(trim($attachments[$i]['filename'])) &gt; 0) {
          $path_parts = pathinfo($attachments[$i]['filename']);
          // Some users zip their inventory files, so we have to unzip them
          if(strtolower($path_parts['extension']) == 'zip') {
            // copy the zip attachment to the temp dir
            $filename = 'file_uploads/temp/'.$user_id.'_'.$path_parts['filename'].'_'.date('m_d_Y').'.'.$path_parts['extension'];
            $fp = fopen($filename, "w");
            fwrite($fp, $attachments[$i]['attachment']);
            // open the zip file
            $zip = new ZipArchive();
            if ($zip-&gt;open($filename) !== TRUE) {
              die ('Could not open archive');
            $zippedfile = $zip-&gt;statIndex(0);
            $path_parts = pathinfo($zippedfile['name']);
            $newfilename = 'file_uploads/'.$user_id.'_'.$path_parts['filename'].'_'.date('m_d_Y').'.'.$path_parts['extension'];
            // extract the parts
            $zip-&gt;extractTo('file_uploads/', $zippedfile['name']);
            // delete the zip file     
            // I need to rename the file to our naming convention
            rename('file_uploads/'.$zippedfile['name'], $newfilename);
            $filestr = preg_replace('`[\\r\
", file_get_contents($newfilename));
            $fp = fopen($newfilename, "w+");
            fwrite($fp, $filestr);
            // now process just like regular CSV or delimited files
            // remove the directory from the filename
            $filename = $user_id.'_'.$path_parts['filename'].'_'.date('m_d_Y').'.'.$path_parts['extension'];
            // Now insert into file_upload table
            $insert_sql = "INSERT INTO `file_uploads` VALUES(NULL,$user_id, '$filename', 0, NOW())";
            $db-&gt;query($insert_sql) or die("Can't insert record");
          } else {
            $filename = 'file_uploads/'.$user_id.'_'.$path_parts['filename'].'_'.date('m_d_Y').'.'.$path_parts['extension'];
            $fp = fopen($filename, "w");
            $attachments[$i]['attachment'] = preg_replace('`[\\r\
            fwrite($fp, $attachments[$i]['attachment']);
            // remove the directory from the filename
            $filename = $user_id.'_'.$path_parts['filename'].'_'.date('m_d_Y').'.'.$path_parts['extension'];
            // Now insert into file_upload table
            $insert_sql = "INSERT INTO `file_uploads` VALUES(NULL,$user_id, '$filename', 0, NOW())";
            $db-&gt;query($insert_sql) or die("Can't insert record:".mysql_error());
        } // if (strlen(trim($attachments['name'])) &gt; 0
      } // for($i = 0; $i &lt; count($attachments); $i++)
      // This is a stop gap to circumvent the message being processed twice
      imap_setflag_full($mailbox, "$index", "\\Seen \\Flagged");

      // Now move the message to completed uploads mailbox
      // imap_mail_move copies the message and then sets the deleted flag.
      // Won't work with POP3 accounts
      // imap_mail_move($mailbox, "$index", "INBOX/completed+uploads") or die("can't move: ".imap_last_error()); 

      // This clear out the deleted messages
      // imap_expunge($mailbox);
    } // if($result2)
  } // while(list( , $node) = each($result))

I originally wrote something like this to take scores for a baseball league. The managers sent their scores in using a specific format in the subject and I parsed the scores into our games table. Very clean and functional.

What problems you think there might be with piping?

Why do you think it’s better than piping?