If you can come up with the code to be placed in the shopping cart pages, I
think I can reference that to do all the other internal updating
pages. (customer account area, and Administration area).
The page where a customer put's in their address information, I think I
just want to make an additional call to the ListMail Pro software, which
will update the data there as well.
1. post customer name to ListMail database
2. post customer email to ListMail database
3. post subscription status test
if person chose 'yes' to subscribe -- post as 'yes'
if person chose 'no' to subscribe -- post as 'no'
Then, all list mailings and removal functions would be handled by the
ListMail pro software -- independent of my shopping cart information.
Seems simple enough.
If I reference everything having to do with list management to point to the
ListMail software, and any functions for editing, simply point over to the
ListMail account -- then the ListMail database should always contain
current subscription data. I'll just cut out every "newsletter" option
from my osCommerce software, and just point the link to the ListMail account.
So, if you can come up with the code that will connect to the ListMail
hosting database, and make the customer information update -- I think I
can figure out what's going on enough to make the changes everywhere else that needs it.
$url = "http://example.com/mail/signup.php?list=1&email=$email&fname=$first_name&lname=$last_name&seq=1&del=0";
$lmp = fopen($url,'r');
fclose($lmp); <?php// ListMailPRO Plugin// Connect to MySQL and initialize any functions// relative path to ListMail, no trailing /$listmailpath = '../mail';include($listmailpath.'/config.php');// connect to listmail db$lmplink = mysql_connect($sqlhost,$sqluser,$sqlpass) or die(mysql_error());mysql_select_db($sqldb,$lmplink) or die(mysql_error());?>
The only question is how we handle the remove requests that come in from the outgoing emails themselves. If the remove link in an email uses the ListMail pro system to change subscription status -- then there should be an additional function which would update the osCommerce database.
<?php// osCommerce-based lists$osclists = array('1','2','3');if(in_array($list,$osclists)){ // run custom osc remove script // 1. connect and select osc database $osclink = mysql_connect($oschost,$oscuser,$oscpass) or die(mysql_error()); mysql_select_db($oscdb,$osclink) or die(mysql_error()); // 2. update osc 'subscribed' field, matching the user with 'custom 1' field containing osc id mysql_query("update osc_users set subscribed = '0' where id = '$user1';",$osclink);}?>
I only need 2 lists.
1=customers -- people who have made a purchase
2=prospects -- people who have signed up for the newsletter, but NOT made a purchase.
If we ignore this person and do not enter it into the ListMail Pro database, it could potentially cause errors (or concerns) later down the road if the customer ever updates their info or changes to subscribe. If the osCommerce software is looking to edit the ListMail Pro database values, but doesn't find any -- it could choke.
Above, I had mentioned an alternative way for creating a new list member with a "removed" status without accessing the ListMail pro database directly. It's a little tricky, but it seems to work. Here's what to do for that part
<?php// ListMailPRO osCommerce Integration// Last Updated Oct 31 2006// ListMail DB Connection - Edit with your details remote or local$sqlhost = 'example.com';$sqluser = 'username';$sqlpass = 'password';$sqldb = 'database';$lmpurl = 'http://example.com/mail/'; // note: trailing slash// connect to listmail db$lmplink = mysql_connect($sqlhost,$sqluser,$sqlpass);if($lmplink) mysql_select_db($sqldb,$lmplink) or die(mysql_error($lmplink));// add a user with a post to signup.phpfunction lmp_add($oscid,$list,$email,$cnf,$fname,$lname){ global $lmpurl; global $lmplink; if(!$lmplink) return false; // cnf should be 1 (active) or 2 (removed) // note that with 'confirmation' enabled for this list the user will be required to confirm to reach cnf 1 if($cnf<>'1') $cnf = '2'; // seq and del are 1 and 0 respectively // oscid is stored in custom field #1 (user1) // list additional options, welcome/confirm settings/emails and notifications are enabled if(!is_numeric($oscid) || !is_numeric($list)) exit('list or oscid non-numeric'); // skip if oscid already in LMP list($cnt)=mysql_fetch_row(mysql_query("select count(*) from lm_users where user1 = '$oscid';",$lmplink)); if($cnt>0) exit(); $vars = array('list','email','cnf','fname','lname','user1'); foreach($vars as $var){ ${$var} = urlencode(${$var}); } $url = $lmpurl."signup.php?list=$list&email=$email&fname=$fname&lname=$lname&user1=$oscid&seq=1&del=0"; $lmp = fopen($url,'r'); fclose($lmp); // oops, can't set cnf during subscription.. update user to 'removed' if cnf = 2 if($cnf=='2'){ mysql_query("update lm_users set cnf = '2' where user1 = '$oscid';",$lmplink) or die(mysql_error($lmplink)); } // this protects against duplicates caused by the same osc id adding a new email address.. mysql_query("delete from lm_users where user1 = '$oscid' and email not like '".addslashes(urldecode($email))."';",$lmplink) or die(mysql_error($lmplink));}// update a user based on oscidfunction lmp_update($oscid,$email,$cnf,$fname,$lname){ global $lmplink; if(!$lmplink) return false; // cnf should be 1 (active) or 2 (removed) if($cnf<>'1') $cnf = '2'; if(!is_numeric($oscid)) exit('oscid non-numeric'); $vars = array('email','fname','lname'); foreach($vars as $var){ ${$var} = addslashes(${$var}); } mysql_query("update lm_users set cnf = '$cnf', email = '$email', fname = '$fname', lname = '$lname' where user1 = '$oscid';",$lmplink) or die(mysql_error($lmplink));}// remove a user based on oscidfunction lmp_remove($oscid){ global $lmplink; if(!$lmplink) return false; if(!is_numeric($oscid)) exit('oscid non-numeric'); // set user status to removed, currently does not support additional list ops or notifications mysql_query("update lm_users set cnf = '2' where user1 = '$oscid';",$lmplink);}// echo "TESTING<br>";// lmp_add(12345,'1','test@example.com','1','TestFN','TestLN');// lmp_remove(12345);// lmp_update(12345,'test2@example.com','1','TestFN2','TestLN2');?>
include('./listmail-plugin.php');Very thorough, Eli - it is a joy to work with someone as knowledgeable as yourself. Nicely done!
QuoteThe only question is how we handle the remove requests that come in from the outgoing emails themselves. If the remove link in an email uses the ListMail pro system to change subscription status -- then there should be an additional function which would update the osCommerce database.
For simplicity I'd prefer to use the standard ListMail "Remove Link" in messages if possible. This will give the added benefit of processing list 'additional options' (remove/add to other lists on removal), etc.
ListMail features convenient "xtra" files that can run before or after certain processes. See the ListMail ./xtra folder for some examples, including "remove-xtra.php", the one we will use. This file could include code such as follows:Code: [Select]<?php// osCommerce-based lists$osclists = array('1','2','3');if(in_array($list,$osclists)){ // run custom osc remove script // 1. connect and select osc database $osclink = mysql_connect($oschost,$oscuser,$oscpass) or die(mysql_error()); mysql_select_db($oscdb,$osclink) or die(mysql_error()); // 2. update osc 'subscribed' field, matching the user with 'custom 1' field containing osc id mysql_query("update osc_users set subscribed = '0' where id = '$user1';",$osclink);}?>
One thing we haven't thought about is users removed for bouncing - an additional 'xtra' script might be needed here, too. In fact, remove-xtra.php itself could/should be added to the bounce removal process.
QuoteIf we ignore this person and do not enter it into the ListMail Pro database, it could potentially cause errors (or concerns) later down the road if the customer ever updates their info or changes to subscribe. If the osCommerce software is looking to edit the ListMail Pro database values, but doesn't find any -- it could choke.
We could suppress any errors fairly easily but we can do it your way, too, as you prefer. It might in fact be better to 'toggle' the status instead of removing and adding the user every time they change their 'subscribed' setting in osC.
QuoteAbove, I had mentioned an alternative way for creating a new list member with a "removed" status without accessing the ListMail pro database directly. It's a little tricky, but it seems to work. Here's what to do for that part
We could do it that way or, while we're connected to the ListMail database, simply run an additional MySQL query to update the status based on the list and email address. The URL-based GET-style command will certainly simplify things with signup, however.
<?php// ListMailPRO osCommerce Integration// Connect to MySQL and initialize functions// relative path to ListMail, no trailing /$lmpath = '../mail';include($lmpath.'/config.php');// connect to listmail db$lmplink = mysql_connect($sqlhost,$sqluser,$sqlpass) or die(mysql_error($lmplink));mysql_select_db($sqldb,$lmplink) or die(mysql_error($lmplink));
$lmpath = 'http://www.mysecondserver.com/mail';// relative path to ListMail, no trailing /
$lmpath = '../mail';
include($lmpath.'/config.php');// ListMail DB Connection
$sqlhost = 'example.com';
$sqluser = 'username';
$sqlpass = 'password';
$sqldb = 'database';
$lmpurl = 'http://example.com/mail/'; // note: trailing slashRight, I had forgotten that you wanted to do this across hosts.
Note that we're going to need both administators assistance in order to set the correct MySQL access and, possibly, enter an addition to the firewall. On the osCommerce host we (obviously) need to be able to access the ListMail server and database remotely. On the ListMail host we need it for the 'xtra' script so osCommerce can be updated when a user uses the remove link. To avoid this I suppose you could simply instruct your users, in each email, to update their 'subscribed' status in osCommerce to remove themselves. That way it would be a one-way connection, but then it would also be less expandable for when you want to add users to multiple lists, etc.
// relative path to ListMail, no trailing /
$lmpath = '../mail';
include($lmpath.'/config.php');// ListMail DB Connection
$sqlhost = 'example.com';
$sqluser = 'username';
$sqlpass = 'password';
$sqldb = 'database';
$lmpurl = 'http://example.com/mail/'; // note: trailing slashMy apologies - I had not set up the remote access for you (I forgot you were hosted with me). I have set up and tested it now.
Regards