Query Builder Table syntax? - custom email groups

[mcarbone]

Hi,

I have searched the forum to help me build new newletter groups. I will be using the group pricing field to distinguish these groups. I have three new groups that I set up in group pricing(volunteers, supporters and sponsors)

I found a few posts that recommend to add via install SQL patches this SQL stmt to add the volunteers group. This is what I processed:

INSERT INTO query_builder (query_category , query_name , query_description , query_string ) VALUES ( 'email,newsletters', 'volunteers', 'Newsletter subscribers who are in the volunteer group', 'select c.customers_email_address, c.customers_lastname, c.customers_firstname, c.customers_group_pricing from TABLE_CUSTOMERS c, where c.customers_newsletter = \'1\' AND c.customers_group_pricing = volunteers GROUP BY c.customers_email_address order by c.customers_lastname, c.customers_firstname ASC');

I am getting this error in both the newletter and send email mgrs:

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 'where c.customers_newsletter = '1' AND c.customers_group_pricing = 'volunteers' ' at line 1
in:
[select c.customers_email_address, c.customers_lastname, c.customers_firstname, c.customers_group_pricing from customers c, where c.customers_newsletter = '1' AND c.customers_group_pricing = 'volunteers' GROUP BY c.customers_email_address order by c.customers_lastname, c.customers_firstname ASC ]
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.

Can anyone guide me to where I'm going wrong. WARNING - SQL - newb.

Thanks sooo much!

MC

[DrByte]

Try this instead:

INSERT INTO query_builder (query_category , query_name , query_description , query_string ) VALUES ( 'email,newsletters', 'volunteers', 'Newsletter subscribers who are in the volunteer group', 'select c.customers_email_address, c.customers_lastname, c.customers_firstname, c.customers_group_pricing from TABLE_CUSTOMERS c where c.customers_newsletter = 1 AND c.customers_group_pricing = 4 GROUP BY c.customers_email_address order by c.customers_lastname, c.customers_firstname ASC');

And change the 4 to whatever number the "volunteers" group is. You can see that number in the URL in your admin area when you click on that group. (I think it shows as gID=XXXX in the URL.) (It also shows in the sidebar header when editing the group details.)

Also note that I took out the two \' marks around the \'1\' because they're not needed there.

[mcarbone]

thanks, you're the best dr. byte!! It worked perfectly! Yes!!!! New email groups!

MC

[mcarbone]

My client wants people to select which newletter/group they belong to when they subscribe / create an account. Since I made my "pricing" groups into newsletter groups also, I needed subscribers to choose which group they belong to. I realize this would not suit most stores...but here's what I came up with for our site in case anyone would like to know.

in includes/languages/engligh.php I added around line 265

define('ENTRY_PRICING_GROUP', 'Newsletter group');
define('TEXT_NONE', 'Select'); 

in includes/templates/my_template/templates/tpl_modules_create_account.php I added around line 184:

<?php 
echo ENTRY_PRICING_GROUP;

    $group_array_query = $db->execute("select group_id, group_name, group_percentage from " . TABLE_GROUP_PRICING);
    $group_array[] = array('id'=>0, 'text'=>TEXT_NONE);

    while (!$group_array_query->EOF) {
        $group_array[] = array('id'=>$group_array_query->fields['group_id'], 'text'=>$group_array_query->fields['group_name'].'&nbsp;'.$group_array_query->fields['group_percentage'].'%');
        $group_array_query->MoveNext();
    }

    echo zen_draw_pull_down_menu('customers_group_pricing', $group_array, $cInfo->customers_group_pricing);
    ?>
    <br class="clearBoth" />
</fieldset>

MC

[mcarbone]

ummmm...boy do I feel stupid! I thought I had this all figured out and posted, but realized I need to gather the info in the DB. That would help.

I looked at several threads for adding a field to the registration page. I configured that page itself but I can't seem to get the db to populate the field chosen.

Here's what I did:

in includes/modules/CUSTOM/create_account.php
I added after line 68:

$group_pricing = zen_db_prepare_input($_POST['group_pricing']); 

and after line 255 I added:

'customers_group_pricing' => $group_pricing, 

tested and nothing from the dropdown was input in the DB. I know this may be due to the dropdown option.

Please help. Stuck!

Thanks a bunch!

MC