Merge / Purge
5 posts by 2 authors in: Forums > CMS Builder
Last Post: July 5, 2010 (RSS)
By gkornbluth - July 1, 2010
Hi all,
Has anyone played around with a function that would automatically mark a record in a table as "hidden" if the e-mail field in that table matches the e-mail field in the accounts table?
Here's why...
I'm thinking of setting up a "newsletter signup" form for prospective members of an organization.
I'd be using a 3rd party package like Easylist to create the opt in e-mail and to keep records of those e-mail addresses awaiting confirmation.
When the link in the opt in e-mail is clicked, the prospect would be taken to a form that uses the membership plugin to automatically create a record in a "Newsletter Signup" table. The form would have fields for the prospect's first name, last name, e-mail, a few other fields for preferences.
If a person who is receiving the non-member newsletter becomes a member, I'd like to be able to automatically purge the newsletter list so that these folks never receive another non-member newsletter, and so that up-to-date lists of interested non-members and successful conversions can be created.
Best,
Jerry Kornbluth
Has anyone played around with a function that would automatically mark a record in a table as "hidden" if the e-mail field in that table matches the e-mail field in the accounts table?
Here's why...
I'm thinking of setting up a "newsletter signup" form for prospective members of an organization.
I'd be using a 3rd party package like Easylist to create the opt in e-mail and to keep records of those e-mail addresses awaiting confirmation.
When the link in the opt in e-mail is clicked, the prospect would be taken to a form that uses the membership plugin to automatically create a record in a "Newsletter Signup" table. The form would have fields for the prospect's first name, last name, e-mail, a few other fields for preferences.
If a person who is receiving the non-member newsletter becomes a member, I'd like to be able to automatically purge the newsletter list so that these folks never receive another non-member newsletter, and so that up-to-date lists of interested non-members and successful conversions can be created.
Best,
Jerry Kornbluth
The first CMS Builder reference book is now available on-line!
Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php
Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php
Re: [gkornbluth] Merge / Purge
By Jason - July 2, 2010
Hi Jerry,
It wouldn't be too difficult to create a function like this. What you would do is for each record , check to see if that email is in the accounts table. For this you could use the mysql_select_count_from() function. If this returns a number other than 0 it means that email is in the accounts table. You can then set the that record to hidden.
Here is some untested pseudo code:
Obviously this would have to be changed to match what's in your database.
Hope this points you in the right direction. Let me know if you run into any issues.
It wouldn't be too difficult to create a function like this. What you would do is for each record , check to see if that email is in the accounts table. For this you could use the mysql_select_count_from() function. If this returns a number other than 0 it means that email is in the accounts table. You can then set the that record to hidden.
Here is some untested pseudo code:
foreach($newsLetterRecords as $record){
$where = "email = '".mysql_escape($record['email']);
if(mysql_select_count_from('accounts',$where){
$query = "UPDATE `{$TABLE_PREFIX}newsLetter` SET hidden = 1 WHERE num =".intval($record['num'])
mysql_query($query) or die("MySQL Error:".mysql_error()."<br />\n");
}Obviously this would have to be changed to match what's in your database.
Hope this points you in the right direction. Let me know if you run into any issues.
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/
Jason Sauchuk - Project Manager
interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/
Re: [Jason] Merge / Purge
By gkornbluth - July 3, 2010
Jason,
You're the best!
I'll give it a try and let you know how it turns out.
Thanks,
Jerry
You're the best!
I'll give it a try and let you know how it turns out.
Thanks,
Jerry
The first CMS Builder reference book is now available on-line!
Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php
Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php
Re: [Jason] Merge / Purge
By gkornbluth - July 3, 2010
Hi Jason,
I’m not sure that I understood you correctly.
I set up a table called newsletter with a field called email and a checkbox field called hidden, and another table called testaccounts that also has a field called email.
When I load the viewer below, for each record with matching email fields in both tables, the hidden field in the newsletter table should be changed to yes.
Dreamweaver shows PHP errors and the page doesn’t seem to work (not blank) but I don’t know why.
Thanks for your help,
Jerry
At the top of a list viewer page the code is:
And the code in the body is:
I’m not sure that I understood you correctly.
I set up a table called newsletter with a field called email and a checkbox field called hidden, and another table called testaccounts that also has a field called email.
When I load the viewer below, for each record with matching email fields in both tables, the hidden field in the newsletter table should be changed to yes.
Dreamweaver shows PHP errors and the page doesn’t seem to work (not blank) but I don’t know why.
Thanks for your help,
Jerry
At the top of a list viewer page the code is:
<?php header('Content-type: text/html; charset=utf-8'); ?>
<?php
require_once "/hsphere/local/home/terreryb/terrerybovich.com/cmsAdmin/lib/viewer_functions.php";
list($newsletterRecords, $newsletterMetaData) = getRecords(array(
'tableName' => 'newsletter',
));
?>And the code in the body is:
<?php foreach($newsletterRecords as $record){
$where = "email = '".mysql_escape($record['email']);
if(mysql_select_count_from('testaccounts',$where){
$query = "UPDATE `{$TABLE_PREFIX}newsletter` SET hidden = 1 WHERE num =".intval($record['num'])
mysql_query($query) or die("MySQL Error:".mysql_error()."<br />\n");
} ?>
<?php endforeach ?>
The first CMS Builder reference book is now available on-line!
Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php
Take advantage of a free 3 month trial subscription, only for CMSB users, at: http://www.thecmsbcookbook.com/trial.php
Re: [gkornbluth] Merge / Purge
By Jason - July 5, 2010
Hi Jerry,
I can see one error. There is a missing single quote at the end of your where clause. Try changing the $where variable to this:
Give this a try. The logic seems fine. If you run into any problems, let me know what types of errors you're getting.
Hope this helps.
I can see one error. There is a missing single quote at the end of your where clause. Try changing the $where variable to this:
$where = "email = '".mysql_escape($record['email'])."'";Give this a try. The logic seems fine. If you run into any problems, let me know what types of errors you're getting.
Hope this helps.
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/
Jason Sauchuk - Project Manager
interactivetools.com
Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/