In the recipe above, an organization has a newsletter signup form on their site that automatically populates a CMSB
multi record database called “Newsletter Signup”. (The Newsletter Signup database has only 4 fields: Hidden, First
Name, Last Name, and Email)
When a subscriber joins the organization they use the membership module to automatically create a record in the
“accounts” database. After the new member has been approved (by checking an “approved” checkbox in the accounts
database) , they want to be able to remove their e-mail address from the Newsletter Signup e-mail list. They also want
to be able to create reports that show who joined after getting the newsletter, the date they subscribed, and the date
they joined.
Jason Sauchuk from Interactive tools was up to the challenge.
He came up with the original code to compare the e-mail addresses in both the” Accounts” and the “Newsletter
Signup” database, and when there was a match, to hide the record in the Newsletter Signup database.
Here’s that code:
At the top of the page:
<?php header('Content-type: text/html; charset=utf-8'); ?>
<?php
require_once "/path_to_your/cmsAdmin/lib/viewer_functions.php";
list($newsletter_signupRecords, $newsletter_signupMetaData) = getRecords(array(
'tableName' => 'newsletter_signup',
));
?>
And in the body:
<?php foreach($newsletter_signupRecords as $record){
$where = "email = '".mysql_escape($record['email'])."' AND approved=1";
if(mysql_select_count_from('accounts',$where)){
$query = "UPDATE `{$TABLE_PREFIX}newsletter_signup` SET hidden = 1 WHERE num =".intval($record['num']) ;
mysql_query($query) or die("MySQL Error:".mysql_error()."<br />\n");
}
}
?>
When I added reporting code into the body, my first approach was to add this where statement to the top of the page:
list($newsletter_signupRecords, $newsletter_signupMetaData) = getRecords(array(
'tableName' => 'newsletter_signup',
'where' => " hidden = '0' ",
'orWhere' => " hidden = '1' ",
));
And this code below the existing body code:
MEMBERS WHO HAVE NOT YET JOINED <br />
<?php foreach ($newsletter_signupRecords as $record): ?><?PHP if ($record['hidden'] == 0): ?>
<?php echo $record['last_name'] ?>, <?php echo $record['first_name'] ?>
<br />
<?php echo $record['email'] ?>
<br />
Date first signed up: <?php echo date("D jS, M Y ", strtotime($record['createdDate'])) ?>
<hr> <?php endif ?>
<?php endforeach ?>
<br /><br />MEMBERS WHO JOINED AFTER GETTING THE NEWSLETTER<br />
<?php foreach ($newsletter_signupRecords as $record): ?><?PHP if ($record['hidden'] == 1): ?>
<?php echo $record['last_name'] ?>, <?php echo $record['first_name'] ?>
<br />
<?php echo $record['email'] ?>
<br />
Date first signed up: <?php echo date("D jS, M Y ", strtotime($record['createdDate'])) ?>
<br />
Date joined: <?php echo date("D jS, M Y ", strtotime($record['updatedDate'])) ?>
<hr> <?php endif ?>
<?php endforeach ?>
The reporting worked perfectly. The problem was that until the page was opened, and then refreshed, it did not reflect
the current changes to the information.
Again, Jason came to the rescue. He suggested that the easiest way to insure that the information was correct, was to
compile the report into 2 different variables while you're processing your records. Then all you have to do is output
the results at the end. This way you won't have to do a second database call to update the report.
Here’s the new code that he suggested for the body:
<?php $notConverted="";
$converted="";
?>
<?php foreach($newsletter_signupRecords as $record){
$where = "email = '".mysql_escape($record['email'])."' AND approved=1";
if(mysql_select_count_from('accounts',$where)){
$converted.=$record['last_name'].",".$record['first_name']."<br />".$record['email']."<br />";
$query = "UPDATE `{$TABLE_PREFIX}newsletter_signup` SET hidden = 1 WHERE num =".intval($record['num']) ;
mysql_query($query) or die("MySQL Error:".mysql_error()."<br />\n");
}
else{
$notConverted.=$record['last_name'].",".$record['first_name']."<br />".$record['email']."<br />";
}
}
?>
Not Converted to Membership <br />
<?php echo $notConverted; ?>
Converted to Membership <br />
<?php echo $converted; ?>
|
