ListMailPRO Email Marketing Software Forums > Customization, Integration

osCommerce Integration

(1/3) > >>

DW:
A client intends to integrate ListMail with osCommerce!  We've agreed to take it to the forum instead of hiding it from everyone via email (thanks, Eli!)

--- Quote ---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.
--- End quote ---

Yes, this seems straightforward.  Upon successful purchase the user would be inserted into ListMail with, if I understand #3 correctly, a custom variable to determine whether the user would like to be included in followup email.

--- Quote ---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.
--- End quote ---

It sounds like you have the right idea!  Be careful, though. I don't recommend necessarily removing features, but adding instead. :)

--- Quote ---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.
--- End quote ---

I haven't used osCommerce myself... Am I correct that the user will be updating their 'profile' from within osCommerce and that changes made there should automatically update ListMail?

We need a way to link users in ListMail to users in osCommerce.

The user could be identified based on email address and ListMail-list  / osCommerce-product #.

I don't recommend adding an additional -field- to the osCommerce user database, where we might store the ListMail user ID#, as that might break things... but we -could- create a whole new separate table to store osCommerce ID vs. ListMail ID.

To start with addition, let's look at the "simpler PHP insert script".  While it hasn't been done yet a similarly simple script could be setup for removes.


--- Code: ---   $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);
--- End code ---


The above code, run after purchase, would insert a user to list 1 if they are not already on it.  We would want to insert some additional code above it to determine the list # based on product.  We would also want to take into account the custom option you mentioned, where you allow users to choose to 'subscribe' or not.  On that subject, couldn't we just ignore users who choose not to subscribe as long as it doesn't create an error when updating or cancelling?  Or, did you want the 'welcome' message from ListMail sent upon -any- purchase, regardless of the 'subscription' option?

Let's hope this integration goes smoothly and helps a large # of ListMail clients!  :lol:

efry:
Hi Dean,

First a couple of responses to your original post, and then I'll add some things at the end.


-----comment----
Yes, this seems straightforward. Upon successful purchase the user would be inserted into ListMail with, if I understand #3 correctly, a custom variable to determine whether the user would like to be included in followup email.
-------

Yes, but not entirely.  It wouldn't have to be a custom variable, because the ListMail Pro database already has a "status" field.   The status of any list member can be 1 of 4 values.  Active, unconfirmed, removed, and bounced.   If the person chose to subscribe, we could set the status to "1" (active).  If they chose not to subscribe, we could set the status to "2" (removed).

The original thought here being that both databases (osCommerce & ListMail Pro) would contain up to date customer info, even if a customer didn't chose to subscribe.  If a customer who didn't sign up logs into their account area (through the osCommerce system), they would see their current subscription shown as "unsubscribed" (removed).

There is also another way to do it, but it's a little tricky.  I'll go into more details on that method later below.



----------comment--------
It sounds like you have the right idea! Be careful, though. I don't recommend necessarily removing features, but adding instead.
------------


You're right.  It's better stated as adding/modifying rather than removing.


----------comment---------
I haven't used osCommerce myself... Am I correct that the user will be updating their 'profile' from within osCommerce and that changes made there should automatically update ListMail?
--------------

Yes.  That's how I see it happening.  Finding the area where osCommerce updates it's own database, and then adding on our custom code to access ListMail right after.


------comment---------
We need a way to link users in ListMail to users in osCommerce.

The user could be identified based on email address and ListMail-list / osCommerce-product #.
-------------

It may be better to use the customer id# that osCommerce assigns each customer paired up with their email address.  

A single customer can place many orders containing many different products, so associating it with a product wouldn't work.

I suppose there is a theoretical issue if a site owner had mutliple websites, each with an osCommerce platform, and using the same Listmail Pro database for both (all).  It would be possible that a customer who subscribed to both places could potentially be assigned the same customer ID# in both osCommerce databases.  (making the custID# and email address values the same -- causing an incorrect update in ListMail).

Does that make sense?  The chances are extremely slim, so I don't think it's a practical issue to worry about.


---------comment---------
I don't recommend adding an additional -field- to the osCommerce user database, where we might store the ListMail user ID#, as that might break things... but we -could- create a whole new separate table to store osCommerce ID vs. ListMail ID.
------------


I'm not sure it's needed if we keep the osCommerce platform as the dominant platform.  

I think as long as we update the ListMail database everytime the osCommerce database is updated (in relation to customer first name, last name and email address) -- the data should be the same.

The only question is how we handle the remove requests that come in from the outgoing emails themselves.

If the remove link uses the osCommerce system to change the subscription status, then as long as we have the piggyback code inserted to modify the ListMail database -- everything should be fine.

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.

I suppose we'll have to work through that one when we come to it.


--------comment-----------
The above code, run after purchase, would insert a user to list 1 if they are not already on it. We would want to insert some additional code above it to determine the list # based on product.
--------------


I suppose this could be useful, but for my project, that goes beyond the scope of my needs.  Personally, 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.


-------comment--------
We would also want to take into account the custom option you mentioned, where you allow users to choose to 'subscribe' or not. On that subject, couldn't we just ignore users who choose not to subscribe as long as it doesn't create an error when updating or cancelling? Or, did you want the 'welcome' message from ListMail sent upon -any- purchase, regardless of the 'subscription' option?
----------------

Again, I think by adding a user who does not want to subscribe, it keeps the osCommerce database consistent.  This is because when a customer fills out their info, if they chose not to subscribe, the osCommerce will still take all of the customer info because a sale is being made -- but it will simply classify the user as unsubscribed.

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.

Entering every customer and appropriate status will prevent problems down the road, because even if the customer isn't receiving the email -- there will still be a record that the osCommerce pages can work with.

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:

1.  First, call the ListMail pro signup page to add the person to a list.  (#1 in this case)

http://www.yourlistmailhost.com/mail/signup.php?list=3&email=test@yahoo.com&fname=Bob&lname=Smith&seq=1&del=0

2.  Next, call the ListMail pro removal page to change their status to removed.

http://www.asaplistmanager.com/mail/rem.php?x=5&l=1&e=test@yahoo.com


This will do the trick, but in order to make it clean -- you must change 2 settings.  #1 is you must turn off the "Welcome" email.  #2 is to make the removal link work with 1 click.

Setting #1:  Select the list you're working with.  (#1 in this example)  Click on "List Settings". Uncheck the "Send welcome email" option and click Save.

Setting #2:  Select "Message Codes".  Click on "Global codes".  Under the "remove link" function code, click on "Edit".  Check the box that says "One-click Remove".

That will do it.  

With this method, the "welcome email" will be disabled, so it may not work for everyone.

For me, I have the "Welcome" email setup to trigger in my osCommerce pages, so it's a viable solution.  Since this thread is about integrating ListMail with osCommerce, I imagine other people can set it up that way as well.


That's enough typing for me for now -- my fingers are about to fall off.  

Eli

DW:
Very thorough, Eli - it is a joy to work with someone as knowledgeable as yourself.  Nicely done!

OK. For any operation the first thing we'll need to do is connect to the ListMail database.  I'm going to assume ListMail data is stored in a separate database.

We could create a file named plugin-listmail.php and place it in the osCommerce folder.

--- Code: ---<?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());
?>
--- End code ---

I'm thinking we could just go ahead and create functions in this file so one can do what one wants with minimal osCommerce additions.  ie adduser($email,$list,$first,$last,$custom1,$custom2,$etc,$seq,$del,$status), remuser($id), updateuser($id,$email,$first,$last,$status).

You mentioned only needing first name, last name, email and status.  We could design it that way at first and modify it to accomodate more data (custom, seq, del, etc) later for anyone that needs it.

What you say about osCommerce keeping a record of single clients purchasing multiple products sounds like it will simplify things.  The osCommerce ID could be stored in a ListMail custom field which could then be used to identify and update the user's ListMail data. :!:

--- Quote ---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.
--- End quote ---

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: ---<?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);
}
?>
--- End code ---

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.

--- Quote ---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.
--- End quote ---

OK. List 1's additional options could provide for removing users automatically from list 2.

--- Quote ---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.
--- End quote ---

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.

--- Quote ---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
--- End quote ---

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.

I think I have everything needed to make this happen.  After the code is done, tomorrow mid-day (PST) most likely, you can try plugging it into osCommerce!

Regards

DW:
I've completed a large part of the plugin. 8) Here's the code, which I'll call listmail-plugin.php:

UPDATED

--- Code: ---<?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.php
function 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 oscid
function 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 oscid
function 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');

?>
--- End code ---

You can follow the examples under "TESTING" to use the functions, with the first element being the 'osCommerce ID'.

This script should be placed in your osCommerce folder.  The following line should come before using the functions in your modifications:

--- Code: ---include('./listmail-plugin.php');
--- End code ---

Let me know how you fare!

Regards

efry:

--- Quote ---Very thorough, Eli - it is a joy to work with someone as knowledgeable as yourself.  Nicely done!
--- End quote ---


 :wink:

Glad to help...  I appreciate your help through this as well Dean...




--- Quote ---
--- Quote ---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.
--- End quote ---

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.
--- End quote ---


Yes, I agree.  


--- Quote ---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: ---<?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);
}
?>
--- End code ---

--- End quote ---


Yes, that sounds good.  This would be a great place to put the code to update the customer data in the osCommerce database to keep things consistent between both databases.



--- Quote ---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.
--- End quote ---


Good thinking -- I hadn't thought of that.



--- Quote ---
--- Quote ---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.
--- End quote ---

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.
--- End quote ---



Yes, that was what I had envisioned too.  "Toggling" their status subscribed or non-subscribed, rather than always adding or deleting their record.



--- Quote ---
--- Quote ---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
--- End quote ---

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.
--- End quote ---



I think the way I had outlined it (although it would probably work ok), isn't the best way to go about this -- as there were a few other shortcomings of that method that I didn't bring up.  

It would be better to just access the database directly to modify the information.  It's cleaner too.


Eli

Navigation

[0] Message Index

[#] Next page

Go to full version