Author Topic: osCommerce Integration  (Read 16354 times)

DW

  • Administrator
  • Posts: 3787
    • View Profile
    • http://listmailpro.com
osCommerce Integration
« on: September 28, 2006, 01:28:09 am »
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.

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.

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.

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: [Select]
  $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);


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:
Dean Wiebe
ListMailPRO Author & Developer - Help | Support | Hosting

efry

  • Posts: 6
    • View Profile
Preliminary Questions Discussed
« Reply #1 on: September 28, 2006, 07:02:27 pm »
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

  • Administrator
  • Posts: 3787
    • View Profile
    • http://listmailpro.com
osCommerce Integration
« Reply #2 on: September 29, 2006, 12:39:06 am »
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: [Select]
<?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());
?>

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.

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

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.

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

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
Dean Wiebe
ListMailPRO Author & Developer - Help | Support | Hosting

DW

  • Administrator
  • Posts: 3787
    • View Profile
    • http://listmailpro.com
osCommerce Integration
« Reply #3 on: September 29, 2006, 12:28:58 pm »
I've completed a large part of the plugin. 8) Here's the code, which I'll call listmail-plugin.php:

UPDATED
Code: [Select]
<?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(
$lmplinkmysql_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');

?>

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: [Select]
include('./listmail-plugin.php');
Let me know how you fare!

Regards
Dean Wiebe
ListMailPRO Author & Developer - Help | Support | Hosting

efry

  • Posts: 6
    • View Profile
osCommerce Integration
« Reply #4 on: October 09, 2006, 02:22:19 pm »
Quote
Very thorough, Eli - it is a joy to work with someone as knowledgeable as yourself.  Nicely done!


 :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.

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.


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


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.


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.

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.



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

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 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

efry

  • Posts: 6
    • View Profile
osCommerce Integration
« Reply #5 on: October 09, 2006, 03:05:52 pm »
Hi Dean,

The code looks good so far.  Good going!

I've got a problem however...

Code: [Select]
<?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));



Within this first part of the code, I am having problems with it connecting to the database.  It doesn't seem to get a successful connection and keeps dying.

I have changed the $lmpath variable in my version to an absolute path of :


Code: [Select]
$lmpath = 'http://www.mysecondserver.com/mail';

But I still can't get it to connect.   :?

(As a quick reminder to those of you reviewing this thread -- my osCommerce shopping cart database is setup on 1 server account, and my ListMail Pro database is setup on a 2nd server.  I am trying to integrate the 2 separately hosted databases to work with each other. )

It looks as though the connection string above would work if my two databases were hosted on the same account, but since they're not -- it seems to be having connection problems.

Any ideas on how to remedy this Dean?

Is it a case of the wrong permission settings for the ListMail site "mail" subfolder?

Eli

DW

  • Administrator
  • Posts: 3787
    • View Profile
    • http://listmailpro.com
osCommerce Integration
« Reply #6 on: October 12, 2006, 09:30:17 am »
Right, 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.

In light of my oversight we're going to need to manually set the values for the ListMail database connection instead of trying to include them via config.php.
Code: [Select]
// relative path to ListMail, no trailing /
$lmpath = '../mail';
include($lmpath.'/config.php');

Will become:
Code: [Select]
// ListMail DB Connection
$sqlhost = 'example.com';
$sqluser = 'username';
$sqlpass = 'password';
$sqldb = 'database';
$lmpurl = 'http://example.com/mail/'; // note: trailing slash

Plus removal of a couple more lines.. I've updated the code in my original post.

Thankfully I didn't try to use any ListMail functions in the code otherwise this would have been a more involved fix. :)

Next we're probably going to have to talk to your ListMail server administrator about allowing remote access from your osCommerce webserver IP to your ListMail database.  Without a firewall this is as easy as placing a % character in the user and database 'host' fields then issuing a "FLUSH PRIVILEGES" query as the administrator.

Let me know how you fare with the changes.

Regards
Dean Wiebe
ListMailPRO Author & Developer - Help | Support | Hosting

efry

  • Posts: 6
    • View Profile
osCommerce Integration
« Reply #7 on: October 16, 2006, 06:05:23 pm »
Quote
Right, 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.


Right...  Asking the user to do an extra step to update seem non-professional.  We should be able to do it all from their single action.


In light of my oversight we're going to need to manually set the values for the ListMail database connection instead of trying to include them via config.php.
Code: [Select]
// relative path to ListMail, no trailing /
$lmpath = '../mail';
include($lmpath.'/config.php');

Will become:
Code: [Select]
// ListMail DB Connection
$sqlhost = 'example.com';
$sqluser = 'username';
$sqlpass = 'password';
$sqldb = 'database';
$lmpurl = 'http://example.com/mail/'; // note: trailing slash



For some reason Dean, the database connection is still not allowing me access.  I'm not sure if it's got something to do with my access permissions on my Listmail Pro hosting account with you, or it it's something else.

When I try to connect to the database, I get denied -- EXCEPT when I use "localhost" as the @sqlhost value above.

When I use "localhost", it seems to connect just fine -- but when I change the $sqluser value to my domain ('example.com'), the system chokes.

Here is the error message I get:  

Warning: mysql_connect(): Access denied for user: 'asapmail@s3.lmhost3.com' (Using password: YES) in /home/httpd/vhosts/asap?????.com/httpdocs/z.php on line 20

Warning: mysql_error(): supplied argument is not a valid MySQL-Link resource in /home/httpd/vhosts/asap?????.com/httpdocs/z.php on line 20



(????? = my domain name)

z.php is the name of the testfile I am running, which is equivalent to your updated code with the first "testing" lmp_add function call uncommented.  I have also added the line   "echo ('success');"  immediately after the database connection, so I can see when the connection is present, and when it fails.

Any ideas why I cannot connect?

For this initial connection purpose, I have regressed a little and have placed the test code (z.php in this case) on the SAME server as the  Listmail pro database in order to address the connection string.  Once we can get the connection working, then I'll move it to the different server and try the remote connection again.

I keep thinking that the problem lies in the permissions of the Listmail Pro database.  The reason I think this, is because when I log into the phpMyAdmin area of the Listmail Pro database and click on the "Home" icon, the result under "Create new database:" says in bold red letters "No Privileges".  

That doesn't seem right.

(FYI: I logged in through the Plesk interface.)

Since you're hosting my account right now for this database, can you take a look at that and see if that's what it should be showing?

Thanks Dean,

Eli

DW

  • Administrator
  • Posts: 3787
    • View Profile
    • http://listmailpro.com
osCommerce Integration
« Reply #8 on: October 17, 2006, 11:16:30 am »
My 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
Dean Wiebe
ListMailPRO Author & Developer - Help | Support | Hosting

efry

  • Posts: 6
    • View Profile
osCommerce Integration
« Reply #9 on: October 23, 2006, 03:32:41 pm »
Quote from: "DW"
My 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



Thanks Dean....  The connection works now.


I was still having a little bit of trouble with the scripting.  The "edit" and "remove" function were working fine, but the addition function with the fopen was giving me some problems.

After poking around a bit on the newsgroups, I found a thread that said to make sure "allow fopen" was turned on in the php.ini file.

I checked mine, and it was turned off.  I switched it to "on", restarted the system and that fixed it.  If any of you lurkers are having problems with the call doing the actual update -- try looking in your host account php.ini file.  Make sure fopen is allowed and turned on.

I'll be working on finalizing this osCommerce integration setup, including instructions.  It should be posted in the next day or so.

Eli

efry

  • Posts: 6
    • View Profile
Need a graceful failure solution
« Reply #10 on: October 24, 2006, 01:20:44 pm »
Hey Dean,

I've got things nearly completed, but am seeing the need for some code to create a graceful exit if a problem occurs.

Currently, I have my osCommerce pages setup to include the ListMail Pro functions you posted as an 'include' file.

Now, since my needs require having ListMail pro setup on a separate URL -- it occured to me, that if there is a timeout, or failure creating the connection to the separate URL -- my osCommerce pages will hang and fail.

I have tested this by changing the "sqlhost" variable in the config area of "listmail-plugin.php" (the code you provided earlier).  

By changing the host to an unresolved URL (to mimic a non-responding host), I find that my osCommerce pages do in fact choke.

If the ListMail pro host does ever timeout or is temporarily non-responsive, I would rather save the sale from my osCommerce pages -- and lose the mailing list addition.

So, can you offer some code (maybe a quick "if - then" statement) to allow a graceful exit if the connection string doesn't respond?

Thanks Dean.


Eli Fry

DW

  • Administrator
  • Posts: 3787
    • View Profile
    • http://listmailpro.com
osCommerce Integration
« Reply #11 on: October 31, 2006, 07:02:21 am »
Hi Eli, thanks for your patience. :)

I've updated the code.  I removed the "or die(mysql_error());" after the mysql_connect() call and made it skip all other parts of the script if the connection cannot be made.

I don't see anything on php.net about a timeout setting, so I'm not sure how long your page will have to wait in case of failure - you could test that.

Please let me know if I can be of further assistance.

Regards
Dean Wiebe
ListMailPRO Author & Developer - Help | Support | Hosting