I am trying to work out how to do something. It must be possible - I've seen something similar done in multiple systems (Twitter, facebook etc.)
The basic thing I want to do is - someone leaves a comment on my website. The comment goes to my email, as well as to my MySql database. I then reply directly on my email. The reply is saved to the database, and auto-approves the user's comment.
I can't work out how to speak to my MySql / PHP system directly from an email. Also - I can't work out how to set up the inboxes?
I am guessing that I'd need to set up mailserver rules so that emails sent to xxxx-response@mydomain.com are actually redirected to a php script, where xxxx is an identifier of the original comment?
Thanks for any light that you can shed...
You can't 'talk to mysql from an email'. An email is a passive piece of text, nothing that can take actions.
The solution is based on something like this:
You have an email account and poll that accounts Inbox by a php script in a periodical manner, by using wget or similar in combinition with a crontab entry. You can also create an event driven approach, but that is more complex and does not really offer advantages. For all messages found inside that inbox you check if it matches any previous entry in the database. You can do that since you have access to the messages headers and body (the text) by php. That is all you need. Typically the connection between two messages is done by evaluating the 'In-reply-to' header that is set by email programs when you press the reply button. It is like a reference.
The access to the accounts inbox is done by standard mail access protocols, typically pop3 or better imap4. When the mail server is not on the same system then abviously you want to add ssl encryption too. php's imap extension is a very good start for implementing such a client, it supports several protocols.
Assuming you're on a unix system, you could set up an account for receiving the mail, e.g. "fred", and use a .forward in fred's home directory to pipe emails to your php script. e.g.
.forward:
|"/usr/bin/php /path/to/your/script.php"
which will invoke your script anytime an email arrives in fred's mailbox.