Newsletter-Only Subscriptions for v1.3x

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_

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

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'');

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.

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.

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. :no:

Thanks,
Kelly

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'' ');

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

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. :wink2:

Thanks,
Kelly

duckydoodles.com:

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.

Hi NG,
I was able to set this up and added my email addresses. It doesn't recognize newsletter only subscribers. Is that the way it should work? If so...it works great.

Thanks you, :smile:
Kelly

sleepless:

I saw the error message for PHPMyadmin but not for the patch tool in my cart.

sigh what you posted is exactly what showed when I used the sql patch -so I [falsely] assumed you didn't understand the error message. Many people using the cart have only minimal knowledge of sql, tho I think it grows with every patch :0

I was not intending to imply..well anything...I just find it safer to work under the assumption that the person needs in-depth instruction rather than assume they know of what they speak and end up spending more time explaining. Especially since people will read the posts who don't have any knowlege and try to implement.

...and since you just posted again, I'll just respond here.

From your posts I thought you wanted to reference real customer accounts.
If you read the sql you'll see it only pulls from the customers table, not subscribers. I was planning on seeing if I could do both for the next update, but I'll try to do it now since I'm waiting for DD to respond.

-Ng_

No rush. I did notice that it was only pulling from the customer table. The ony reason it's a good idea to have both is that the original sent the emails in html or text nicely if the customer had an account. If it was an email for a subscriber only, it sent alot of code mixed with the text and no html. At least mine did. I actually pulled it off my site even before the later updates made it unusable.
So...it doesn't hurt to be able to check both.

Your assumption was the safest one to make. :smile:
It was for the same reason I was hesitant to post the code for fear someone may use it without checking to see if it worked first.

Thanks,
Kelly

Okay, I cannot get the three table join to work properly, so....

You'll still enter all email addresses in the single field in the Email configuration.
However when you select the groups to send to, you'll see the group you've already set up, which is only customer accounts that match the emails, and this group 'newsletter-only subscribers test group'. So to test you'll want to send to both.
If I knew the minimum MySQL versions people were running I could use subqueries or unions to combine both groups, but since I can't these would need to be separate groups.

Here's the sql for the patches page:

INSERT INTO query_builder
(query_category, query_name, query_description, query_string)
VALUES ('email,newsletters', 'Newsletter-only subscribers Test Group',
'Returns email address of Newsletter-only subscribers designated in Email Newsletter 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'' ');

I found an unrelated error while testing this-if you have HTML mail turned off the email format wasn't being set (not crucial, as it will always default to text), but it makes the admin section display a blank for preference, which I found annoying.
So I'll be fixing that and incorporate these test groups into the new release-I'll have update checks so you won't lose data.

No timetable on that release...it'll take a while to ensure updates are safe.

-Ng_

Now you lost me. I know I had a hard time trying to get the query to pull from the subscribe table. Obviously it is over my head. :blink:
So...I will wait for your next release. :smile:

Thanks,
Kelly

Is there a way to set the width of the newsletter subscribe box? It's really wide. I have the rest of my side boxes at 175 px.

Thanks!

Well it should size with the rest of the boxes-nothing's hardcoded other than to set the width of the email field as 30px less than the width of the field (15px on either side).

I looked at your site and it looks to all fit, so I'm guessing you got it figured out? If it was wider and you did something to fix it, can you let us know in case someone else runs into the problem?

Thx,

-Ng_

@duckydoodles.com:
I tried to mess up the cart by changing the header around, deleting files, etc...at most I got part of the header then errors (because the file didn't exist, etc).

If I disabled the newsonly-subscriptions in admin those errors vanished and the cart loaded normally.

It might be the contribution-but I think it's more likely a typo in the file.
Now I did manage to get a blank page by doing the following:

Inserting the full code (with enclosing tags) inside an area already marked with tags, e.g:

<?php require(DIR_WS_MODULES . 'sideboxes/search_header.php'); 
<?php
// BEGIN newsletter_subscribe mod 1/1
.... the code ...
// END newsletter_subscribe mod 1/1    
?>
?>

or by omitting the closing ?> and inserting the code in an html area (one not enclosed in tags), e.g.:

<?php require(DIR_WS_MODULES . 'sideboxes/search_header.php'); ?>
<?php
// BEGIN newsletter_subscribe mod 1/1
.... the code ...
// END newsletter_subscribe mod 1/1    
</div>

Actually I did get an error : parse error, unexpected '<' but I might have more error reporting on that you do. It's common for production servers to suppress them for security reasons, tho they should be enabled in application_top.php (around line 47: error_reporting(E_ALL); )

This is actually the most common cause of 'blank' pages-one that confounds me on occasion even tho I know it's something to look for.

I suggest you review any pages you've done manual changes to (special attn to tpl_header.php) and see if the is a typo/syntax error as mentioned above. If you have a text editor that colors to differentiate between php and html areas (like SciTE or Crimson) it might make it easier if you don't see it. Or you are free to email the file to me and I can check it on my system to see if that's the problem child.

Barring more info, that's the best advice I can offer. I see your site is still blank, so I wish you the best of luck in figuring it out.

-Ng_

NotGoddess:

? The 'Subscribe' sidebox title is not a link by default. Are your other sideboxes
linked? It might be a feature you have enabled somewhere.

[edit: found it]
edit /includes/modules/sideboxes/YOUR_TEMPLATE/subscribe.php
and add this somewhere:

  $title_link = false;

Let me know if that works and I'll include it in the next update.

-Ng_

Thanks NG, That's fixed the heading link!

Is there a way to set the width of the newsletter subscribe box? When i view my test site using firefox its ok and is the same size as my other right hand boxes, BUT in IE its way larger - any ideas how to fix this?