Page 1 of 1

Update database from a link in an email

Posted: Sat Jan 09, 2010 9:14 pm
by craigwg
I have a blog I have created and I recently built a comment section so users can make comments. Of course robots leave random comments and links which I don't want. Thus I have added a comment column to my database called "Approved" and mark it so approved comments are visible, otherwise the comments stay in the database but are unapproved and don't appear. Pretty standard.

The problem is this: Everytime someone leaves a comment, I get an email that a comment was left from using the mail function in php. I then have to manually sign into phpmyadmin and change the database to approve the comment. Is it possible for the email I receive to contain a link that changes the database for me? I mean, I think that would mean building a connection to the database in the email but that doesn't seem wise. I realize I could build another admin page but that is not ideal. I just want to click a link from the email itself and approve the comment so its visible. Is that possible?

Re: Update database from a link in an email

Posted: Sat Jan 09, 2010 10:52 pm
by SidewinderX
Yes, it is possible.

If you were to create a file called approve.php and add the content:

Code: Select all

if($_GET['id'] != "") {
    //establish a database connection
    //update the record WHERE `id` = escape_me($_GET['id'])
}
However, this is obviously insecure because if someone knew the link, they could update any record they wanted. So you might want to implement some kind of password key to pass along or use sessions - sessions preferred.

Re: Update database from a link in an email

Posted: Sun Jan 10, 2010 7:54 pm
by craigwg
This idea works great. I implemented it with success. What about other options? Is it possible to do it straight from the email?

Re: Update database from a link in an email

Posted: Sun Jan 10, 2010 9:26 pm
by SidewinderX
Are you talking about embedding some kind of application within an email? If so, that is not possible.

Re: Update database from a link in an email

Posted: Sun Jan 10, 2010 9:57 pm
by craigwg
Depends on how you define application. I just want to send a single update query to a database by clicking a link in an email. Is that an application? At what point does php become an application? If it isn't possible, why isn't it possible?

Craig

Re: Update database from a link in an email

Posted: Sun Jan 10, 2010 10:06 pm
by SidewinderX
If you implemented the idea I suggested, records can be updated by manipulating the the id request value in the url. Thus, by navigating the browser to the url http://www.example.com/approve.php?id=6 (where example.com corresponds to your domain name) will "approve" the comment with id equal to six. Now it is just a matter of adding that link to the email that is generated when a comment is created.

I just wanted to mention again, that without using sessions or some "password" this is insecure.