PHPList integration to OsCommerce

valguss · May 15, 2008

Hi There - i've just installed this contributuion and it seems to be working great- thanks

However, becasue i've installed it late on, i have many customers in the database who aren't added to phpList. Has anyone worked on a script to import all these users?

Cheers

Scratch that - i've just had a tinker and have made an importing script - i've made it available on the contribution page

TracyS · June 5, 2008

I have this problem as well and cannot find where a fix has been posted. Has anyone figured this one out yet?

Here is my exact error - with a fake email address as it does pull up an email address just fine:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'phplist_user_user where email = 'name@provider.com'' at line 1

select id, email, foreignkey from domain.com DBname phplist_user_user where email = 'name@provider.com'

The code it seems to be referring to is in admin/customers.php

		$existing_email_query = tep_db_query("select id, email, foreignkey from " . PHPLIST_DB . "" . PHPLIST_TABLE_PREFIX . "user_user where email =  " . $cInfo->customers_email_address . ""); //check for existing by email address

I've also tried

		$existing_email_query = tep_db_query("select id, email, foreignkey from " . PHPLIST_DB . "" . PHPLIST_TABLE_PREFIX . "user_user where email =  '" . $cInfo->customers_email_address . "'"); //check for existing by email address

Has anyone successfully gotten rid of this error?

I've got the exact same problem with my install. Did you ever get an answer or figure out how to
fix this error?

TracyS · June 10, 2008

I have this problem as well and cannot find where a fix has been posted. Has anyone figured this one out yet?

Here is my exact error - with a fake email address as it does pull up an email address just fine:
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'phplist_user_user where email = 'name@provider.com'' at line 1

select id, email, foreignkey from domain.com DBname phplist_user_user where email = 'name@provider.com'

Ok - I put all of my phpList database tables into my osC database and now I've gotten rid of the above error.

I have a new error though - and I cannot find a fix that works.

ERROR on admin/customers.php when clicking "edit" :

Warning: reset() [function.reset]: Passed variable is not an array or object in /home/.lew/site.com/admin/includes/classes/object_info.php on line 17

Warning: Variable passed to each() is not an array or object in /home/.lew/site.com/admin/includes/classes/object_info.php on line 18

I have found and tried this fix:

To eliminate thee array_merge() errors, edit the admin/customers.php file:

Find these lines:

$customer_info = array_merge($country, $info, $reviews);

$cInfo_array = array_merge($customers, $customer_info); 


and change them to:

$customer_info = array_merge((array)$country, (array)$info, (array)$reviews);

$cInfo_array = array_merge((array)$customers, (array)$customer_info);

This did not fix the problem. I have read that one can cause an "is_array" before calling this in admin/classes/object_info.php

  class objectInfo {

// class constructor
function objectInfo($object_array) {
  if (isset(($object_array)){
  while (list($key, $value) = each($object_array)) {
	$this->$key = tep_db_prepare_input($value);
  }
}
}	
 }

But I don't know how to get it to check to see if it is an array first. Also, it appears from other people's posts, that this error is typically caused by a field not being filled in on the database. If I run admin/customers.php before making any changes to it for the phpList contrib it works perfectly. But as soon as I try to add these changes it acts up.

One other thing I thought of is that the define file only allows you to set one list - but I have two lists, one for Retail and one for Wholesale customers. As people sign up on the website they are automatically signed up as Retail - so it only goes to that list with phpList. Then, if I change their status online to Wholesale I can go into the phpList admin and change what list they are subscribed to. But I was wondering if this would cause the above error?

So far I have to just pull a list of all of the customers in the database and their customer group into excel, then I separate by group and upload to the phpList database according to group and telling the admin in phpList not to change any existing files.

This would be soo much easier if I could this bit of the contribution working so I wouldn't have to upload everytime we want to send an email - not to mention, what happens if the customer uses the osC website to change their newsletter status. Then it won't filter through to phpList.

If anybody has any ideas on how to get this working and avoid the above error it would be greatly appreciated :blush:

skhuu · June 16, 2008

ERROR on admin/customers.php when clicking "edit" :
Warning: reset() [function.reset]: Passed variable is not an array or object in /home/.lew/site.com/admin/includes/classes/object_info.php on line 17

Warning: Variable passed to each() is not an array or object in /home/.lew/site.com/admin/includes/classes/object_info.php on line 18

I have found and tried this fix:
To eliminate thee array_merge() errors, edit the admin/customers.php file:

Find these lines:

$customer_info = array_merge($country, $info, $reviews);

$cInfo_array = array_merge($customers, $customer_info); 


and change them to:

$customer_info = array_merge((array)$country, (array)$info, (array)$reviews);

$cInfo_array = array_merge((array)$customers, (array)$customer_info);

I have the above problem, am usng the latest version, has anybody found a fix for this?

Thanks

Jonathan DC · June 29, 2008

I'm verry happy with this contribution and works perfect except one thing:

In admin/stats_newsletter.php

When I have for example 2 costumers sign in for newsletter the result here is:

Number of members registered with the newsletter : 0

This is the stats_newsletter.php

<?php
/*
$Id: stats_newsletter.php,v 1.1 2007/11/22 09:00:00 
Contribution by Nekosign, webmaster@nekosign.com 

osCommerce, Open Source E-Commerce Solutions
http://www.oscommerce.com

Copyright (c) 2002 osCommerce

Released under the GNU General Public License
*/

 require('includes/application_top.php');

?>
<!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN">
<html <?php echo HTML_PARAMS; ?>>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=<?php echo CHARSET; ?>">
<title><?php echo TITLE; ?></title>
<link rel="stylesheet" type="text/css" href="includes/stylesheet.css">
<link rel="icon" href="favicon.ico" />
</head>
<body>
<!-- header //-->
<?php require(DIR_WS_INCLUDES . 'header.php'); ?>
<!-- header_eof //-->

<!-- body //-->
<table border="0" width="100%" cellspacing="2" cellpadding="2">
 <tr>
<td width="<?php echo BOX_WIDTH; ?>" valign="top"><table border="0" width="<?php echo BOX_WIDTH; ?>" cellspacing="1" cellpadding="1" class="columnLeft">
<!-- left_navigation //-->
<?php require(DIR_WS_INCLUDES . 'column_left.php'); ?>
<!-- left_navigation_eof //-->
</table></td>
<!-- body_text //-->
<td width="100%" valign="top">	  

<table border="0" width="100%" cellspacing="0" cellpadding="2">
  <tr>
	<td><table border="0" width="100%" cellspacing="0" cellpadding="0">
	  <tr>
		<td class="pageHeading"><?php echo HEADING_TITLE; ?></td>
		<td class="pageHeading" align="right"><?php echo tep_draw_separator('pixel_trans.gif', HEADING_IMAGE_WIDTH, HEADING_IMAGE_HEIGHT); ?></td>
	  </tr>
	</table></td>
  </tr>
<?php
 if ($HTTP_GET_VARS['cpage'] > 1) $rows = $HTTP_GET_VARS['cpage'] * MAX_DISPLAY_SEARCH_RESULTS - MAX_DISPLAY_SEARCH_RESULTS;

$customers_query_raw = "select c.customers_id, c.customers_firstname, c.customers_lastname, c.customers_email_address, c.customers_newsletter , b.customers_info_id , b.customers_info_date_account_created as date_account_created
						from " . TABLE_CUSTOMERS . " c," . TABLE_CUSTOMERS_INFO . " b where c.customers_id = b.customers_info_id and c.customers_newsletter = '1'
						order by b.customers_info_date_account_created, c.customers_lastname, c.customers_firstname";


$customers_split = new splitPageResults($HTTP_GET_VARS['cpage'], MAX_DISPLAY_SEARCH_RESULTS, $customers_query_raw, $customers_query_numrows);
$customers_query = tep_db_query($customers_query_raw);

?>
  <tr>
	  <td class="dataTableContent" colspan="3"><?php echo TEXT_DESCRIPTION; ?><strong><?php echo $customers_query_numrows; ?></strong></td>
  </tr>
  <tr>
	  <td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td>
  </tr>
  <tr>
	<td><table border="0" width="100%" cellspacing="0" cellpadding="0">
	  <tr>
		<td valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="4">
		  <tr class="dataTableHeadingRow">
			<td class="dataTableHeadingContent" width="10%"><?php echo TABLE_HEADING_NUMBER; ?></td>
			<td class="dataTableHeadingContent" width="20%"><?php echo TABLE_HEADING_LAST_NAME; ?></td>
			<td class="dataTableHeadingContent" width="20%"><?php echo TABLE_HEADING_FIRST_NAME; ?></td>
			<td class="dataTableHeadingContent" width="30%"><?php echo TABLE_HEADING_EMAIL; ?></td>
			<td class="dataTableHeadingContent" width="25%"><?php echo TABLE_HEADING_CREATE; ?></td>
		  </tr>

<?php	  

while ($customers = tep_db_fetch_array($customers_query)) {
  $rows++;

			if (strlen($rows) < 2) {
	$rows = '0' . $rows;
  }
?>
		  <tr class="dataTableRow" onMouseOver="this.className='dataTableRowOver';this.style.cursor='hand'" onMouseOut="this.className='dataTableRow'">
			<td width="30" nowrap class="dataTableContent"><?php echo $rows; ?>.</td>
			<td class="dataTableContent"><?php echo '<a href="' . tep_href_link(FILENAME_CUSTOMERS, tep_get_all_get_params(array('cID', 'action')) . 'cID=' . $customers['customers_id']. '&action=edit') . '">' . $customers['customers_lastname'] . '</a>';?> </td>
			<td class="dataTableContent"><?php echo $customers['customers_firstname']; ?></td>
			<td class="dataTableContent"><?php echo '<a href="' . tep_href_link(FILENAME_MAIL, 'selected_box=tools&customer=' . $customers['customers_email_address'], 'NONSSL') . '">' . $customers['customers_email_address'] . '</a>'; ?> </td>
			<td class="dataTableContent"><?php echo tep_date_short($customers['date_account_created']); ?></td>
		  </tr>
<?php
}
?>
		</table></td>
	  </tr>
	  <tr>
		<td colspan="3"><table border="0" width="100%" cellspacing="0" cellpadding="2">
		  <tr>
			<td class="smallText" valign="top"><?php echo $customers_split->display_count($customers_query_numrows, MAX_DISPLAY_SEARCH_RESULTS, $HTTP_GET_VARS['cpage'], 'Totaal <b>%s</b> van <b>%s</b> (van <b>%s</b> klanten)' , '', 'cpage'); ?></td>
			<td class="smallText" align="right"><?php echo $customers_split->display_links($customers_query_numrows, MAX_DISPLAY_SEARCH_RESULTS, MAX_DISPLAY_PAGE_LINKS, $HTTP_GET_VARS['cpage'], tep_get_all_get_params(array('cpage')), 'cpage'); ?></td>
		  </tr>
		</table></td>
	  </tr>
	</table></td>
  </tr>
</table>
	</td>
<!-- body_text_eof //-->
 </tr>
</table>
<!-- body_eof //-->

<!-- footer //-->
<?php require(DIR_WS_INCLUDES . 'footer.php'); ?>
<!-- footer_eof //-->
</body>
</html>
<?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?>

In admin/mail.php

When you select send "To All Newsletter Subscribers"

It reads also 0 Subscribers.

Also in admin/stats_inactive_user.php it reads subscribe= no

This is admin/stats_inactive_user.php.

<?php
/*
 $Id: stats_inactive_user.php,v 1.2 2004/05/02 15:00:00
 osCommerce, Open Source E-Commerce Solutions
 http://www.oscommerce.com

 Copyright (c) 2004 osCommerce

 Released under the GNU General Public License
 Created by John Wood - www.z-is.net
*/
require('includes/application_top.php');?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html <?php echo HTML_PARAMS; ?>>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=<?php echo CHARSET; ?>">
<title><?php echo TITLE; ?></title>
<link rel="stylesheet" type="text/css" href="includes/stylesheet.css">
<script language="javascript" src="includes/general.js"></script>
</head>
<body marginwidth="0" marginheight="0" topmargin="0" bottommargin="0" leftmargin="0" rightmargin="0" bgcolor="#FFFFFF">
<!-- header //-->
<?php require(DIR_WS_INCLUDES . 'header.php'); ?>
<!-- header_eof //-->

<!-- body //-->
<table border="0" width="100%" cellspacing="2" cellpadding="2">
 <tr>
<td width="<?php echo BOX_WIDTH; ?>" valign="top"><table border="0" width="<?php echo BOX_WIDTH; ?>" cellspacing="1" cellpadding="1" class="columnLeft">
<!-- left_navigation //-->
<?php require(DIR_WS_INCLUDES . 'column_left.php'); ?>
<!-- left_navigation_eof //-->
	</table></td>
<!-- body_text //-->
<td width="100%" valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2">
  <tr>
	<td>
		<table border="0" width="100%" cellspacing="0" cellpadding="0">
		  <tr> 
			<td class="pageHeading"> 
			  <?php echo HEADING_TITLE; ?>
			  <br>
			</td>
			<td class="pageHeading" align="right">
			  <?php echo tep_draw_separator('pixel_trans.gif', HEADING_IMAGE_WIDTH, HEADING_IMAGE_HEIGHT); ?>
			</td>
		  </tr>
		  <tr>
			<td class="dataTableContent"></td>
			<td class="pageHeading" align="right"> </td>
		  </tr>
		</table>
	  </td>
  </tr>
  <tr>
	<td><table border="0" width="100%" cellspacing="0" cellpadding="4">
	  <tr>
		<td valign="top">
<?php
$cust_query = tep_db_query("select customers_firstname, customers_lastname from " . TABLE_CUSTOMERS . " where customers_id = '" . (int)$HTTP_GET_VARS['id'] . "'");
$cust = tep_db_fetch_array($cust_query);
	if ($HTTP_GET_VARS['go'] == 'delete')
	{
		  echo '<br>' . sprintf(SURE_TO_DELETE, $cust[customers_firstname] . ' ' . $cust[customers_lastname]) . '<br><br><a href="' . tep_href_link(FILENAME_STATS_INACTIVE_USER,  'page=' . $HTTP_GET_VARS['page'] . '&go=deleteyes&id=' . $HTTP_GET_VARS['id']) . '">' . tep_image_button('button_delete.gif', IMAGE_DELETE) . '</a>  <a href="' . tep_href_link(FILENAME_STATS_INACTIVE_USER, 'page=' . $HTTP_GET_VARS['page']) . '">' . tep_image_button('button_cancel.gif', IMAGE_CANCEL) . '</a><br><br>';
	}
	 elseif ($HTTP_GET_VARS['go'] == 'deleteyes')
	 {
		  tep_db_query("DELETE FROM " . TABLE_CUSTOMERS . " where customers_id = '" . (int)$HTTP_GET_VARS['id'] . "'");
		  tep_db_query("DELETE FROM " . TABLE_CUSTOMERS_BASKET . " where customers_id = '" . (int)$HTTP_GET_VARS['id'] . "'");
		  tep_db_query("DELETE FROM " . TABLE_CUSTOMERS_BASKET_ATTRIBUTES . " where customers_id = '" . (int)$HTTP_GET_VARS['id'] . "'");
		  tep_db_query("DELETE FROM " . TABLE_CUSTOMERS_INFO . " where customers_info_id = '" . (int)$HTTP_GET_VARS['id'] . "'");
		  tep_db_query("DELETE FROM " . TABLE_ADDRESS_BOOK . " where customers_id = '" . (int)$HTTP_GET_VARS['id'] . "'");
		  tep_db_query("DELETE FROM " . TABLE_PRODUCTS_NOTIFICATIONS . " where customers_id = '" . (int)$HTTP_GET_VARS['id'] . "'");
		  echo '<br>' . sprintf(SIU_CUSTOMER_DELETED, $cust[customers_firstname] . ' ' . $cust[customers_lastname]) . '<br><br><br><a href="' . tep_href_link(FILENAME_STATS_INACTIVE_USER, 'page=' . $HTTP_GET_VARS['page']) . '">' . tep_image_button('button_back.gif', IMAGE_BACK) . '</a><br><br>';
	} 
	elseif ($HTTP_GET_VARS['go'] == 'deletenull')
	{
		echo '<br>' . SURE_TO_DELETE_NULL . '<br><br><a href="' . tep_href_link(FILENAME_STATS_INACTIVE_USER,  'page=' . $HTTP_GET_VARS['page'] . '&go=deletenullyes') .'">' . tep_image_button('button_delete.gif', IMAGE_DELETE) . '</a>  <a href="' . tep_href_link(FILENAME_STATS_INACTIVE_USER, 'page=' . $HTTP_GET_VARS['page']) . '">' . tep_image_button('button_cancel.gif', IMAGE_CANCEL) . '</a><br><br>';
	} 
	elseif ($HTTP_GET_VARS['go'] == 'deletenullyes')
	{
	  $siu_query_raw = "select ci.customers_info_date_of_last_logon, c.customers_id, c.customers_firstname, c.customers_lastname, c.customers_email_address, c.customers_newsletter from " . TABLE_CUSTOMERS_INFO . " ci join " . TABLE_CUSTOMERS . " c left join " . TABLE_ORDERS . " o on c.customers_id = o.customers_id where o.customers_id is NULL and c.customers_id = ci.customers_info_id  and (ci.customers_info_date_of_last_logon='0000-00-00 00:00:00' or ci.customers_info_date_of_last_logon is NULL) order by c.customers_id";
	  $siu_query = tep_db_query($siu_query_raw);
  while ($customers = tep_db_fetch_array($siu_query)) 
	  {
		  $cid=$customers[customers_id];
		  tep_db_query("DELETE FROM " . TABLE_CUSTOMERS . " where customers_id = '" . $cid . "'");
		  tep_db_query("DELETE FROM " . TABLE_CUSTOMERS_BASKET . " where customers_id = '" . $cid . "'");
		  tep_db_query("DELETE FROM " . TABLE_CUSTOMERS_BASKET_ATTRIBUTES . " where customers_id = '" . $cid . "'");
		  tep_db_query("DELETE FROM " . TABLE_CUSTOMERS_INFO . " where customers_info_id = '" . $cid . "'");
		  tep_db_query("DELETE FROM " . TABLE_ADDRESS_BOOK . " where customers_id = '" . $cid . "'");
		  tep_db_query("DELETE FROM " . TABLE_PRODUCTS_NOTIFICATIONS . " where customers_id = '" . $cid . "'");
		}	
	  echo '<br>' . sprintf(SIU_CUSTOMER_DELETED_NULL) . '<br><br><br><a href="' . tep_href_link(FILENAME_STATS_INACTIVE_USER, 'page=' . $HTTP_GET_VARS['page']) . '">' . tep_image_button('button_back.gif', IMAGE_BACK) . '</a><br><br>';
	}
	elseif ($HTTP_GET_VARS['go'] == 'deleterange')
	{
		$fromdate="$yy1-$mm1-$dd1 00:00:00";
		$todate="$yy2-$mm2-$dd2 23:59:59";
		$fromdate1="$dd1-$mm1-$yy1";
		$todate1="$dd2-$mm2-$yy2";
		echo '<br>' . sprintf(SURE_TO_DELETE_RANGE, $fromdate1 . ' to ' . $todate1) . '<br><br><a href="' . tep_href_link(FILENAME_STATS_INACTIVE_USER,  'page=' . $HTTP_GET_VARS['page'] . '&go=deleterangeyes&fromdate=' . $fromdate . '&todate=' . $todate.'&fromdate1=' . $fromdate1 . '&todate1=' . $todate1) .'">' . tep_image_button('button_delete.gif', IMAGE_DELETE) . '</a>  <a href="' . tep_href_link(FILENAME_STATS_INACTIVE_USER, 'page=' . $HTTP_GET_VARS['page']) . '">' . tep_image_button('button_cancel.gif', IMAGE_CANCEL) . '</a><br><br>';
	} 
	elseif ($HTTP_GET_VARS['go'] == 'deleterangeyes')
	{
	  $siu_query_raw = "select ci.customers_info_date_of_last_logon, c.customers_id, c.customers_firstname, c.customers_lastname, c.customers_email_address, c.customers_newsletter from " . TABLE_CUSTOMERS_INFO . " ci join " . TABLE_CUSTOMERS . " c left join " . TABLE_ORDERS . " o on c.customers_id = o.customers_id where o.customers_id is NULL and c.customers_id = ci.customers_info_id  and ci.customers_info_date_of_last_logon>='$fromdate' and ci.customers_info_date_of_last_logon<='$todate' order by c.customers_id";
	  $siu_query = tep_db_query($siu_query_raw);
	  while ($customers = tep_db_fetch_array($siu_query)) 
	  {
		  $cid=$customers[customers_id];
		  tep_db_query("DELETE FROM " . TABLE_CUSTOMERS . " where customers_id = '" . $cid . "'");
		tep_db_query("DELETE FROM " . TABLE_CUSTOMERS_BASKET . " where customers_id = '" . $cid . "'");
		tep_db_query("DELETE FROM " . TABLE_CUSTOMERS_BASKET_ATTRIBUTES . " where customers_id = '" . $cid . "'");
		tep_db_query("DELETE FROM " . TABLE_CUSTOMERS_INFO . " where customers_info_id = '" . $cid . "'");
		tep_db_query("DELETE FROM " . TABLE_ADDRESS_BOOK . " where customers_id = '" . $cid . "'");
		tep_db_query("DELETE FROM " . TABLE_PRODUCTS_NOTIFICATIONS . " where customers_id = '" . $cid . "'");
		}	
	  echo '<br>' . sprintf(SIU_CUSTOMER_DELETED_RANGE, $fromdate1 . ' to ' . $todate1) . '<br><br><br><a href="' . tep_href_link(FILENAME_STATS_INACTIVE_USER, 'page=' . $HTTP_GET_VARS['page']) . '">' . tep_image_button('button_back.gif', IMAGE_BACK) . '</a><br><br>';
	}elseif ($HTTP_GET_VARS['go'] == '' )
	{
			  echo tep_draw_form('export', FILENAME_STATS_INACTIVE_USER. '?go=deleterange');
			  echo FROMDATE." ";
			  echo tep_get_day_list('dd1');
			  echo tep_get_monthname_list('mm1');
			  echo tep_get_year_list('yy1');
			  echo " ".TODATE." ";
			  echo tep_get_day_list('dd2');
			  echo tep_get_monthname_list('mm2');
			  echo tep_get_year_list('yy2');
			  echo '   <input type="submit" value="'. SIU_DELETE .'">';
	  echo '</form>';
			  echo '<p>';
			  echo tep_draw_form('export', FILENAME_STATS_INACTIVE_USER. '?go=deletenull');
		  echo '   <input type="submit" value="'. SIU_DELETE_NULL .'">';
	  echo '</form>';
		 		 echo '<p>';
?>
		<table border="0" width="100%" cellspacing="0" cellpadding="4">
	<tr class="dataTableHeadingRow">
	  <td class="dataTableHeadingContent"><?php echo TABLE_HEADING_ID; ?></td>
	  <td class="dataTableHeadingContent"><?php echo TABLE_HEADING_CUSTOMERS; ?></td>
	  <td class="dataTableHeadingContent"><?php echo TABLE_HEADING_EMAIL; ?></td>
	  <td class="dataTableHeadingContent"><?php echo TABLE_HEADING_NEWS; ?></td>
	  <td class="dataTableHeadingContent"><?php echo TABLE_HEADING_LAST_LOGON; ?></td>
	  <td class="dataTableHeadingContent"><?php echo TABLE_HEADING_DELETE; ?></td>
	</tr>
<?php
  $siu_query_raw = "select ci.customers_info_date_of_last_logon, c.customers_id, c.customers_firstname, c.customers_lastname, c.customers_email_address, c.customers_newsletter from " . TABLE_CUSTOMERS_INFO . " ci join " . TABLE_CUSTOMERS . " c left join " . TABLE_ORDERS . " o on c.customers_id = o.customers_id where o.customers_id is NULL and c.customers_id = ci.customers_info_id order by c.customers_id";
  $siu_split = new splitPageResults($HTTP_GET_VARS['page'], MAX_DISPLAY_SEARCH_RESULTS, $siu_query_raw, $siu_query_numrows );
  $siu_query = tep_db_query($siu_query_raw);
  while ($customers = tep_db_fetch_array($siu_query)) 
  {
	if ($customers['customers_newsletter'] == '1') 
	{
		$customers['customers_newsletter'] = NEWSLETTER_YES;
	  } 
	  else
	   {
		$customers['customers_newsletter'] = NEWSLETTER_NO;
	  }
?>
  <tr class="dataTableRow"> 
	<td class="dataTableContent"><?php echo $customers['customers_id'];?></td>
	<td class="dataTableContent"><?php echo $customers['customers_firstname'] . ' ' . $customers['customers_lastname'];?></td>
	<td class="dataTableContent"><?php echo '<a href="mailto:' . $customers['customers_email_address'] . '"><u>' . $customers['customers_email_address'] . '</u></a>'; ?></td>
	<td class="dataTableContent"><?php echo $customers['customers_newsletter']; ?></td>
	<td class="dataTableContent"><?php echo tep_date_short($customers['customers_info_date_of_last_logon']); ?></td>
	<td class="dataTableContent"><?php echo '<a href="' . tep_href_link(FILENAME_STATS_INACTIVE_USER, 'go=delete&id=' . $customers['customers_id'] . '&page=' . $HTTP_GET_VARS['page']) .'">' . SIU_DELETE . '</a>'; ?></td>
  </tr>
<?php
 }
?>
  </table></td>
  </tr>
  <tr>
	<td colspan="6"><table border="0" width="100%" cellspacing="0" cellpadding="2">
	  <tr>
		<td class="smallText" valign="top"><?php echo $siu_split->display_count($siu_query_numrows, MAX_DISPLAY_SEARCH_RESULTS, $HTTP_GET_VARS['page'], TEXT_DISPLAY_NUMBER_OF_CUSTOMERS); ?></td>
		<td class="smallText" align="right"><?php echo $siu_split->display_links($siu_query_numrows, MAX_DISPLAY_SEARCH_RESULTS, MAX_DISPLAY_PAGE_LINKS, $HTTP_GET_VARS['page'], tep_get_all_get_params(array('page', 'info', 'x', 'y', 'cID'))); ?></td>
	  </tr>
   </table></td>
 </tr>
</table></td>
</tr>
</table></td>
<!-- body_text_eof //-->
 </tr>
</table>
<!-- body_eof //-->
<?php
 }

?>
<!-- footer //-->
<?php require(DIR_WS_INCLUDES . 'footer.php'); ?>
<!-- footer_eof //-->
</body>
</html>
<?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?>

I don't know how to change this so it reads also from phplist.

Anybody has the same problem?

ps: sorry for my english, I'am dutch.

WS Evolution · January 19, 2009

I am importing email addresses from OSCommerce and I was wondering if PHPList can handle single quotes in email addresses? For example: O'hagon@domain-name.co.uk

At the moment I don't import addresses with single quotes as the import script shows an error if so , but if I allow this will it cause any problems?

Would I have to add slashes to the address? would phplist remove slashes as standard if this was done?

or can phplist cope with email address with single quotes without any modification to the email string?

Regards,

Gareth

Johnatan · May 3, 2009

For me everything works great, but only one punctilio makes me crazy.

When I put checkmark to newsletter in create_account.php my customer is not subscribed to newsletter. I must go in admin section and open customer and makes him subscribed.

I think here is the mistake:

//subscribe logic
     if ($newsletter) {
       if (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, modified, disabled, uniqid, htmlemail, foreignkey) values ('" . $email_address . "', 1, " . PHPLIST_SPAGE . ", now(), now(), 0, '" . $id . "', " . PHPLIST_HTMLEMAIL . ", " . $customer_id . ")"); //insert the new user into phplist
         $user_query=tep_db_query("select id from " . PHPLIST_DB . "" . PHPLIST_TABLE_PREFIX . "user_user where email = '" . $email_address . "'"); //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'] . ", '" . $firstname . "')"); //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'] . ", '" . $lastname . "')"); //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 . ", now())"); //subscribe the new user to the correct list
         //generating history
         $history_detail_text .= "\nSubscribepage = " . PHPLIST_SPAGE . "\nforeign key = " . $customer_id . "\n";
         $attribute_name_query = tep_db_query("select name from " . PHPLIST_DB . "" . PHPLIST_TABLE_PREFIX . "user_attribute where id = " . PHPLIST_FIRSTNAME . "");
         $attribute_name = tep_db_fetch_array($attribute_name_query);
         $history_detail_text .= "" . $attribute_name[name] . " = " . $firstname . "\n";
         $attribute_name_query = tep_db_query("select name from " . PHPLIST_DB . "" . PHPLIST_TABLE_PREFIX . "user_attribute where id = " . PHPLIST_LASTNAME . "");
         $attribute_name = tep_db_fetch_array($attribute_name_query);
         $history_detail_text .= "" . $attribute_name[name] . " = " . $lastname . "\n";
         $list_name_query = tep_db_query("select name from " . PHPLIST_DB . "" . PHPLIST_TABLE_PREFIX . "list where id = " . PHPLIST_LISTNO . "");
         $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 through osC', '" . $history_detail_text . "', '" . $history_systeminfo_text . "')");

PLEASE HELP

BR

Johnatan

Johnatan · May 4, 2009

I found where the problem is.

something here....

// Begin PHPlist Newsletter add-on
/*        $customers_query = tep_db_query("select c.customers_id, c.customers_gender, c.customers_firstname, c.customers_lastname, c.customers_dob, c.customers_email_address, a.entry_company, a.entry_street_address, a.entry_suburb, a.entry_postcode, a.entry_city, a.entry_state, a.entry_zone_id, a.entry_country_id, c.customers_telephone, c.customers_fax, c.customers_newsletter, c.customers_default_address_id from " . TABLE_CUSTOMERS . " c left join " . TABLE_ADDRESS_BOOK . " a on c.customers_default_address_id = a.address_book_id where a.customers_id = c.customers_id and c.customers_id = '" . (int)$HTTP_GET_VARS['cID'] . "'");
       $customers = tep_db_fetch_array($customers_query);
       $cInfo = new objectInfo($customers);
*/
       $customers_query = tep_db_query("select c.customers_id, c.customers_gender, c.customers_firstname, c.customers_lastname, c.customers_dob, c.customers_email_address, a.entry_company, a.entry_street_address, a.entry_suburb, a.entry_postcode, a.entry_city, a.entry_state, a.entry_zone_id, a.entry_country_id, c.customers_telephone, c.customers_fax, c.customers_default_address_id from " . TABLE_CUSTOMERS . " c left join " . TABLE_ADDRESS_BOOK . " a on c.customers_default_address_id = a.address_book_id where a.customers_id = c.customers_id and c.customers_id = '" . (int)$HTTP_GET_VARS['cID'] . "'");
       $customers = tep_db_fetch_array($customers_query);
       $cInfo = new objectInfo($customers);

       require(DIR_WS_INCLUDES . '/phplist_define.php'); //get the phplist specifics

       //get user specifics from PHPlist
       $existing_email_query = tep_db_query("select id, email, foreignkey from " . PHPLIST_DB . "" . PHPLIST_TABLE_PREFIX . "user_user where email =  '" . $cInfo->customers_email_address . "'"); //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 = " . $cInfo->customers_id . ""); //check for existing by phplist userid in case they subscribed using another method.

       $existing_email = tep_db_fetch_array($existing_email_query);
       $existing_user = tep_db_fetch_array($existing_user_query);

       if (tep_db_num_rows($existing_user_query) > 0) //check for user subscription
         $newsletter_query = tep_db_query("select userid from " . PHPLIST_DB . "" . PHPLIST_TABLE_PREFIX . "listuser where listid = " . PHPLIST_LISTNO . " and userid = " . $existing_user['id'] . "");
       else
         $newsletter_query = tep_db_query("select userid from " . PHPLIST_DB . "" . PHPLIST_TABLE_PREFIX . "listuser," . PHPLIST_DB . "" . PHPLIST_TABLE_PREFIX . "user_user where listid = " . PHPLIST_LISTNO . " and userid = id and email = '" . $cInfo->customers_email_address . "'");
       $cInfo->customers_newsletter = ((tep_db_num_rows($newsletter_query) < 1) ? '0' : '1');
// End PHPlist Newsletter add-on

when I make a option to recieve newsletters in create_account.php this part of contrib makes me a change in admin/customers.php to unsubscribed

please if somone can help?!

Nathali · August 25, 2009

Hi everyone,

I would like to install PHPlist mailer. I want to install it on my host server, BUT as a separate unit from my OSCommerce.

(folder as xxxxxx.co.il/phplist & sepearated php database)

My questions are:

1. Can I install it by fantastico tool I have on my Cpanel, without worry that this tool will trash my OSCommerce ? or it is

better todo it manually ?

2. If it safe to install by Fantastico, what variables I had to fill before the installation start? Is it written somewhere here ?

Please advise and do not reply as "go and read in phplist installation guide!" :D (I'm tired from reading installations

guides)

3. If in the future I would like to integrate the newsletters will not be a problem.

4. I would like to use PHPlist to send mass emails to dealers & computer stores, what should I be aware of when I will

install PHPlist? (contacts groups, additional scripts etc.. )

If I made an headache to one of you from my long questions, please accept my apology in advance.. :)

Waiting please to any advise from you.. Thanks a lot!

Nathali

spuri · November 13, 2009

Hello,

I have just installed this great contribution.

Unfortunately I am not sure I use this on the right way.

I have Osc 2.2 with >1000 customers, installed phplist in separate db and run phplist_import so I have the subscribed users list in phplist.

I tried with my user to unsubscribe on the osc site, than I thought reimport will correct the phplist user database, but it does not. I thought I should run import everytime to update suscription changes form osc to phplist and I would like to only allow unsubscribe on the osc site.

So how should i use this?

thanks in advance

Edited November 13, 2009 by spuri

MyR · March 31, 2010

To everyone:

What are the specific features of phplist you want in oscommerce?

Open/View Tracking?

Click Tracking?

Bounce Processing?

May 10, 2010

To everyone:

What are the specific features of phplist you want in oscommerce?

Open/View Tracking?

Click Tracking?

Bounce Processing?

Templates, multiple lists (for me - retail and wholesale customers, high volume, low volume etc), opt-out link in email text as required by US law, and yes, bounce processing.

It seems a little pointless reinventing the engine underlying phplist - it works so well and is feature rich. I would really like to be able to integrate it more fully with OSC, including a common admin interface and user interface that is part of the 'My Account Info' box.

Unless someone can point me to a contribution that does what I describe, I am going to try to do it myself :unsure: . If anyone wants to join in the fun just let me know as I need all the help I can get :)

Dave

bobsi18 · May 29, 2010

Geah,

I've got phplist integrated with oscommerce, and I'm having a few problems. When a customer signs up for an account, if they have an ' (apostrophe) in their name, it throws an error:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'name')' at line 1

insert into phplist1.phplist_user_user_attribute (attributeid, userid, value) values ( 1, 5674, 'o'name')

[TEP STOP]

I have no idea what to do about this, any ideas?

bobsi18 · May 29, 2010

I think I've fixed this. Let me know if there is a better fix, or I've done something wrong...

On an oscommerce installation with the phplist addon installed, open create_account.php.

find (line 38 of my modified store):

$firstname = tep_db_prepare_input($_POST['firstname']);
$lastname = tep_db_prepare_input($_POST['lastname']);

replace with:

$firstname = tep_db_prepare_input($_POST['firstname']);
$fixed_firstnamename = addslashes($firstname);

$lastname = tep_db_prepare_input($_POST['lastname']);

$fixed_lastname = addslashes($lastname);

find

tep_db_query("insert into " . PHPLIST_DB . "" . PHPLIST_TABLE_PREFIX . "user_user_attribute (attributeid, userid, value) values ( 1, " . $user['id'] . ", '" . $firstname . "')"); //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'] . ", '" . $lastname . "')"); //add the users lastname to the attributes table

replace with

tep_db_query("insert into " . PHPLIST_DB . "" . PHPLIST_TABLE_PREFIX . "user_user_attribute (attributeid, userid, value) values ( 1, " . $user['id'] . ", '" . $fixed_firstname . "')"); //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'] . ", '" . $fixed_lastname . "')"); //add the users lastname to the attributes table

find:

$history_detail_text .= "" . $attribute_name[name] . " = " . $firstname . "\n";

replace with

$history_detail_text .= "" . $attribute_name[name] . " = " . $fixed_firstname . "\n";

find:

$history_detail_text .= "" . $attribute_name[name] . " = " . $lastname . "\n";

replace with

$history_detail_text .= "" . $attribute_name[name] . " = " . $fixed_lastname . "\n";

Edited May 29, 2010 by bobsi18

Fashion-Art · October 23, 2010

Hello everyone!

I' trying to install this contribution, but I don't quite understand the instructions... Hope someone will help me:)

Where should I upload the "Nyhedsbrev.php" file to?

At some point he says that I shall modify 1 link, but I cant find the link anywhere in the "nyhedsbrev.php file. Below I'm showing the part I'm talking about in the instruction file:

"You need to change 1 link to your own ;)

Search for:

1: http://www.moebelarkitekten.dk/lists/?p=subscribe&id=1'>http://www.moebelarkitekten.dk/lists/?p=subscribe&id=1

( Change to )

- Your own link, standard would be:

http://www.moebelarkitekten.dk/lists/?p=subscribe"'>http://www.moebelarkitekten.dk/lists/?p=subscribe"

Here is the whole instruction file:

"This file is custom made, so that you just need to modife it to your show.. ( I meen, put Stylesheet into the document.... )

This file has the option, so you can easy integrate it to your own show, the email formular is already made...

The subscribe thanks page is changed, so it make a pop up instead of a new page...

Files:

nyhedsbrev.php

You can change the filename if you would like.. ;)

Just upload it if you dont use right and left column...

Install 1 minut.

---------------

PLEASE NOTICE THAT THIS FILE IS CUSTOM MADE...

1: Missing the Includes=".... column_right.php

2: Missing the Includes=".... column_left.php

If you use those, just put the 2 lines in the file..

...

README:

You need to change 1 link to your own ;)

Search for:

1: http://www.moebelarkitekten.dk/lists/?p=subscribe&id=1'>http://www.moebelarkitekten.dk/lists/?p=subscribe&id=1

( Change to )

- Your own link, standard would be:

http://www.moebelarkitekten.dk/lists/?p=subscribe

....

TEXT Translation:

Search for:

1: Fornavn Translated: = First Name

2: Efternavn Translated: = Last Name

3: Tilmeld Nyhedsbrev Translated: = Submit Newsletter

Number 3 is the Button text..

If any question, please use forum ;)

I also copyed my newsletter subscribe form, to the file "TheOtherFile.txt" if you dont wanna use my file... ;) then your just modified if by yourself...

Credits goes to:

www.moebelarkitekten.dk

Architecht Furnitures"

Best regards

Michael Nielsen

Roaddoctor · March 22, 2012

minor fixed - tested 2.2rc2a

On an oscommerce installation with the phplist addon installed, open create_account.php.

find (line 38 of my modified store):

Find:

$firstname = tep_db_prepare_input($_POST['firstname']);
$lastname = tep_db_prepare_input($_POST['lastname']);

replace with:

$firstname = tep_db_prepare_input($_POST['firstname']);
$fixed_firstname = addslashes($firstname);
$lastname = tep_db_prepare_input($_POST['lastname']);
$fixed_lastname = addslashes($lastname);


find


	  tep_db_query("insert into " . PHPLIST_DB . "" . PHPLIST_TABLE_PREFIX . "user_user_attribute (attributeid, userid, value) values (" . PHPLIST_FIRSTNAME . ", " . $user['id'] . ", '" . $firstname . "')"); //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'] . ", '" . $lastname . "')"); //add the users lastname to the attributes table


replace with


	  tep_db_query("insert into " . PHPLIST_DB . "" . PHPLIST_TABLE_PREFIX . "user_user_attribute (attributeid, userid, value) values (" . PHPLIST_FIRSTNAME . ", " . $user['id'] . ", '" . $fixed_firstname . "')"); //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'] . ", '" . $fixed_lastname . "')"); //add the users lastname to the attributes table


find:
$history_detail_text .= "" . $attribute_name[name] . " = " . $firstname . "\n";

replace with
$history_detail_text .= "" . $attribute_name[name] . " = " . $fixed_firstname . "\n";

find:
$history_detail_text .= "" . $attribute_name[name] . " = " . $lastname . "\n";

replace with
$history_detail_text .= "" . $attribute_name[name] . " = " . $fixed_lastname . "\n";

alfredor · April 3, 2019

WARNING

If you get this message with this contribution
"Warning: Illegal string offset 'customers_newsletter' in ../account_newsletters.php"
is because in the script the $newsletter['customers_newsletter'] is not an Array.
You need to replace $newsletter['customers_newsletter'] -> $newsletter

FIND:
if ($newsletter_general != $newsletter['customers_newsletter']) {

REPLACE
if ($newsletter_general != $newsletter) {

***********

FIND:
echo tep_draw_checkbox_field('newsletter_general', '1', (($newsletter['customers_newsletter'] == '1') ? true : false), 'onclick="checkBox(\'newsletter_general\')"');

REPLACE
echo tep_draw_checkbox_field('newsletter_general', '1', (($newsletter == '1') ? true : false), 'onclick="checkBox(\'newsletter_general\')"');

PHPList integration to OsCommerce

Recommended Posts

Link to comment

Share on other sites

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Guest

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation