how to integrate with phplist?

**remp** · 12 Jan 2008, 07:26 AM

Well hello, like the title said; how can i integrate phplist into this cart?

**DrByte** · 12 Jan 2008, 07:50 AM

You could try this:
http://www.zen-cart.com/forum/showthread.php?t=73816
or
http://www.zen-cart.com/forum/showthread.php?t=46427

**hqarrse2** · 17 Jul 2008, 07:26 AM

Warning: This is still in final testing! If you want to help with that and further improvement I would be really grateful.

########################################

I needed a quick solution to this so did a 'quick'n'dirty' KISS script to syncronize phplist with zencart.

The basic version is done and any help testing or opinions on whether further development is worth it would be appreciated.

So firstly, what does it do:

- Exports the ZC customers who are subscribed to your newsletters to a mailing list in a standalone version of PHPList.

- Updates this whenever it's run so that changes in ZC are also made in PHPList (subscribe / unsubscribe / html / text / email address).

- Will unsubscribe a customer in ZC when the user is deactivated by PHPList or removed from the mailing list.

Limitations:

- It is a simple script that must be run and show no errors before sending out your newsletter. It only takes a couple of seconds and could be done with a cronjob.

- It is 'all or nothing' - it only works with one list in PHPList at the moment.

- I haven't used attributes yet as I don't need them - names etc. So "Hi John, here's your newsletter" isn't possible, although would be very easy to add.

Benefits:

- No modification to ZC at all. Very very minor modification to PHPList which is only to give a neat link and some protection to your sync script.

- All subscribe and unsubscribe is still done through ZC. This could also be changed fairly easily.

- Is it worth it? Well here's the tricky one. I don't know yet. I'm still getting to know PHPList, but don't know if I've lost too many of its features to be worthwhile. Feedback appreciated.

####################_

Now to business:

1. Install PHPList and note your table prefix and DB name.
2. Create a list in admin->lists->add a list. Note the id number which on a fresh install will be 2. (eg 2. My List).
3. Get the prefix of your ZC tables and the DB name.
4. Create a mysql user with these privileges (assuming prefix lists, zen):

select, insert, update, delete on lists_user_user and lists_listuser

select, update on zen_customers

For production use I would make this column specific - look through the script and work it out as I haven't done that yet!

5. Create the user who will admin the list, and remove his privileges to access the members pages or edit lists. He is allowed to delete members from the list (done via the lists pages) but not more.

6. Edit your setup to have UNSUBSCRIBE and SUBSCRIBE links to the zen cart unsubcribe and account.

In the code I have commented out the lines that update the ZC customers table. Once you're sure all is working then you can uncomment these but be careful. This is test only at the moment!

####################

To make it a bit more useful I added a VERY simple security measure, and a link to the lists admin panel. To use this uncomment lines 8 - 12 and 151, fill in the random number in line 8, and check the return URL in line 31.

Now edit lists/admin/home.php and add:

Code:

if (checkAccess("admin")) {
  $some = 1;
  $ls->addElement("Sync Mailing List","/zcsync.php?sycall=MY_RANDOM_NUMBER");
  $ls->addColumn("Sync Mailing List","&nbsp","Before all mailshots!  Run and confirm no errors.");
}

after line 73.

##############################____

Code:

<?php
//simple security checkdate

/*if ($_GET['sycall'] != "A_RANDOM_NUMBER")
{
	echo ('<h3>No access allowed.  Call this only from with the mailing list admin.</h3>');	
}
else {
*/
$dbhost = 'localhost';
$dbuser = '';
$dbpass = '';
$zc_dbname = '';
$zc_prefix = '';
$lists_dbname = '';
$lists_prefix = '';

$list_id = '2'; //the id of the mailing list you want to sync with ZC

//get the dull html stuff done.

echo ('<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">');
echo ('<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">');
echo('<head><meta http-equiv="Content-Type" content="text/html;charset=utf-8" />');
echo('<style type="text/css" media="all">body {font-family: verdana, arial; font-size: 11px;}.red {color: red;} .green {color: green;} .blue {color: cyan;} </style><title>ZC / PHPLists Sync</title></head>');
echo('<body><div style="width: 750px; border: 1px solid #BCBCBC"><h3>ZC / PHPList Syncronization Script</h3><p>In case of errors, stop, save the page or print it, and phone Joe Blogs.</p><p>Use before every mail shot.</p><hr/><p>');
echo('<p>All OK? <a href="/lists/admin/?page=home">RETURN TO ADMIN and crack on!</a></p><hr/>');

//connect to the DB

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('<span class="red"><strong>Error</strong> connecting to mysql</span><br/>');

//mysql_select_db($zc_dbname);

/* initial insert */

$start_id = 0;
$changes_add = 0;
$changes_delete = 0;
$changes_edit = 0;

$zc_query = "select customers_id, customers_email_address, customers_newsletter, customers_email_format from ". $zc_dbname . "." . $zc_prefix ."customers where customers_id > ".$start_id." order by customers_id desc";
$zc_result = mysql_query($zc_query) or die('<span class="red">Error, ZC select query failed.</span> <br/>');
$count = 0;

while($row = mysql_fetch_array($zc_result, MYSQL_ASSOC))
{
	$count ++;
	$mail = $row['customers_email_address'];
	$customers_id = $row['customers_id'];
	$subscribed = $row['customers_newsletter'];
	$html = $row['customers_email_format'];
	$htmlemail = $html == "HTML" ? 1 : 0;

	/* does customer already exist?
	*
	* scenario 1.  No change. continue
	* scenario 2.  Email change or email format change - update
	* scenario 3.  Has unsubscribed (in zenc) - delete
	* scenario 4.  Lists has decided he is invalid and disabled him - action:
	*              - Update zen cart to show unsubscribed.
	*              - Delete from Lists DB
	* scenario 5.  User has been removed from the mailing list (table listuser)
	* 
	* If a subscribed customer is deleted completely from PHPLists this will not affect ZC.
	* He will just be copied in again on the next sync.
	*/

	$lists_check_query = "select u.id,u.email,u.htmlemail,u.disabled,lu.listid from ". $lists_dbname . "." . $lists_prefix ."user_user u left join " . $lists_dbname . "." . $lists_prefix ."listuser lu on u.id = lu.userid where u.id = ". $customers_id;
	//echo $lists_check_query.'<br/>';
	$lists_check_result = mysql_query($lists_check_query) or die('<span class="red"><strong>Error</strong>, could not select from the Lists DB.</span><br/>');
	if ($check_row = mysql_fetch_array($lists_check_result, MYSQL_ASSOC))
	{ 
		//the user is held in the phplists db.
		
		// scenario 4 / 2 / 5 
		// The user has been unsubscribed in some way in phplist.
		
		if ($check_row['disabled'] == 1 || $subscribed == 0 || !$check_row['listid']) {
			$reason="";
			if ($check_row['disabled'] == 1) { $reason .= " disabled in phplist"; }
			if (!$check_row['listid']) { $reason .= " not in listuser table"; }
			if ($subscribed == 0) { $reason .= " not subscribed in zencart"; }
			
			$action_query = "delete from ". $lists_dbname . "." . $lists_prefix ."listuser where userid = ". $customers_id;
			mysql_query($action_query) or die('<span class="red"><strong>Error</strong>, a delete from the lists list_user table has failed - Customer: '. $customers_id .'</span><br/>');
			$action_query = "delete from ". $lists_dbname . "." . $lists_prefix ."user_user where id = ". $customers_id;
			mysql_query($action_query) or die('<span class="red"><strong>Error</strong>, a delete from Lists has failed - Customer: '. $customers_id .'</span><br/>');
			//$action_query = "update ". $zc_dbname . "." . $zc_prefix ."customers set customers_newsletter = 0 where customers_id = ".$customers_id;
			//mysql_query($action_query) or die('<span class="red"><strong>Error</strong>, I was unabled to unsubscribe customer: '. $customers_id .'</span><br/>');
			echo ('<span class="green">Customer '. $customers_id.' deleted.'. $reason .'</span><br/>');	
			$changes_delete ++;
		}
		
		//scenario 2 - email / format change
		
		elseif ($check_row['email'] != $mail || $check_row['htmlemail'] != $htmlemail )
		{
			$lists_query = "update ". $lists_dbname . "." . $lists_prefix ."user_user set email = '". $mail ."', htmlemail = ". $htmlemail ." where id = ". $customers_id;
			mysql_query($lists_query) or die('<span class="red"><strong>Error</strong>, I was unabled to update email fields for customer: '. $customers_id .'</span><br/>');
			echo ('<span class="green">Customer '. $customers_id.' has changed his email or html / text choice.  Updated.<br/>');	
			$changes_edit ++;
		}
		
		//scenario 1 - no change
		else 
		{
			//echo ('<span class="blue">Customer '. $mail.' is already on the list and there are no changes in his details.</span><br/>');
		}
	}
	
	// so, he's not in the DB.  Do the copy if he has subscribed
	
	elseif ($subscribed == 1)
	{
		$customers_disabled = 0;
		
		echo ('<span class="green">Copying id '. $customers_id .', email '. $mail .'</span><br/>');
		
		//create and check the unique id 
		$id = md5(uniqid(mt_rand()));
		$id_result = mysql_query("select id from ". $lists_dbname . "." . $lists_prefix ."user_user where uniqid = \"$id\"");
		while (mysql_fetch_row($id_result))
		{
			$id = md5(uniqid(mt_rand()));
			$id_result = Sql_Query("select id from ". $lists_dbname . "." . $lists_prefix ."user_user where uniqid = \"$id\"");
		}
		
		// have a valid unique id, so insert
		
		$lists_query = "insert into ". $lists_dbname . "." . $lists_prefix ."user_user (id,email,htmlemail,confirmed,uniqid,disabled) values ('". $customers_id ."','". $mail ."',$htmlemail,1,'".$id."',$customers_disabled)";
		mysql_query($lists_query) or die('<span class="red"><strong>Error</strong>, lists insert query into userser table failed.</span><br/>');
		$lists_query = "insert into ". $lists_dbname . "." . $lists_prefix ."listuser (userid,listid) values ('". $customers_id ."','". $list_id ."')";
		mysql_query($lists_query) or die('<span class="red"><strong>Error</strong>, lists insert query into userlist table failed.</span><br/>');
		$changes_add ++;
	}
	else {
		//echo ('<span class="blue">User '. $mail .' is not subscribed to the newsletter and was left in peace.</span><br/>');
	}
}

echo ('</p><hr/><p>'. $count .' rows were processed.<br/>');
echo ($changes_add .' records were added.<br/>');
echo ($changes_edit .' records were edited.<br/>');
echo ($changes_delete .' records were deleted.<br/>');
echo ('</p></div></body></html>');
//}
?>

Reminder:

It's still in final testing! If you want to help with that and further improvement I would be really grateful.

**hqarrse2** · 7 Aug 2008, 03:47 AM

Now fully cracked although waiting final testing. It's a complete rewrite of the script with full integration by redirecting all ZC newsletter pages to PHPLists. If anyone is interested in giving input / testing please drop me an email at admin@olivenet.co.uk .

**hqarrse2** · 16 Aug 2008, 09:45 AM

This is live but in final testing. A couple of people are helping with this, but haven't come back to me yet.

I will probably be fully live at some point over the weekend at which point I will post a link to the live site and the code. There has been very little interest though so it may not be worth releasing as a formal submission.

**panelcart** · 16 Aug 2008, 02:52 PM

I a interested.

**hqarrse2** · 17 Aug 2008, 10:08 AM

OK - the big moment which is to allow people to subscribe via the phplists install. To see my front end go to www.rvops.co.uk and you will see a hoofing great newsletter image on the right.

I have a standard phplists install except a DB change discussed in the readme installed in my zencart root directory. This has its templates updated to suit the shop itself. I have not used the lists password login system - just the standard install where account access is done via links with an ID in them.

The ZC block and other newsletter functions use altered header files in the relevant parts of ZenCart to redirect users to PHPLists. All newsletter links in ZC should be neatly redirected with customer emails preserved and depending on login status and whether they are already subscribed in phplists.

Please read this and the readme well (the links to the code in the comments below). It is also recommended that you understand the flow diagram well.

Note that while I'm going to take the plunge this is still a development 'Alpha' and it is undeniably a bit of a quick fix. It does everything I want except that multiple lists are not allowed for at the moment. Also I don't bother with transferring any attributes (name etc) from ZC. However both of these only require simple mods to the script.

I won't waffle on now as I'm really after input from testers rather than providing a full mod. There are undoubtedly a lot of quirks but I just don't have fine to do this fully at the moment.

Code:

<?php

/* Olive Net Ltd Zen Cart to PHPLists Sync Script
 * Developed for www.rvops.co.uk
 * Comments, corrections and feedback to admin@olivenet.co.uk
 * V 1.0A 0913 04 Aug 08 
 * 
 * Test Alpha release - back up and use at your own risk!
 * Unlimited use and distribution but please credit where appropriate
 * 
 * General:
 * 
 * This script should be run before each mail shot.  It
 * checks the ZC and Lists DBs against each other for
 * new customers / subscribers.
 * 
 * It is recommended that you fully understand the sequence
 * diagram available here:
 * 
 * http://www.olivenet.co.uk/index.php?page=development
 * 
 * The script was made for one particular configuration and enables the complete functional
 * replacement of ZC newsletter functions with a PHPLists installation, rather than being
 * a ZC modification in its own right.  It does not therefore put phplists functionality 
 * in ZC as a true modification might do and it will need a reasonable level of knowledge
 * of web apps to do a good integration. 
 * 
 * Notes:
 * 
 * Requires modifications to customers table in ZC DB
 * and user_user table in PHPList DB as follows:
 * 
 * ZC: Add column of type Varchar(255) lists_unique_id to customers table default null
 * Lists: Add column zenid of type Integer to user_user table default null.
 * 
 * - Assumes that all mailing list admin (for customers and admin) will be done in PHPLists
 * so all ZC newsletter pages should be forwarded to phplists using the copied PHPLists unique id
 * as a URL parameter to make the process smoother for the customer.
 * 
 * - Users must therefore be redirected from ZC to PHPLists.  Olive Net have done this
 * by changing /includes/modules/pages/account_newsletters/header_php.php 
 * and /includes/modules/pages/unsubscribe/header_php.php .  This works well with a simple no-account-or-login 
 * phplists setup.
 * 
 * The changed files are available for 
 * download from:
 * 
 * http://www.olivenet.co.uk/index.php?page=development
 * 
 * - The script currently only handles one list.  It is a simple mod to allow more (see things-to-do).
 * 
 * - Olive Net use a right hand 'subscribe now' block.  This is also included in the download.
 * 
 * - Only the email address is copied - no other fields like name ('attributes' in phplist).  It would be
 * quite easy to modify the script to include these.
 * 
 * - Users should not be deleted from PHPLists - just unsubscribed.
 * 
 * - The only part that ZC plays in the newsletter is collecting the initial information during
 * customer registration and recording the choice of whether to subscribe.  Once that is copied 
 * in to PHPLists (when the script is next run), PHPLists takes over the admin role completely.  
 * A notable effect of this is that a user can have a different 
 * email address in PHPLists than in Zen Cart if changed after the initial sync.
 * 
 * - Since the PHP List front end is used quite a few layout tweaks to fit in with your ZC install.
 * This is therefore not a quick mod and does need asthetic work on PHPLists.  
 * 
 * - The script is 'ZC-safe' in that none of ZC's data is touched.  The script / PHPLists only writes
 * to the additional lists_unique_id field of the customers table and never deletes.
 * 
 * TO DO:
 * 
 * - When a user updates their mail address in ZC, they should be prompted to update their PHPLists one.
 * - All sections of the script should make use of transactions to allow rollback in case of error.
 * - Streamlining of laborious query code.
 * - Change the LIST_ID to an array so that more than one list can be handled.
 */ 

$debug = 1;
$dbhost = 'localhost';
$dbuser = ''; 
$dbpass = '';
define('ZC_DBNAME','');
define('ZC_PREFIX','');
define('LISTS_DBNAME','');
define('LISTS_PREFIX','_');
define('LIST_ID','2'); //the id of the mailing list you want to sync with ZC
define('LIST_SUBSCRIBE_PAGE','1');

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('<span class="red"><strong>Error</strong> connecting to mysql</span><br/>');

//get the dull html stuff done.

echo('<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">');
echo('<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">');
echo('<head><meta http-equiv="Content-Type" content="text/html;charset=utf-8" />');
echo('<style type="text/css" media="all">body {font-family: verdana, arial; font-size: 11px;}.red {color: red;} .green {color: green;} .blue {color: blue;} </style><title>Olive Net ZC / PHPLists Sync</title></head>');
echo('<body><div style="width: 750px; border: 1px solid #BCBCBC"><h3>Olive Net ZC / PHPList Syncronization Script</h3><p>In case of errors, stop, save the page or print it, and phone HELP!.</p><p>Use before every mail shot.</p><hr/><p>');
echo('<p>All OK? <a href="/lists/admin/?page=home">RETURN TO ADMIN and crack on!</a></p><hr/>');

echo('<p>Checking for deleted users - ZC</p>');

$zc_query = "select lu.id, lu.zenid, lu.email from ". LISTS_DBNAME . "." . LISTS_PREFIX . "user_user lu left join ". ZC_DBNAME . "." . ZC_PREFIX ."customers c on lu.zenid = c.customers_id where c.customers_id is null and lu.zenid > 0";
if ($debug > 0) echo '<span class="blue">Checking for subscribed users removed from ZC: </span>'.$zc_query.'<br/>';
$zc_result = mysql_query($zc_query) or die('<span class="red">Error, ZC unsubscribe check query failed.</span> <br/>');
$count = 0;
while($row = mysql_fetch_array($zc_result, MYSQL_ASSOC))
{
		$count ++;
		$zenid = $row['zenid'];
		$id = $row['id'];
		$email = $row['email'];
		$action_query = "delete from ". LISTS_DBNAME . "." . LISTS_PREFIX ."user_user where id = '". $id ."'";
		if ($debug > 0) echo '<span class="blue">Deleting user from user_user. Query: </span> '.$action_query.'<br/>';
		mysql_query($action_query) or die('<span class="red"><strong>Error</strong>, I was unabled to delete a user from Lists user_user</span><br/>');		
		$action_query = "delete from ". LISTS_DBNAME . "." . LISTS_PREFIX ."listuser where userid = '". $id ."'";
		if ($debug > 0) echo '<span class="blue">Deleting user from listuser. Query: </span> '.$action_query.'<br/>';
		mysql_query($action_query) or die('<span class="red"><strong>Error</strong>, I was unabled to delete a user from Lists listuser</span><br/>');		
		$action_query = "delete from ". LISTS_DBNAME . "." . LISTS_PREFIX ."user_blacklist where email = '". $email ."'";
		if ($debug > 0) echo '<span class="blue">Deleting user from user blacklist. Query: </span> '.$action_query.'<br/>';
		mysql_query($action_query) or die('<span class="red"><strong>Error</strong>, I was unabled to delete a user from Lists blacklist</span><br/>');		
}

echo ('<br/>'.$count . ' users were removed from ZC so are being removed from PHPLists<br/><br/>');

if ($debug > 0) echo '<span class="blue">These will be deleted in the final script from lists and the history table</span><br/><br/>';

echo('Checking for new ZC customers (who have subscribed)<br/>');

/* we check for customers with a null lists_unique_id value. These are new since the last check. */

$zc_query = "select customers_id, customers_email_address, customers_newsletter, customers_email_format from ". ZC_DBNAME . "." . ZC_PREFIX ."customers where lists_unique_id is null";
if ($debug > 0) echo '<span class="blue">ZC new subscriptions query:</span> '.$zc_query.'<br/>';
$zc_result = mysql_query($zc_query) or die('<span class="red">Error, ZC select query failed.</span> <br/>');
$count = 0;

while($row = mysql_fetch_array($zc_result, MYSQL_ASSOC))
{
	$uniqid = 0;
	$set_zen_subscribed = false; /* this will be used to decide if a zen customer subscription should be forced */
	$count ++;
	$mail = $row['customers_email_address'];
	$zenid = $row['customers_id'];
	$subscribed = $row['customers_newsletter'];
	$html = $row['customers_email_format'];
	$htmlemail = $html == "HTML" ? 1 : 0;
	
	/* does the user already exist in PHPLists (test by mail address)? */
	
	$lists_check_query = "select u.id,u.uniqid from ". LISTS_DBNAME . "." . LISTS_PREFIX ."user_user u where u.email = '". $mail ."'";
	if ($debug > 0) echo '<span class="blue">Search for user in PHPLists by email query: </span> '.$lists_check_query.'<br/>';
	$lists_check_result = mysql_query($lists_check_query) or die('<span class="red"><strong>Error</strong>, could not search by email from the Lists DB.</span><br/>');
	if ($check_row = mysql_fetch_array($lists_check_result, MYSQL_ASSOC))
	{
		$uniqid = $check_row['uniqid'];	
	
		/* update phplists user table */
		
		$action_query = "update ". LISTS_DBNAME . "." . LISTS_PREFIX ."user_user set zenid = '". $zenid ."' where id = ".$check_row['id'];
		if ($debug > 0) echo '<span class="blue">Update user in PHPLists as registerd in ZC query: </span> '.$action_query.'<br/>';
		mysql_query($action_query) or die('<span class="red"><strong>Error</strong>, I was unabled to insert a ZC user ID into the Lists user_user subscription table</span><br/>');		

		/* to do: We also need to check userlists table for our ZC list entry */

		/* we now say that a customer will be subscribed even
		 * if they said no on their registration, because they had
		 * already subscribed independently.  Set this to false if required */
		  
		$set_zen_subscribed = true;

	}
	
	/* else the customer is not in our lists DB. If they have chosen to subscribe we must add them*/ 
			
	else if ( $subscribed == 1 )
	{		
		$id = md5(uniqid(mt_rand()));
		$id_result = mysql_query("select id from ". LISTS_DBNAME . "." . LISTS_PREFIX ."user_user where uniqid = \"$id\"");
		while (mysql_fetch_row($id_result))
		{
			$id = md5(uniqid(mt_rand()));
			$id_result = Sql_Query("select id from ". LISTS_DBNAME . "." . LISTS_PREFIX ."user_user where uniqid = \"$id\"");
		}
			
		/* have a valid unique id, so insert into lists */
			
		$lists_query = "insert into ". LISTS_DBNAME . "." . LISTS_PREFIX ."user_user (id,email,entered,htmlemail,subscribepage,confirmed,uniqid,disabled,zenid) values ('','". $mail ."',NOW(),'".$htmlemail."','".LIST_SUBSCRIBE_PAGE."',1,'".$id."','0','" . $zenid . "')";
		if ($debug > 0) echo '<span class="blue">Inserting a new user into lists </span> '.$lists_query.'<br/>';
		mysql_query($lists_query) or die('<span class="red"><strong>Error</strong>, lists insert query into userser table failed.</span><br/>');
		if ($last_id = mysql_insert_id()) { 
		$lists_query = "insert into ". LISTS_DBNAME . "." . LISTS_PREFIX ."listuser (userid,listid) values ('". $last_id ."','". LIST_ID ."')";
		if ($debug > 0) echo '<span class="blue">Inserting the new user into listuser - query: </span> '.$lists_query.'<br/>';
		mysql_query($lists_query) or die('<span class="red"><strong>Error</strong>, lists insert query into userlist table failed.</span><br/>');
		}
		else {
			die('<span class="red"><strong>Error</strong>, after inserting a new user I couldnt get the primary key back.</span><br/>');
		}
	
		/* also need to insert the new uniqid in ZC.  Done at the end */
		
		$uniqid = $id;
	}

	/* now insert the uniqid into ZC customers table.  
	 * this will still be 0 if the new user is neither subscribed
	 * nor doesn't already exist. 0 is a 'mark-as-read' */
	
	if ($set_zen_subscribed)
	{
		$action_query = "update ". ZC_DBNAME . "." . ZC_PREFIX ."customers set lists_unique_id = '". $uniqid ."',customers_newsletter = 1 where customers_id = ".$zenid;
	}
	else {
		$action_query = "update ". ZC_DBNAME . "." . ZC_PREFIX ."customers set lists_unique_id = '". $uniqid ."' where customers_id = ".$zenid;
	}
	if ($debug > 0) echo '<span class="blue">Update ZC customers to add Uniqid. Query: </span> '.$action_query.'<br/>';
	mysql_query($action_query) or die('<span class="red"><strong>Error</strong>, I was unabled to insert a unique ID into the ZC customers table</span><br/>');		

}

echo('<br>'.$count.' new ZC users were processed.<br/><br/>');

/* almost there - just check for new PHPList registrations and link to ZC if users */

$lists_query = "select uniqid, email from ". LISTS_DBNAME . "." . LISTS_PREFIX ."user_user where zenid is null";
if ($debug > 0) echo '<span class="blue">Lists new subscriptions query:</span> '.$lists_query.'<br/>';
$lists_result = mysql_query($lists_query) or die('<span class="red">Error, Lists select query failed.</span> <br/>');
$count = 0;
while ($lists_check_row = mysql_fetch_array($lists_result, MYSQL_ASSOC))
{
	$lists_email = $lists_check_row['email'];
	$uniqid = $lists_check_row['uniqid'];
	$zc_query = "select customers_id from ". ZC_DBNAME . "." . ZC_PREFIX ."customers c where c.customers_email_address = '". $lists_email ."'";
	if ($debug > 0) echo '<span class="blue">Checking lists new subscriptions against ZC query:</span> '.$zc_query.'<br/>';
	$zc_result = mysql_query($zc_query) or die('<span class="red">Error, lists to ZC new select query failed.</span> <br/>');
	$count ++;
	if($row = mysql_fetch_array($zc_result))
	{
		$action_query = "update ". ZC_DBNAME . "." . ZC_PREFIX ."customers set lists_unique_id = '". $uniqid ."',customers_newsletter = 1 where customers_email_address = '".$lists_email."'";
		if ($debug > 0) echo '<span class="blue">Update ZC customers to add Uniqid. Query: </span> '.$action_query.'<br/>';
		mysql_query($action_query) or die('<span class="red"><strong>Error</strong>, I was unabled to insert a unique ID into the ZC customers table</span><br/>');		
		$action_query = "update ". LISTS_DBNAME . "." . LISTS_PREFIX ."user_user set zenid = '". $row['customers_id'] ."' where uniqid = '".$uniqid."'";
		if ($debug > 0) echo '<span class="blue">Update Lists users to add zenid. Query: </span> '.$action_query.'<br/>';
		mysql_query($action_query) or die('<span class="red"><strong>Error</strong>, I was unabled to insert a zenid into the Lists users table</span><br/>');		
	}
	else 
	{
		$action_query = "update ". LISTS_DBNAME . "." . LISTS_PREFIX ."user_user set zenid = 0 where uniqid = '".$uniqid."'";
		if ($debug > 0) echo '<span class="blue">Update Lists users to add a zenid of 0. Query: </span> '.$action_query.'<br/>';
		mysql_query($action_query) or die('<span class="red"><strong>Error</strong>, I was unabled to insert a zenid of 0 into the Lists users table</span><br/>');			
	}
	
}
echo('<br>'.$count.' new lists users were processed.<br/><br/>');
	
echo ('</div></body>');

?>

NOTE:

The main script is not secured at the moment. I have added a very simple bit of code to the start of the script which does enough IMHO. I call the script from a link inside the phplists admin. The link contains ?callcode=YOUR_PASSCODE. Since there is no input function in the script there is no real security issue if your script is run anyway except that repeated use could be used to load your server - a DOS effectively.

Code:

//simple security check

if ($_GET['callcode'] != "YOUR_PASSCODE")
{
        echo ('<h3>No access allowed.  Call this only from with the mailing list admin.</h3>');
}
else {

THE SCRIPT ABOVE

}

Also my shop only has about 500 registered customers - it's quick to run but I don't know how much time / resources it will need for a huge customer DB.

####################_

I think this is a good system and is mostly done after a lot of work. There is plenty to do and I invite the OS community to help with that :) . We are not software producers and this has been written to fir our needs rather than for flexibility so please be forgiving! As well as testing it would be superb if, for example, a developer could make this all transaction based for starters.

**brushwoodnursery** · 23 Oct 2008, 02:37 PM

How is this coming along? I really like phplist and would prefer a full integration.

**hqarrse2** · 31 Oct 2008, 10:51 AM

Great - all working fine with no further mods to above. Any feedback appreciated of course. See:

http://www.rvops.co.uk/lists/?p=subscribe

(BTW I was a factor of 10 out on figures in my post above. We have about 6000 customers on the DB and all works well).

**manuel_mra** · 2 Nov 2008, 03:37 PM

Hi friends:
I have the same trouble, but I did other solving :
1.- Install the PHPList in one folder under the root (following the instructions to setup).
2.- Create the general fields necesary to build the information about the list.
3.- Go to the PHPList admin and create a suscribe list with the fields.
4.- Go to the admin and view the subscribe list (See the source code). There are a many fields inside this scripts you need to put in your own page. All you need is to make up this page and call inside your site page. Don't forget the number of your subscribe list (ID).
The problem is that when i send the subscribe page it sends me to a popup window and i didn't fix it.

Manuel.