Newsletter-Only Subscriptions for v1.3x

[NotGoddess]

I found it-this is the query someone used for a test group:

INSERT INTO query_builder ( query_id , query_category , query_name , query_description , query_string ) VALUES ( '', 'email,newsletters', 'Newsletter to Test Group Only', 'Send sample newsletter to Customer Test Group only', 'select customers_email_address, customers_lastname, customers_firstname from TABLE_CUSTOMERS WHERE customers_group_pricing = \'2\'');

However, it requires you to modify your group pricing group...not something I'd be in favor of, since it does mods to other things.

But you can do that...I'll see if I can create a cleaner way to make test emails work, perhaps you'll be able to signify addresses in the email configuration, like you do CC emails for so many other options.

-Ng_

[KADesign]

I believe I used the one that gave options for three or so different groups. I only had to insert the code into mysql in the shop admin. I didn't use the one that has you create a pricing group. I preferred the other.


Thanks,
Kelly

[KADesign]

Here is a copy of one of them.


Copied from original post:
Okay, this approach will work for you. Take a look at the list of Customers in the Admin panel. The customers_id for the test account you want to use will be displayed next to the last name.

Now copy this code into your SQL patch tool in Admin and change the customers_id to the correct id for the test account.



CODE:
INSERT INTO query_builder ( query_id , query_category , query_name , query_description , query_string ) VALUES ( '', 'email,newsletters', 'Newsletter to Test Account Only', 'Send sample newsletter to Customer Test Account only', 'select customers_email_address, customers_lastname, customers_firstname from TABLE_CUSTOMERS WHERE customers_id = \'4\'');

[NotGoddess]

I'll have to think about it-you see I don't want people to have to hard code values (e.g. enter customer id directly in the query) as then they'd have to edit the database manually to change it.

I agree it's probably best to reference real customer accounts. I just have to figure out the proper query.

[NotGoddess]

Alrighty, I think I've got it.

2 sql statements: First one to setup an Email configuration field to hold the test emails, and one for the query builder. This is set up to use email addresses, but you could adapt it to use customer id's if you'd rather.

If you use prefixes in your database, you'll want to change the tables accordingly (but don't change the TABLE_CUSTOMERS, etc ones...zencart replaces with proper table names).

INSERT INTO configuration 
 (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, last_modified, date_added, ) 
 VALUES ('Newsletter Email Test Group', 'NEWSONLY_SUBSCRIPTION_TEST_GROUP', '', 'Enter email address, separated with a comma, of customers you would like to include in the test group.', 12, 40, now(), now() );

INSERT INTO query_builder 
(query_category, query_name, query_description, query_string) 
 VALUES ('email,newsletters', 'Newsletter Test Group',
 'Returns name and email address of Customer Accounts designated in Email configuration.',
 'SELECT c.customers_firstname, c.customers_lastname, c.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'' ');

After inserting, you'll want to enter email addresses that match your test accounts.

[KADesign]

Hi NG,

I tried to install the code using the sql patch tool and it wouldn't work. I also tried to install it through phpmyadmin. The error is listed below.

Error 
SQL query: 

INSERT INTO configuration(

configuration_title,
configuration_key,
configuration_value,
configuration_description,
configuration_group_id,
sort_order,
last_modified,
date_added,

)
VALUES (
'Newsletter Email Test Group', 'NEWSONLY_SUBSCRIPTION_TEST_GROUP', '', 'Enter email address, separated with a comma, of customers you would like to include in the test group.', 12, 40, now( ) , now( ) 
) 
MySQL said:  

#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 ') 
VALUES ('Newsletter Email Test Group', 'NEWSONLY_SUBSCRIPTION_TEST_GROUP', ' at line 2

The admin sql patch didn't specify what error, it just wouldn't work.

Thanks,
Kelly

[NotGoddess]

Actually it did specify the error-it told you where to look. but if you weren't versed in mysql you wouldn't know what to look for. There was an extra , at the end of the set statement.

Try this. I've added delete statements to remove the old ones just in case one did go through.

DELETE FROM configuration where configuration_key = 'NEWSONLY_SUBSCRIPTION_TEST_GROUP';

INSERT INTO configuration 
 (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, last_modified, date_added ) 
 VALUES ('Newsletter Email Test Group', 'NEWSONLY_SUBSCRIPTION_TEST_GROUP', '', 'Enter email address, separated with a comma, of customers you would like to include in the test group.', 12, 40, now(), now() );

DELETE FROM query_builder WHERE query_name = 'Newsletter Test Group';

INSERT INTO query_builder
(query_category, query_name, query_description, query_string)
VALUES ('email,newsletters', 'Newsletter Test Group',
'Returns name and email address of Customer Accounts designated in Email configuration.',
'SELECT c.customers_firstname, c.customers_lastname, c.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'' ');

[duckydoodles.com]

Hello. I've just installed the add-on and now when I go to my home page, its blank. Any ideas?

[KADesign]

Hi,

Thanks that seems to have worked.
I saw the error message for PHPMyadmin but not for the patch tool in my cart. If it showed it, i simply didn't see it before leaving the page.
Although not a pro at mysql, I do have some knowledge, at least enough to read the error statement. I only stated I didn't see it not that I didn't understand it.

Thanks,
Kelly

[NotGoddess]

Hello. I've just installed the add-on and now when I go to my home page, its blank. Any ideas?

I tried to send messages to you, only because this is a realtime problem, but no response so I'll give you what I think the problem is:

You copied tpl_header.php.example directly over your tpl_header.php file.
Now maybe you didn't but I'm guessing here.

The problem is that is only an example file...it just shows you where to make the edit to add the subscribe_header...since most people customize their header it's going to be different for everyone (so I couldn't have a file that overwrote).

Try putting your original header file back in.

If that doesn't work, or is in no way close to what you did, get back to me.

You can try disabling the subscriptions in admin->configuration->my store
and set Enable Newsletter-only subscriptions? to false.