Email Address Exporter Question

[DrByte]

You could use the DROP TABLE and CREATE TABLE and INSERT INTO statements, for just that table, from the original /zc_install/sql/mysql_zencart.sql script.

[vancouverdude]

I did what you suggested and it worked great, the email exporter is working perfectly!

Thanks for your help!!!!

[gbdriver]

I did what you suggested and it worked great, the email exporter is working perfectly!

Thanks for your help!!!!

Would you mind expanding on this. I have the same problem, I ran the following SQL statements, but I still have the problem:

# Table structure for table 'newsletters'
#

DROP TABLE IF EXISTS newsletters;
CREATE TABLE newsletters (
newsletters_id int(11) NOT NULL auto_increment,
title varchar(255) NOT NULL default '',
content text NOT NULL,
content_html text NOT NULL,
module varchar(255) NOT NULL default '',
date_added datetime NOT NULL default '0001-01-01 00:00:00',
date_sent datetime default NULL,
status int(1) default NULL,
locked int(1) default '0',
PRIMARY KEY (newsletters_id)
) ENGINE=MyISAM;

Thanks, Rob

[edywa]

I copied all the files into the respective folders but can't see the export email option in the tools menu. Tried it more than once and checked that the folders are correct but its still not working. I'm using v. 1.3.9b. Anybody can suggest a solution? thanks!

[woodlandsprite]

I did what you suggested and it worked great, the email exporter is working perfectly!

Thanks for your help!!!!

I'm going to second the plea for further details.
I'm completely new to modifying sql databases and I haven't any idea how to remove the remnants from the old newsletter subscribe mod from my database. I'm trying to rebuild from 1.3.0.2 onto 1.5.1

Thanks in advance to anyone who can assist.

[woodlandsprite]

ah ha!
I figured it out.

using the original /zc_install/sql/mysql_zencart.sql
search for all instances of "query_builder" and run the drop table, create table and insert into statements as a sql patch.

I realize this looks exactly like what Dr.Byte already suggested, but in my foggy brain, I did not latch onto the fact that it was the "query_builder" table that was of specific import.

[oavs]

ah ha!
I figured it out.

using the original /zc_install/sql/mysql_zencart.sql
search for all instances of "query_builder" and run the drop table, create table and insert into statements as a sql patch.

I realize this looks exactly like what Dr.Byte already suggested, but in my foggy brain, I did not latch onto the fact that it was the "query_builder" table that was of specific import.

OK I did that and still the same results. can some one suggest what else can be done please?


I have
Zencart 1.5.5d
EmailAddressExporter-151c2



===drop table==from Zencart 1.5.5d zc_install/sql/install/mysql_zencart.sql ===========

DROP TABLE IF EXISTS query_builder;
CREATE TABLE query_builder (
query_id int(11) NOT NULL auto_increment,
query_category varchar(40) NOT NULL default '',
query_name varchar(80) NOT NULL default '',
query_description TEXT NOT NULL,
query_string TEXT NOT NULL,
query_keys_list TEXT NOT NULL,
PRIMARY KEY (query_id),
UNIQUE KEY query_name (query_name)
) ENGINE=MyISAM COMMENT='Stores queries for re-use in Admin email and report modules';


===insert into statements=from Zencart 1.5.5d zc_install/sql/install/mysql_zencart.sql ================

## Insert the default queries for "all customers" and "all newsletter subscribers"
INSERT INTO query_builder ( query_id , query_category , query_name , query_description , query_string , query_keys_list ) VALUES ( '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', '');
INSERT INTO query_builder ( query_id , query_category , query_name , query_description , query_string , query_keys_list ) VALUES ( '2', 'email,newsletters', 'All Newsletter Subscribers', 'Returns name and email address of newsletter subscribers', 'select customers_firstname, customers_lastname, customers_email_address from TABLE_CUSTOMERS where customers_newsletter = \'1\'', '');
INSERT INTO query_builder ( query_id , query_category , query_name , query_description , query_string , query_keys_list ) VALUES ( '3', 'email,newsletters', 'Dormant Customers (>3months) (Subscribers)', 'Subscribers who HAVE purchased something, but have NOT purchased for at least three months.', 'select max(o.date_purchased) as 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, c.customers_lastname, c.customers_firstname HAVING max(o.date_purchased) <= subdate(now(),INTERVAL 3 MONTH) ORDER BY c.customers_lastname, c.customers_firstname ASC', '');
INSERT INTO query_builder ( query_id , query_category , query_name , query_description , query_string , query_keys_list ) VALUES ( '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, c.customers_lastname, c.customers_firstname order by c.customers_lastname, c.customers_firstname ASC', '');
INSERT INTO query_builder ( query_id , query_category , query_name , query_description , query_string , query_keys_list ) VALUES ( '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, c.customers_lastname, c.customers_firstname order by c.customers_lastname, c.customers_firstname ASC', '');
INSERT INTO query_builder ( query_id , query_category , query_name , query_description , query_string , query_keys_list ) VALUES ( '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', '');
INSERT INTO query_builder ( query_id , query_category , query_name , query_description , query_string , query_keys_list ) VALUES ( '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', '');

[oavs]

Only item from the drop down isn't working is "Dormant Customers (>3months)(Subscribers)(xxx)"
It is giving >> WARNING: An Error occurred, please refresh the page and try again. warning.

Rest of the drop downs are fine working???

[DrByte]

It is giving >> WARNING: An Error occurred, please refresh the page and try again. warning.

From the FAQs: http://www.zen-cart.com/content.php?...-and-try-again

[oavs]

here is the log.

[11-Jan-2017 07:46:29 Australia/Brisbane] Request URI: /xxxx/email_export.php?action=save, IP address: 14.202.99.123
#1 mysqli_query() called at [/home/xxxxx/public_html/includes/classes/db/mysql/query_factory.php:45]
#2 queryFactory->query() called at [/home/xxxxx/public_html/includes/classes/db/mysql/query_factory.php:257]
#3 queryFactory->Execute() called at [/home/xxxxx/public_html/xxxx/email_export.php:88]

[11-Jan-2017 07:46:29 Australia/Brisbane] PHP Warning: mysqli_query(): Empty query in /home/xxxxx/public_html/includes/classes/db/mysql/query_factory.php on line 45
[11-Jan-2017 07:46:29 Australia/Brisbane] Request URI: /xxxx/email_export.php?action=save, IP address: 14.202.99.123
#1 trigger_error() called at [/home/xxxxx/public_html/includes/classes/db/mysql/query_factory.php:167]
#2 queryFactory->show_error() called at [/home/xxxxx/public_html/includes/classes/db/mysql/query_factory.php:139]
#3 queryFactory->set_error() called at [/home/xxxxx/public_html/includes/classes/db/mysql/query_factory.php:266]
#4 queryFactory->Execute() called at [/home/xxxxx/public_html/xxxx/email_export.php:88]

[11-Jan-2017 07:46:29 Australia/Brisbane] PHP Fatal error: 0: :: ==> (as called by) /home/xxxxx/public_html/xxxx/email_export.php on line 88 <== in /home/xxxxx/public_html/includes/classes/db/mysql/query_factory.php on line 167

================================

Lines 44-49 query_factory.php

if(isset($queryLog)) $queryLog->start($query);
$result = mysqli_query($link, $query);
if(isset($queryLog)) $queryLog->stop($query, $result);
if(isset($queryCache)) $queryCache->cache($query, $result);
return($result);
}