DownloadMail

Hi Tim, 

Great questions. Download Mail takes care of the first step of downloading the mail and getting it into MySQL.  Once it's in MySQL we can forget about the fact that they're emails and just think of it as records in the database.

So for the todo example, the next step would be to write some code to move the records from the incoming mail table.  So some pseudo-code (untested) for that might be something like this:

// process todo emails
$todoEmails = mysql_select('incoming_mail', " to = 'todo@example.com ");
foreach ($todoEmails as $message) {

  // add to todos
  mysql_insert("todo_section", array(
    'todo_name' => $message['subject'],
    'todo_desc' => $message['text'],
  ));

  // remove from incoming mail
  mysql_delete('incoming_mail', $message['num']);
}

And you'd run that after /downloadMail/run.php or on a cron job every few minutes.

Finally - at a real push.  Would it be possible to add some kind of patterns to the subject line that adds custom fields. 

ie Subject:  Chase invoice ABC ##high##

-  title field = Chase invoice ABC, and 
 - priority field = high

Absolutely, all that's needed is a few lines of PHP code to match the patterns and then change the fields.  The way I do this is to write out examples and instructions on how to process the subject lines so that a human can do it.  (If it contains "this" do that, If it contains "the other thing" do something else, and if it doesn't match any of the rules then do something else).  It's then pretty quick to turn it into PHP code.

Hope that helps, let me know any questions.

Hey Dave,

Thanks for this.

I've added that code to the run.php, but I get an error: MySQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'to = 'todo@example.com'' at line 1 (at old_alias_functions.php line 142 in function mysql_select_query)

I've played with various combinations of ' and " but think it must be something else?

$todoEmails = mysql_select('incoming_mail', " to = 'todo@example.com' ");

Hi Tim,

That was untested mockup code so there will probably be a few issues, but I think that one is because the "Download Mail" fieldname of "to" is actually a reserved keyword in MySQL: http://dev.mysql.com/doc/mysqld-version-reference/en/mysqld-version-reference-reservedwords-5-5.html

MySQL is picky about us naming fields things like select, insert, etc.  Try adding `backticks` around it to let MySQL know that we mean it to be a fieldname, eg:

$todoEmails = mysql_select('incoming_mail', " `to` = 'todo@example.com' ");

Let me know if you run into any more issues.

PS:  If you want to use Download Mail on a project shortly, let me know and I can convert it to use the new "Background Tasks" cronjob system so you don't need to setup multiple cronjobs.  We're going to be porting everything over to that new system as we move forward.

Woo hoo.  done and working.

For anyone else interested, I also had to create all the default bits.

  // add to todos
  mysql_insert("to_do", array(
    'title' => $message['subject'],
    'content' => $message['text'],
    'createdDate' => $message['createdDate'],
    'updatedDate' => $message['createdDate'],
    'date_due' => $message['createdDate'],
    'createdByUserNum' => '1',
    'updatedByUserNum' => '1',
    'hidden' => '0',
  ));

 Now onto the pattern matching:)

Thanks Dave.

Some good stuff for the downoadMail plugin. Here's a question of my own:

I want to filter the mail records into an array - but only if the "from" email matches the email of a registered user in the users database?

The idea is that I am using DLMail to provide a feedback/letters page. I want all submissions from registered users to go live on the site immediatey - whereas any messages from unknown users would need to be "approved" first.

:0)

Perchpole

Hi, Dave -

Thanks for the code. It's a bit more complex than I had imagined! I'm looking for a solution I can apply before looping the results, like this:

<?php foreach($incoming_mail as $mail): ?>
<?if(incoming_mail "from" does not match accounts "email") {continue}; ?>
<?php echo $mail['subject'] ?>
<?php echo $mail['text'] ?>
etc...
<?php endforeach ?>

If the "from" email does not match an email in the accounts table it means the mail has been sent from an unregistered user. In this eventuality the foreach loop continues to the next row.

Does that makes sense?

:oS

Perch

Hi Perch, 

Ok, sure.  Here's some code to create an array of emails from the accounts table:

<?php
// get account emails
  $allAccounts = mysql_select('accounts');
  $allEmails = array_pluck($allAccounts, 'email');
  // showme($allEmails);
?>

And here's some to filter results based on that list:

<?php foreach ($incoming_mail as $mail): ?>
  <?php if (!in_array($incoming_mail['from'], $allEmails) ( continue; } // skip if email doesn't match ?>
  <?php echo $mail['subject'] ?><br/>
  <?php echo $mail['text'] ?><br/><br/>
<?php endforeach ?>

There's lots of issues to consider, like what happens if there's thousands of users (loading the email list might use a lot of memory), or what happens if spam or other emails start to build up in the mail table (eventually it would slow things down), or if users email from their home emails, etc, or names don't exactly match.  But the above code should help get you started.

Hope that helps!