Sending an email notification when a record is changed

In some scenarios, you might wish to send an email notification to one or more recipients when a user adds a new record or modifies an existing one. Although I plan to add this feature to AppGini, I'd still like to share with you how to do it manually in the generated code. The reason is that manually coding it allows more flexibility and more options.

The scenario

So, let's assume the following scenario: we want to send an email to some recipients when an empoyee adds a new order to the Orders table of the Northwind application.

Let's do it!

To begin, we need first to open our project in AppGini and generate the PHP code. If you didn't use AppGini before, please have a look on this video tutorial to see how easy it is to do so. Next, we should open the folder containing the generated code. Open the file "orders_dml.php" in a text editor (since we'll work with the Orders table -- in general, look for the file named "tablename_dml.php" where tablename is the name of the table you want to apply the code changes to).

The "orders_dml.php" file contains some PHP functions. We're interested in the function for inserting new records, which is called orders_insert(). Here is how this function looks like as generated by AppGini:

function orders_insert(){
	/*
	  I removed some of the code here for brevity as
	  We don't need to edit this part of the code ...
	*/

	// mm: save ownership data
	$recID=mysql_insert_id();
	sql("insert into membership_userrecords set tableName='orders', pkValue='$recID', memberID='".getLoggedMemberID()."', dateAdded='".time()."', dateUpdated='".time()."', groupID='".getLoggedGroupID()."'", $eo);

	return (get_magic_quotes_gpc() ? stripslashes($recID) : $recID);
}

So, it's before the return line (line 11 above) where we should insert the code for sending the email. Now, there are many ways of writing this code. A clean way is to create a hook function that gets called after inserting new records. I plan to add this in future versions as it makes code cleaner and easier to manage. But for the sake of simplicity, we'll follow a more direct approach. We'll just add the email sending code like that:

function orders_insert(){
	/*
	  I removed some of the code here for brevity as
	  We don't need to edit this part of the code ...
	*/

	// mm: save ownership data
	$recID=mysql_insert_id();
	sql("insert into membership_userrecords set tableName='orders', pkValue='$recID', memberID='".getLoggedMemberID()."', dateAdded='".time()."', dateUpdated='".time()."', groupID='".getLoggedGroupID()."'", $eo);

	// our modified code for sending the email notification
	@mail(
		/* comma-separated list of recipients */
		"[email protected], [email protected]",

		/* message subject */
		"New order added",

		/* message contents */
		"A new record has been added by ".getLoggedMemberID().
		".\n\n". 
		"To view it, please go to:\n".
		"http://www.northwind.com/db/orders_view.php?SelectedID=$recID",

		/* we must add sender email similar to this */
		"From: [email protected]"
	);

	return (get_magic_quotes_gpc() ? stripslashes($recID) : $recID);
}

The above code sends a notification of new records to the two imaginary recipients typed in line 14. You should of course change those in your code. To make the message content more useful, we used the function getLoggedMemberID() in line 20 above to insert the username of the member who added the new record, and the variable $recID (which contains the ID of the new record) in line 23 to add a link to the new record.

We also needed to specify the mail sender in line 26. We used [email protected] but you should replace that with a sender that can actually send emails from your server to avoid marking the message as spam.

After making this modification, just save the changes and upload the generated code to your server. Now, whenever someone adds a new record to the table, an email will be sent to the recipient(s) you provided in the modified code. For the above code, here is an example of how this email would look like:

Other possibilities

To send a notification email when an existing record is modified, you can apply the same procedure above to the orders_update() function in the same file. To send a notification email when a record has been deleted, apply the same procedure to the orders_delete() function.

Sometimes, you might want to send the notification email to the user who added/modified the record. AppGini stores users' email addresses in the database table called membership_users. So, we need to query that table to get the user's email address. Here is the code above after modifying it to send a notification to the user who added the record:

function orders_insert(){
	/*
	  I removed some of the code here for brevity as
	  We don't need to edit this part of the code ...
	*/

	// mm: save ownership data
	$recID=mysql_insert_id();
	sql("insert into membership_userrecords set tableName='orders', pkValue='$recID', memberID='".getLoggedMemberID()."', dateAdded='".time()."', dateUpdated='".time()."', groupID='".getLoggedGroupID()."'", $eo);

	// our modified code for sending the email notification
	$userEmail=sqlValue("select email from membership_users where memberID='".getLoggedMemberID()."'");

	@mail(
		/* comma-separated list of recipients */
		"johndoe@northwind.com, janedark@northwind.com, $userEmail",

		/* message subject */
		"New order added",

		/* message contents */
		"A new record has been added by ".getLoggedMemberID().
		".\n\n". 
		"To view it, please go to:\n".
		"http://www.northwind.com/db/orders_view.php?SelectedID=$recID",

		/* we must add sender email similar to this */
		"From: [email protected]"
	);

	return (get_magic_quotes_gpc() ? stripslashes($recID) : $recID);
}

Line 12 above retrieves the user's email address into the variable $userEmail. Line 16 adds that email address to the recipients list.

I hope you've found this tutorial helpful. If you have any questions or feedback, please don't hesitate to contact me using the online support form.