1064 error when trying to send customer email/newsletter

Hi,

The other night I upgraded my database from 1.3.8a to 1.3.9h.

Everything seems to be working well other than when I try to send a customer email - i get the following 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 'as s left join zen_customers as c on c.customers_id = s.customers_id' at line 1
in:
[select c.customers_firstname, c.customers_lastname, s.email_address as customers_email_address from as s left join zen_customers as c on c.customers_id = s.customers_id ]
If you were entering information, press the BACK button in your browser and re-check the information you had entered to be sure you left no blank fields.

I have read through some posts on similar problems and the following seems to be something that pops up.

Prior to upgrade, I had the newsletter subscribe and email exporter modules installed. I did not remove any of these before upgrading.

Could someone please describe what steps I need to take in order to get everything working again.

Thanks in advance

Amanda

lj20272:

Prior to upgrade, I had the newsletter subscribe ... modules installed. I did not remove any of these before upgrading.
That's the problem.
In your database, you have a "query_builder" table that has an entry that was altered by the newsletter-subscribe addon, and is looking for a table that no longer exists, namely TABLE_SUBSCRIBE.
You'll need to delete that entry from the database table, or replace it with the original entry that existed before you installed that addon which changed it.

Awesome, thanks for the quick reply.

I'm not too sure what I need to actually delete. Below is the SQL result for the query_builder table

Generation Time: Feb 10, 2011 at 08:07 PM
Generated by: phpMyAdmin 3.3.9 / MySQL 5.0.91-community-log
SQL query: SELECT * FROM zen_query_builder LIMIT 0, 30 ;
Rows: 11
query_id query_category query_name query_description query_string query_keys_list

1 email All Customers Returns all customers name and email address for sending mass emails (ie: for newsletters, coupons, GVs, messages, etc). select customers_email_address, customers_firstname, customers_lastname from TABLE_CUSTOMERS order by customers_lastname, customers_firstname, customers_email_address

2 email,newsletters Customer Account Newsletter Subscribers Returns name and email address of newsletter subscribers who have a customer account. select customers_firstname, customers_lastname, customers_email_address from TABLE_CUSTOMERS where customers_newsletter = '1'

3 email,newsletters Dormant Customers (>3months) (Subscribers) Subscribers who HAVE purchased something, but have NOT purchased for at least three months. select o.date_purchased, c.customers_email_address, c.customers_lastname, c.customers_firstname from TABLE_CUSTOMERS c, TABLE_ORDERS o WHERE c.customers_id = o.customers_id AND c.customers_newsletter = 1 GROUP BY c.customers_email_address HAVING max(o.date_purchased) <= subdate(now(),INTERVAL 3 MONTH) ORDER BY c.customers_lastname, c.customers_firstname ASC

4 email,newsletters Active customers in past 3 months (Subscribers) Newsletter subscribers who are also active customers (purchased something) in last 3 months. select c.customers_email_address, c.customers_lastname, c.customers_firstname from TABLE_CUSTOMERS c, TABLE_ORDERS o where c.customers_newsletter = '1' AND c.customers_id = o.customers_id and o.date_purchased > subdate(now(),INTERVAL 3 MONTH) GROUP BY c.customers_email_address order by c.customers_lastname, c.customers_firstname ASC

5 email,newsletters Active customers in past 3 months (Regardless of subscription status) All active customers (purchased something) in last 3 months, ignoring newsletter-subscription status. select c.customers_email_address, c.customers_lastname, c.customers_firstname from TABLE_CUSTOMERS c, TABLE_ORDERS o WHERE c.customers_id = o.customers_id and o.date_purchased > subdate(now(),INTERVAL 3 MONTH) GROUP BY c.customers_email_address order by c.customers_lastname, c.customers_firstname ASC

6 email,newsletters Administrator Just the email account of the current administrator select 'ADMIN' as customers_firstname, admin_name as customers_lastname, admin_email as customers_email_address from TABLE_ADMIN where admin_id = $SESSION:admin_id

7 email,newsletters Customers who have never completed a purchase For sending newsletter to all customers who registered but have never completed a purchase SELECT DISTINCT c.customers_email_address as customers_email_address, c.customers_lastname as customers_lastname, c.customers_firstname as customers_firstname FROM TABLE_CUSTOMERS c LEFT JOIN TABLE_ORDERS o ON c.customers_id=o.customers_id WHERE o.date_purchased IS NULL

8 email,newsletters All Newsletter Subscribers Returns name and email address of all Customer Account subscribers and all Newsletter-Only subscribers. select c.customers_firstname, c.customers_lastname, s.email_address as customers_email_address from TABLE_SUBSCRIBERS as s left join TABLE_CUSTOMERS as c on c.customers_id = s.customers_id

9 email,newsletters Newsletter-only Subscribers Returns email address of all confirmed Newsletter-Only subscribers. SELECT email_address as customers_email_address FROM TABLE_SUBSCRIBERS WHERE email_format != 'NONE' and confirmed = 1 and (customers_id IS NULL or customers_id = 0) order by email_address

10 email,newsletters Email Test Group - Newsletter-only subscribers Returns name and email address of Newsletter-only subscribers designated in Email test group configuration. SELECT s.email_address as customers_email_address FROM TABLE_SUBSCRIBERS as s LEFT JOIN TABLE_CONFIGURATION as q on LOCATE( s.email_address, q.configuration_value) >= 1 WHERE configuration_key = 'NEWSONLY_SUBSCRIPTION_TEST_GROUP'

11 email,newsletters Email Test Group - Customers Returns name and email address of Newsletter-only subscribers designated in Email test group configuration. SELECT c.customers_email_address as customers_email_address FROM TABLE_CUSTOMERS as c LEFT JOIN TABLE_CONFIGURATION as q on LOCATE( c.customers_email_address, q.configuration_value) >= 1 WHERE configuration_key = 'NEWSONLY_SUBSCRIPTION_TEST_GROUP'

Delete the entries I've highlighted in your post above.

Excellent!!! Have done as suggested and all is working fine.

Once again, thank you for your help.

Now, "if" I was to want to reinstall either of these modules at a later date, would it just be a matter of installing as if it were "new" installations?

THANK YOU! Was having the same problem but all fixed now thanks to this thread. :D

Hi, I have the same problem after upgrading to 1.3.9h with the old newsletter-subscribe mod. I know I'll need to upgrade again soon but I need to get this fixed first. When i try to email I get the same error message

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 'as s left join customers as c on c.customers_id = s.customers_id' at line 1
in:
[select c.customers_firstname, c.customers_lastname, s.email_address as customers_email_address from as s left join customers as c on c.customers_id = s.customers_id ]
If you were entering information, press the BACK button in your browser and re-check the information you had entered to be sure you left no blank fields.

My question is, ( and maybe it's a dumb one) but, what happens to the newsletter only subscribers when I fix this?

marknew:

My question is, ( and maybe it's a dumb one) but, what happens to the newsletter only subscribers when I fix this?If by "when I fix this" you mean removing the query_builder entries I discussed earlier, then nothing will happen to the data in the newsletter-subscribers-addon tables. It just won't be retrievable. It'll all still be there, and you could write a script to extract the data and push it into something like the Mailchimp and use the Mailchimp Newsletter Signup Sidebox instead. That's a much smarter choice than trying to use the old newsletter-subscribe mod that mangles all kinds of core code files.

Thanks DRByte,

that's exactly what I mean. The problem I now have is getting it into mailchimp. I'm no programmer and I couldn't write a script to export the email addresses. I don't suppose there is a script out there to take it from newsletter subscribe to mailchimp?

If I remember that newsletter-subscribe mod properly, you need a file that contains a statement like ```
define('TABLE_SUBSCRIBE', 'subscribe');

Thanks, I'll give it a try.

I had this problem with my upgrade from 1.3.8 to 1.5 - I won't install Newsletter Subscribe again, and this thread helped me to get everything functional again. Thanks!

However, the very first thing my client wants to do when I launch this new site is to send an email to all subscribers, and now I can't access it. I installed Email Exporter again - this time the one for 1.5, but you are now saying that I have to put the define TABLE_SUBSCRIBE somewhere in order to allow those tables to be accessed in the dropdown of email address exporter.

Where exactly do I put this define('TABLE_SUBSCRIBE', 'subscribe'); so that my data shows up in email exporter pulldown menu? This is the last thing I have to figure out before I can launch :-)

Thanks.

You can put it in /admin/includes/extra_configures/defines_for_newsletter_subscribe.php

Shoot, that didn't do anything. I checked the database and I've got my 1,200 names and addresses there, but they are not coming in in the export dropdown. Any other ideas? I'm going to be forced to install that newsletter subscribe module again if I can't find another way to extract them, and I'm afraid to do that as there is not a version for 1.5, but my client needs to send the announcement email to those subscribers, so I'm not sure what my options are.

If you can see the info "in the database", then I assume you're referring to the use of a tool like phpMyAdmin.
And if that's the case, then you can just export the "subscribers" (or is it "newsletter_subscribers"?) table in a CSV format, and use that exported file for your mailing list service.