J'ai une boutique en ligne fonctionnant sous Oscommerce et j'utilise phplist pour la gestion de mes newsletters.
Pour simplifier la gestion des abonnements, des désinscriptions, etc, j'ai trouvé un script permettant de coupler les deux applications mais il nécéssite à la base 2 bd sur un seul serveur.
Mes deux applications ont deux bases de données différents sur deux serveurs différents. L'hébergeur ne permet pas de créer une nouvelle base de données sur le serveur de mon choix (cela aurait été trop facile)
Je vous mets plus bas un fragment de code. Comment peut-on le modifier pour prendre en compte le fait qu'il y ait 2 serveurs?
Code : Tout sélectionner
//Begin PHPlist Newsletter add-on
require(DIR_WS_INCLUDES . '/phplist_define.php'); //get the phplist specifics
$existing_email_query = tep_db_query("select id, email, foreignkey from " . PHPLIST_DB . "" . PHPLIST_TABLE_PREFIX . "user_user where email = '" . $customers_email_address . "'", $connect); //check for existing by email address
$existing_user_query = tep_db_query("select id, foreignkey, email from " . PHPLIST_DB . "" . PHPLIST_TABLE_PREFIX . "user_user where foreignkey = " . $customers_id . "", $connect); //check for existing by phplist userid in case they subscribed using another method
$history_systeminfo_text = "\nHTTP_USER_AGENT = " . $_SERVER["HTTP_USER_AGENT"] ."\nHTTP_REFERER = " . $_SERVER["HTTP_REFERER"] . "\nREMOTE_ADDR = " . $_SERVER["REMOTE_ADDR"] . "";
$history_detail_text = "";
//subscribe logic
if ($customers_newsletter){
if (tep_db_num_rows($existing_user_query) > 0) { //existing user by id found?
$existing_user = tep_db_fetch_array($existing_user_query);
$duplicate=tep_db_fetch_array($existing_email_query);
if ($customers_email_address != $existing_user['email']){ //found id but had a different email address
if ($duplicate['email'] == $customers_email_address) { //does the new email address from the form exist in phplist already?
tep_db_query("update " . PHPLIST_DB . "" . PHPLIST_TABLE_PREFIX . "user_user set disabled = 0, confirmed = 1, bouncecount = 0, foreignkey = " . $customers_id . " where email = '" . $customers_email_address . "'", $connect); //change the foreignkey to match the new customer_id
$history_detail_text .= "foreign key = " . $customers_id . "\nchanged from " . $existing_user['foreignkey'] . "\n\n";
}
else { //no matching email address found
tep_db_query("update " . PHPLIST_DB . "" . PHPLIST_TABLE_PREFIX . "user_user set email = '" . $customers_email_address . "', disabled = 0, bouncecount = 0, confirmed = 1 where foreignkey = " . $customers_id . "", $connect); //no duplicates in phplist, so update the email address in phplist
$history_detail_text .= "email = " . $customers_email_address . "\nchanged from " . $existing_user['email'] . "\n\n";
}
}
else { //found id with matching email address
tep_db_query("update " . PHPLIST_DB . "" . PHPLIST_TABLE_PREFIX . "user_user set disabled = 0, bouncecount = 0, confirmed = 1 where foreignkey = " . $customers_id . "", $connect); //make sure the status is set to enabled and confirmed
$history_detail_text .= "No user details changed\n\n";
}
$existing_subscription_query = tep_db_query("select userid from " . PHPLIST_DB . "" . PHPLIST_TABLE_PREFIX . "listuser where listid = " . PHPLIST_LISTNO . " and userid = " . $existing_user['id'] . "");
if (tep_db_num_rows($existing_subscription_query) < 1) { //no existing subscription to the newsletter
//generating history, previous subscriptions
$history_detail_text .= "";
$existing_subscription_query = tep_db_query("select li.name from " . PHPLIST_DB . "" . PHPLIST_TABLE_PREFIX . "list li, " . PHPLIST_DB . "" . PHPLIST_TABLE_PREFIX . "listuser lu where li.id = lu.listid and lu.userid = " . $existing_user['id'] . "", $connect);
if (tep_db_num_rows($existing_subscription_query) < 1) {
$history_detail_text .= "Was subscribed to: \n";
} else {
while($existing_subscription = tep_db_fetch_array($existing_subscription_query)){
$history_detail_text .= "Was subscribed to: " . $existing_subscription[name] . "\n";
}
}
tep_db_query("insert into " . PHPLIST_DB . "" . PHPLIST_TABLE_PREFIX . "listuser (userid, listid, entered) values (" . $existing_user['id'] . ", " . PHPLIST_LISTNO . ", '" . date('Y-m-d H:i:s') . "')", $connect); // subscribe the new user to the correct list
//generating history, current (updated) subscriptions
$updated_subscription_query = tep_db_query("select li.name from " . PHPLIST_DB . "" . PHPLIST_TABLE_PREFIX . "list li, " . PHPLIST_DB . "" . PHPLIST_TABLE_PREFIX . "listuser lu where li.id = lu.listid and lu.userid = " . $existing_user['id'] . "", $connect);
while ($updated_subscription = tep_db_fetch_array($updated_subscription_query)) {
$history_detail_text .= "Is now subscribed to: " . $updated_subscription[name] . "\n";
}
}
tep_db_query("insert into " . PHPLIST_DB . "" . PHPLIST_TABLE_PREFIX . "user_user_history (userid, ip, date, summary, detail, systeminfo) values (" . $existing_user['id'] . ", '" . $_SERVER["REMOTE_ADDR"] . "', '" . date('Y-m-d H:i:s') . "', 'Update by osC-admin', '" . $history_detail_text . "', '" . $history_systeminfo_text . "')", $connect); //create history post
}
elseif (tep_db_num_rows($existing_email_query) < 1) { //no existing user by email address found (therefore a new user - no id or email found)
$id = md5(uniqid(mt_rand())); //generate unique id and add new user to database
tep_db_query("insert into " . PHPLIST_DB . "" . PHPLIST_TABLE_PREFIX . "user_user (email, confirmed, subscribepage, entered, disabled, uniqid, htmlemail, foreignkey) values ('" . $customers_email_address . "', 1, " . PHPLIST_SPAGE . ", now(), 0, '" . $id . "', " . PHPLIST_HTMLEMAIL . ", " . $customers_id . ")", $connect); //insert the new user into phplist
$user_query=tep_db_query("select id from " . PHPLIST_DB . "" . PHPLIST_TABLE_PREFIX . "user_user where email = '" . $customers_email_address . "'", $connect); //get the new user's phplist id
$user = tep_db_fetch_array($user_query);
tep_db_query("insert into " . PHPLIST_DB . "" . PHPLIST_TABLE_PREFIX . "user_user_attribute (attributeid, userid, value) values (" . PHPLIST_FIRSTNAME . ", " . $user['id'] . ", '" . $customers_firstname . "')", $connect); //add the users firstname to the attributes table
tep_db_query("insert into " . PHPLIST_DB . "" . PHPLIST_TABLE_PREFIX . "user_user_attribute (attributeid, userid, value) values (" . PHPLIST_LASTNAME . ", " . $user['id'] . ", '" . $customers_lastname . "')", $connect); //add the users lastname to the attributes table
tep_db_query("insert into " . PHPLIST_DB . "" . PHPLIST_TABLE_PREFIX . "listuser (userid, listid, entered) values (" . $user['id'] . ", " . PHPLIST_LISTNO . ", '" . date('Y-m-d H:i:s') . "')", $connect);// subscribe the new user to the correct list
//generating history
$history_detail_text .= "\nSubscribepage = " . PHPLIST_SPAGE . "\n";
$attribute_name_query = tep_db_query("select name from " . PHPLIST_DB . "" . PHPLIST_TABLE_PREFIX . "user_attribute where id = " . PHPLIST_FIRSTNAME . "", $connect);
$attribute_name = tep_db_fetch_array($attribute_name_query);
$history_detail_text .= "" . $attribute_name[name] . " = " . $customers_firstname . "\n";
$attribute_name_query = tep_db_query("select name from " . PHPLIST_DB . "" . PHPLIST_TABLE_PREFIX . "user_attribute where id = " . PHPLIST_LASTNAME . "", $connect);
$attribute_name = tep_db_fetch_array($attribute_name_query);
$history_detail_text .= "" . $attribute_name[name] . " = " . $customers_lastname . "\n";
$list_name_query = tep_db_query("select name from " . PHPLIST_DB . "" . PHPLIST_TABLE_PREFIX . "list where id = " . PHPLIST_LISTNO . "", $connect);
$list_name = tep_db_fetch_array($list_name_query);
$history_detail_text .= "\nList subscriptions:\n" . $list_name['name'] . "";
tep_db_query("insert into " . PHPLIST_DB . "" . PHPLIST_TABLE_PREFIX . "user_user_history (userid, ip, date, summary, detail, systeminfo) values (" . $user['id'] . ", '" . $_SERVER["REMOTE_ADDR"] . "', '" . date('Y-m-d H:i:s') . "', 'Update by osC-admin', '" . $history_detail_text . "', '" . $history_systeminfo_text . "')", $connect); //create history post
}
else { //subscribe the existing user if disabled and add foreignkey match to customer_id
$existing_email = tep_db_fetch_array($existing_email_query); //existing user by email found
tep_db_query("update " . PHPLIST_DB . "" . PHPLIST_TABLE_PREFIX . "user_user set disabled = 0, bouncecount = 0, confirmed = 1, foreignkey = " . $customers_id . " where id = " . $existing_email['id'] . "", $connect); //change the foreignkey to match the customer_id
$history_detail_text .= "foreign key = " . $customers_id . "\nchanged from ";
//check to see if they already are subscribed to the correct list
$list_query = tep_db_query("select * from " . PHPLIST_DB . "" . PHPLIST_TABLE_PREFIX . "listuser where userid = " . $existing_email['id'] . " and listid = " . PHPLIST_LISTNO . "", $connect);
if ($list=tep_db_num_rows($list_query) < 1) { //no existing subscription to the newsletter found
//generating history, previous subscriptions
$history_detail_text .= "\n\nList subscriptions:\n";
$existing_subscription_query = tep_db_query("select li.name from " . PHPLIST_DB . "" . PHPLIST_TABLE_PREFIX . "list li, " . PHPLIST_DB . "" . PHPLIST_TABLE_PREFIX . "listuser lu where li.id = lu.listid and lu.userid = " . $existing_email['id'] . "", $connect);
if (tep_db_num_rows($existing_subscription_query) < 1) {
$history_detail_text .= "Was subscribed to: \n";
} else {
while($existing_subscription = tep_db_fetch_array($existing_subscription_query)){
$history_detail_text .= "Was subscribed to: " . $existing_subscription[name] . "\n";
}
}
tep_db_query("insert into " . PHPLIST_DB . "" . PHPLIST_TABLE_PREFIX . "listuser (userid, listid, entered) values (" . $existing_email['id'] . ", " . PHPLIST_LISTNO . ", '" . date('Y-m-d H:i:s') . "')", $connect); //make sure they are subscribed to the correct list
//generating history, current (updated) subscriptions
$updated_subscription_query = tep_db_query("select li.name from " . PHPLIST_DB . "" . PHPLIST_TABLE_PREFIX . "list li, " . PHPLIST_DB . "" . PHPLIST_TABLE_PREFIX . "listuser lu where li.id = lu.listid and lu.userid = " . $existing_email['id'] . "", $connect);
while ($updated_subscription = tep_db_fetch_array($updated_subscription_query)) {
$history_detail_text .= "Is now subscribed to: " . $updated_subscription[name] . "\n";
}
}
tep_db_query("insert into " . PHPLIST_DB . "" . PHPLIST_TABLE_PREFIX . "user_user_history (userid, ip, date, summary, detail, systeminfo) values (" . $existing_email['id'] . ", '" . $_SERVER["REMOTE_ADDR"] . "', '" . date('Y-m-d H:i:s') . "', 'Update by osC-admin', '" . $history_detail_text . "', '" . $history_systeminfo_text . "')", $connect); //create history post
}
}Cordialement
Laurent
