Customer List Improved

[Code Red]

I've just installed kremit's Customer List Improved contribution and it works very well, and with a bit of adaptation it's proved perfect for my needs.

 

However, I really wanted the outputted list (minus the edit/delete functions) to be visible in the public "front end" of the site, not in the Admin as I'm using the Customers facility for a different purpose. So I copied the file across into the catalog folder and made sure all the links to the includes etc were modified to run in that part of the site.

 

However, I'm now getting an SQL syntax 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 'select c.customers_id, c.customers_email_address, c.customers_te

select count(select c.customers_id, c.customers_email_address, c.customers_telephone, c.customers_fax, a.entry_company as company, a.entry_city as city, a.entry_street_address as street, a.entry_suburb as suburb, a.entry_postcode as postcode, a.entry_state as state_alt, z.zone_name as state, ctry.countries_iso_code_2 as country,c.customers_newsletter, a.entry_country_id from customers c left join address_book a on c.customers_id = a.customers_id and c.customers_default_address_id = a.address_book_id left join customers_info ci on c.customers_id = ci.customers_info_id left join countries ctry on a.entry_country_id = ctry.countries_id left join zones z on a.entry_zone_id = z.zone_id order by state ASC, city ASC, c.customers_lastname ASC) as total

 

This is the part of the code it doesn't like -

 

	$db_orderby = 'state ' . $sort . ', city ' . $sort . ', c.customers_lastname';

if(!$sort) $sort = 'ASC';

if (isset($HTTP_GET_VARS['search']) && tep_not_null($HTTP_GET_VARS['search'])) {
  $keywords = tep_db_input(tep_db_prepare_input($HTTP_GET_VARS['search']));
  $search = "where c.customers_lastname like '%" . $keywords . "%' or c.customers_firstname like '%" . $keywords . "%' or c.customers_email_address like '%" . $keywords . "%'";
}

$customers_query_raw = 'select c.customers_id, c.customers_email_address, c.customers_telephone, c.customers_fax, a.entry_company as company, a.entry_city as city, a.entry_street_address as street, a.entry_suburb as suburb, a.entry_postcode as postcode, a.entry_state as state_alt, z.zone_name as state, ctry.countries_iso_code_2 as country,c.customers_newsletter, a.entry_country_id from ' . TABLE_CUSTOMERS . ' c left join ' . TABLE_ADDRESS_BOOK . ' a on c.customers_id = a.customers_id and c.customers_default_address_id = a.address_book_id left join ' . TABLE_CUSTOMERS_INFO . ' ci on c.customers_id = ci.customers_info_id left join ' . TABLE_COUNTRIES . ' ctry on a.entry_country_id = ctry.countries_id left join ' . TABLE_ZONES . ' z on a.entry_zone_id = z.zone_id ' . $search . ' order by ' . $db_orderby . ' ' . $sort;

$customers_split = new splitPageResults($HTTP_GET_VARS['page'], MAX_DISPLAY_SEARCH_RESULTS, $customers_query_raw, $customers_query_numrows);
$customers_query = tep_db_query($customers_query_raw);
while ($customers = tep_db_fetch_array($customers_query)) {
  $info_query = tep_db_query("select customers_info_date_account_created as date_account_created, customers_info_date_account_last_modified as date_account_last_modified, customers_info_date_of_last_logon as date_last_logon, customers_info_number_of_logons as number_of_logons from " . TABLE_CUSTOMERS_INFO . " where customers_info_id = '" . $customers['customers_id'] . "'");
  $info = tep_db_fetch_array($info_query);

  if ((!isset($HTTP_GET_VARS['cID']) || (isset($HTTP_GET_VARS['cID']) && ($HTTP_GET_VARS['cID'] == $customers['customers_id']))) && !isset($cInfo)) {
	$country_query = tep_db_query("select countries_name from " . TABLE_COUNTRIES . " where countries_id = '" . (int)$customers['entry_country_id'] . "'");
	$country = tep_db_fetch_array($country_query);

	$reviews_query = tep_db_query("select count(*) as number_of_reviews from " . TABLE_REVIEWS . " where customers_id = '" . (int)$customers['customers_id'] . "'");
	$reviews = tep_db_fetch_array($reviews_query);

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

	$cInfo_array = array_merge($customers, $customer_info);
	$cInfo = new objectInfo($cInfo_array);
  }

 

This strikes me as a bit odd, as it's still looking at the same db tables as when it was running in admin - I was expecting problems with missed links or missing functions etc but not this. I usually see this sort of thing because I've written some bad code while trying to interrogate the db but I've not touched this bit at all - and if I load the original file still in admin, it works fine!

 

Any pointers on the (probably obvious) mistake I'm making here?

[cob-web]

Any pointers on the (probably obvious) mistake I'm making here?

 

Hey Code Red,

 

I have a same (or similar) problem with the MySQL-Syntax:

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 ''countries ctry on a.entry_country_id = ctry.countries_id left join zones z on a' at line 1

select count(*) as total from customers c left join address_book a on c.customers_id = a.customers_id and c.customers_default_address_id = a.address_book_id left join customers_info ci on c.customers_id = ci.customers_info_id left join 'countries ctry on a.entry_country_id = ctry.countries_id left join zones z on a.entry_zone_id = z.zone_id

 

Have you solved the problem yet?

 

Thanks for any hints

 

cob-web