EMAIL CUSTOMERS AUTOMATICALLY IN PDG COMMERCE USING MYSQL TRIGGERS & BUBBLEGUM

We recently selected PDG Commerce as the solution for one of our customers. However, this client had a number of requirements that fell outside of the scope of PDG’s offering. Some of the additions we needed to make were located in the administration portion of the website where a lot of the code is compiled in a CGI making direct alterations and additions nearly impossible.

In what will surely be series of articles on the subject of PDG Commerce, I’m first going to tackle an automatic email generator for notifying customers that their membership category has been elevated by the site administrator. Since we aren’t able to simply modify the customer admin page directly, we will have to rely on a function of MySQL called a trigger. A trigger as defined by mysql.com is “… a named database object that is associated with a table, and that activates when a particular event occurs for the table.” In this case the event that we are interested in is when customer’s category is changed from “Anonymous” to “Reseller”. Before we go any further, let’s enjoy a fresh piece of bubblegum. Mmmm. We should be done with this before the flavor’s gone! Let’s go!

The first thing we’ll want to do is create a new table in our PDG database for the trigger to update.

CREATE TABLE IF NOT EXISTS `EMAIL_CUSTOMERS` (
`email_customer_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`CU_CustomerID` int(11) NOT NULL,
`emailed` int(1) NOT NULL DEFAULT ‘0’,
PRIMARY KEY (`email_customer_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=0 ;

The new table is just going to consist of an auto incrementing id, the CU_CustomerID as taken from the CUSTOMERS table, and the column ’emailed’ that defaults to zero. We’ll be using that to ensure that the user only receives one email.

Now let’s create our trigger… If you’re using phpMyAdmin, choose the SQL tab and paste the following in the form.

Note: Be sure to change your delimiter to something that’s not a semicolon (like $$ for instance.)

Note: MySQL triggers require Superuser privileges.

CREATE TRIGGER customer_trigger AFTER UPDATE ON CUSTOMERS
FOR EACH ROW
BEGIN
DECLARE send_email INT DEFAULT 0;
IF (new.CU_CustCatID=2 AND old.CU_CustCatID=1) THEN
SET send_email = 1;
END IF;
IF
send_email = 1 THEN
INSERT INTO EMAIL_CUSTOMERS (CU_CustomerID, emailed) VALUES (new.CU_CustomerID, 0);
END IF;
END;

In a trigger you can use OLD.col_name to refer to the columns of a row before they were updated and NEW.col_name to refer to the columns of the row after they were updated. That’s what’s going on in the 5th line. We’re just looking to see if the CU_CustCatID was changed by the admin from “Anonymous”(1) to “Reseller”(2). If so, we set our variable ‘send_mail’ to ‘1’. We then insert into our EMAIL_CUSTOMERS table the CU_CustomerID from CUSTOMERS.

FYI

You can check out your new trigger by issuing the command SHOW TRIGGERS;.
If you need to drop a trigger simply issue this command DROP TRIGGER triggername;

Try changing the status of a user from “Anonymous” to “Reseller” in your Administration page of PDG to test it out. You can then check your EMAIL_CUSTOMER table. If all went well you should see a new row with the CU_CustomerID data is there for that user. You’ll also notice the emailedcolumn is set to 0 so we have something to check whether or not to email that customer.

Next we’re going create a new PHP file using the code below. Since we are going to be eventually running this PHP file in cron, take notice of line one. Without it, the cron won’t know what type of file this is.

#!/usr/bin/php -q
<?php
// functions for opening and closing the database…
function dbopen() {
	global $dblink;
	$dbhost = "localhost";
	$dbname = "pdgdatabasename";
	$dbuser = "databaseuser";
	$dbpass = "databasepass";
	$dblink = mysql_connect($dbhost, $dbuser, $dbpass) or die("Database Unavailable");
	mysql_select_db($dbname, $dblink);
}
function dbclose() {
	global $dblink;
	mysql_close($dblink);
}

dbopen();
// this query will save a few steps by selecting only the customers that are in our EMAIL_CUSTOMERS table that haven't been emailed
$result=mysql_query("SELECT * FROM CUSTOMERS INNER JOIN EMAIL_CUSTOMERS ON EMAIL_CUSTOMERS.CU_CustomerID=CUSTOMERS.CU_CustomerID WHERE emailed=0")
or die(mysql_error());

while($what = mysql_fetch_array($result))
	{
	extract($what);
		// Mail Variables
		$subject = 'Account Upgrade';
		// User Message
		$message = '<h2>Congratulations and Welcome</h2>';
		$message .= '

Your membership application has been approved and your account status has been elevated to Reseller.

';
		// Here we'll can even send the users login information.
		$message .= '

TIP: Remember to login at http://www.yourdomain.com Your User Name is: ‘ . $CU_UserName . ‘ and your password is: ‘ . $CU_Password . ‘

';
			
		// User Headers
		// To send HTML mail, the Content-type header must be set
		$headers  = 'MIME-Version: 1.0' . "\r\n";
		$headers .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";
		$headers .= 'From: Your Website Name <info@yourdomain.com>' . "\r\n";
				 
		// Send email to Customer
		$cust = mail($CU_Email, $subject, $message, $headers);
		// Display Message
			if ($cust)
				echo 'sent';
			else
				echo 'failed';
		// END OF MAIL
		// NOW LET'S MAKE SURE THIS PERSON ISN'T MAILED AGAIN
		
		mysql_query("UPDATE EMAIL_CUSTOMERS SET emailed=1 WHERE CU_CustomerID='$CU_CustomerID'")
		or die(mysql_Error());
	} 	
dbclose();
?>

The final step will be to save our php file and have a systems administrator add it to the crontab to run every 5 minutes or so depending on your preference. (It would probably good be a good practice to move this file above the web directory for security reasons.) Now when the Administrator of the site changes the category of a customer to “Reseller”, that customer will be emailed within 5 minutes of the update. I should also point out that if a customers category is changed back to “Anonymous” and then back again to “Reseller”, the email will be re-sent as our trigger will pick it up with the logic we are using.

There are probably a number of ways to achieve this same functionality. If you have any tweaks to share or if this was in any way a help to you, we’d love to hear about it in the comments below! Oh, and you can get rid of that gum now if you’d like!

Leave a Reply

Your email address will not be published. Required fields are marked *