How to identify correct email to update proper database record?

Using PHP, I am making an application similar to an online concierge service. I have a page displaying a form asking the user what he would like me to do. When the user completes the form, the information is saved into a database and also the user is sent an email saying: "Thank you for recently submitting: (whatever he just submitted)." The user will (hopefully) be submitting this same form many times each month.

I would like to have the user have the ability to reply to my automated email with new information, and then that new information update the correct database record.

Problem: How do I get that new information to update the correct database record?

For example, lets say User 'John' submits that form twice today. First he requests me to make him dinner reservations at Chili's, and second he requests me to book him some airline tickets to NYC. John would receive 2 emails from the site thanking him for his requests. Lets say later today John changes his mind and decides he wants to go to Applebees instead. I would like him to be able to reply to the FIRST email stating his change. I would then like that information appended to the correct record in the Database. How do I make a connection between the information in the Database record and the email?

I was thinking as a possible solution I could include the Database's record ID number in the subject line of the email, like: "Thank you for your recent request #428." and then I could grab that number 428 and update record number 428 in the database. But I think this might confuse the user if he sees this number popping up out of no where.

I am looking for something similar to using a hidden input like you can do in Form's and processing POST data.

BTW: I am using CakePHP 2.1.x and am downloading my emails using IMAP. But the answer is probably not CakePHP specific.

Any suggestions? Thanks!

Answers


This is the usual way of doing it. It's often coupled with unique Reply-To and Sender addresses to add even more security and make it possible to track bounces.

It's pretty safe (why would anybody change the subject line?) but you should still make sure that only the correct sender address can do amendments, otherwise malicious users can just change the number and post stuff to whatever tickets they like.

It's easy for the user. The thing I would change is the wording of the subject, for two reasons:

  1. The request number is a technical number and should not be part of a sentence (in my opinion).
  2. If you are able to create a more telling subject line so that users can see which of the two mails are for which type of service, it would be much easier for the user to find and answer to the correct one of the two. Even if it's just a very generic category name that distinguishes them it may prove useful to many users.

With this in mind, a great subject line could be:

Dinner booking request - Automatic confirmation (#428)


Need Your Help

When using ASP.NET MVC and LINQ, should you be creating stored procedures in your SQL database?

asp.net asp.net-mvc linq stored-procedures database-design

This is something I don't understand. The conventional wisdom for stored procedures is to always create them if you know know a query that will be executed as part of your application and can descr...

Does Google reCaptcha use session?

php captcha recaptcha

I was using a captcha package called visualCaptcha in my php web app. But when I use it cross domain (front end and back end are in different domains) captcha sessions were not working.