control mysql through email?

On and off I've been trying to figure out a way to beable to upload some files to my mysql db by attatching them in an email.

I have procmail set up to redirect mail with a certain subject to a php script, but I have no idea what to do from there.

Has anybody here done something like this before?

We do lots of stuff like that (for instance, we use a perl script for taking emails you send to support and sticking them in a database).

I'm not a PHP fan, but it's pretty insanely easy in perl. Just connect to your database like you would in any other script, read STDIN and, if you want, to crude header parsing. Then just stick the whole shebang into the database.

You'll pretty much want to do the same thing in PHP. But perl is better at this sort of thing.


nate.

I am trying to use procmail to redirect but am having trouble. Scripting languages aside, should I be using some other means to redirect?

Here's my procmailrc file:

MAILDIR=$HOME/Maildir
LOGFILE=${HOME}/pm.log
VERBOSE=yes
SHELL=/bin/sh


# filter 0
:0
* ^subject:.*appraisal
| /usr/local/bin/php /PATHTOSCRIPT/email_test2.php



# catch all for inbox
:0
$HOME/Maildir/


Essentially it just throws things matching the subject to the script, which the beginning looks like this:


#!/usr/local/bin/php
<?
// read from stdin
$fd = fopen("php://stdin", "r");
$email = "";
while (!feof($fd)) {
$email .= fread($fd, 1024);
}
fclose($fd);


however, it isn't called properly. Here's what the log shows.

procmail: [15012] Mon Jul 11 17:28:59 2005
procmail: Assigning "SHELL=/bin/sh"
procmail: Match on "^subject:.*appraisal"
procmail: Assigning "LASTFOLDER=/usr/local/bin/php /PATHTOSCRIPT/email_test2.php"
procmail: Executing "/usr/local/bin/php,/PATHTOSCRIPT/email_test2.php"
procmail: Notified comsat: "inhouseapps@:/usr/local/bin/php /PATHTOSCRIPT/email_test2.php"
From tansofun@fastmail.fm Mon Jul 11 17:28:59 2005
Subject: appraisal
Folder: /usr/local/bin/php /PATHTOSCRIPT/em 1271
/usr/local/bin/php: /usr/local/bin/php: No such file or directory


So it doesnt look like procmail is working properly; are you using sendmail or some other means of redirection? should i use them instead?

> Folder: /usr/local/bin/php /PATHTOSCRIPT/em 1271
> /usr/local/bin/php: /usr/local/bin/php: No such file or directory

Procmail's working properly; php just doesn't exist on the mail server, or at least not in that place.

php isn't installed on most of the mail servers. I don't think there's really been an official decision (it might be still installed on some of the mail servers), but you can't reliably expect it to be there. There are some posts in the "archives" of this forum about that; you can get around this by pointing to your own PHP install, though it will take some extra work - but you're probably better off using Perl or Python.

Also, you can't just take stdin from the email and stick it into a database.

First off, you would need to extract the file from the email... assuming the file is a MIME attachment, you could do this in Perl using Mimetools - there are probably similar tools for PHP (though see above). If it's not a MIME attachment (rare, these days), I think you'll want to extract the attachment and uudecode it.

See also:
http://www.onlamp.com/pub/a/php/2000/09/15/php_mysql.html
for some information on putting binary data into a MySQL database if I'm reading your post correctly and that's what you're trying to do (maybe you could be more specific about what you're trying to do?).

If at all possible, I'd suggest putting the files in a directory and just putting a pointer to the file's path in the database.Edited by will on 07/12/05 00:11 AM (server time).

I'd like to thank everybody for the help they've given so far, and alas I still don't have this working. So, a couple questions.

I do have my own version of php compiled, so I pointed it to my version and I get an error, but more on that later.

If I use /usr/local/bin/php, and php isn't installed on the mail server, how does it know that
/PATH/TO/myphp/ is on my server and not the mail server? Just curious.

I'm not very familiar with linux, so, when you suggest using perl or python, I assume perl comes standard with most distros so I could run my script from the mail server?

The error I get after changing the script and rc file to be running my version of php is:
/home/PATH/TO/cgi-bin/php.cgi: error while loading shared libraries: libmysqlclient.so.14: cannot open shared object file: No such file or directory

Funny thing is, my script so far is just a skeleton and isn't doing anything mysql related. I installed using dreamhosts wiki

http://wiki.dreamhost.com/index.php/Installing_PHP5

and it looks like there's mysql support in there, the end of the configure line is
--with-mysql=/usr --with-mysqli

and php, as far as I knew, came with mysql support. My site, through htaccess files, is pointed at my compiled version of php, and does all sorts of sql stuff just fine, so now I'm wondering if there is something I seriously overlooked.

Maybe its time to learn perl, though this stuff is really bothering me, and I'd like to get down to the bottom of it.