SQL Query Help Please

I have a ton of users that register on my site, however they are not subscribed to my newsletters.


  1. is it legal to auto subscribe them as my terms of service state that I can contact them blah blah blah, or is that part of any anti spam laws?


  2. can anyone tell me how i would do the sql query as I am not a db admin.



    Really appreciate this forums users, wouldnt be here without their gracious help day after day.



    Thanks,

    sraza

If you want to transfer users to the subscribers database table upon registration, then try this:



In “/include/common/profiles.php”



ADD PART IN RED



//
// Create new user
//
if ($mode == "add") {
if (AREA == 'A') {
fn_check_privileges('manage_users');
} else {
if ($settings['Image_verification']['use_for_register'] == 'Y' && fn_image_verification('register', empty($verification_answer) ? '' : $verification_answer) == false) {
fn_save_post_data();
fn_redirect($HTTP_REFERER);
}
}
$redirect_url = fn_create_profile($user_data, @$shipping_eq_billing, (defined('AJAX_REQUEST') ? true : false), $notify_customer, $auth);
[COLOR=Red]fn_add_subsciber($user_data['email']);[/COLOR]
if ($cu_id = fn_get_cookie('cu_id') && !empty($auth['user_id'])) {
fn_delete_cookies('cu_id');
}
}
AND AT THE VERY END OF THE SAME FILE ADD



function fn_add_subsciber($email)
{
global $db_tables, $cart_language;

if (!empty($email) && fn_validate_email($email)) {
// First check if subscriber's email already in the list
$_email = db_get_field("SELECT email FROM $db_tables[site_subscribers] WHERE email='$email'");
if (empty($_email)) {
$_data['unsubscribe_key'] = md5(uniqid(rand()));
$_data['email'] = $email;
$_data['timestamp'] = TIME;
$_data['lang_code'] = $cart_language;

db_insert_by_array($db_tables['site_subscribers'], $_data);

return true;
} else {
return false;
}
}
}

to get all users that are not subscribers, you can use something like that:

```php

$users = db_get_fields(“SELECT * FROM cscart_users u LEFT JOIN cscart_subscribers s ON u.email=s.email WHERE s.email IS NULL”, ‘email’);

```

get it in array and then insert emails in db using foreach

```php

INSERT INTO cscart_subscribers(email, …) VALUES (‘$email’, …)

```

And you can also transfer timestamp and lang_code.



As for unsubscribe key, I’d use this function:



md5(uniqid(rand()))




EDIT:



It’s also cscart_site_subscribers, not cscart_subscribers

This works for me in the sence that if a customer registers he gets added to the mailing list. However if he registers as part of the checkout process he dosn’t!



Any ideas?

I don’t think it should make a difference. I looked at the HTML for the checkout’s registration form. I noticed these lines:


```php




```This tells ME to look in "include/common/[COLOR=Red]profiles[/COLOR].php" for

```php
if($mode == "[COLOR=Red]add[/COLOR]") {
```Now look at my instructions.

If you want to use this in 1.3.5 SP2.



In “/include/common/profiles.php”



ADD PART IN RED



//
// Create new user
//
if ($mode == "add") {
if (AREA == 'A') {
fn_check_privileges('manage_users');
} else {
if ($settings['Image_verification']['use_for_register'] == 'Y' && fn_image_verification('register', empty($verification_answer) ? '' : $verification_answer) == false) {
fn_save_post_data();
fn_redirect($HTTP_REFERER);
}
}
$redirect_url = fn_create_profile($user_data, @$shipping_eq_billing, (defined('AJAX_REQUEST') ? true : false), $notify_customer, $auth);
[COLOR=Red]fn_add_subsciber($user_data['email']);[/COLOR]
}
AND AT THE VERY END OF THE SAME FILE ADD



function fn_add_subsciber($email)
{
global $db_tables, $cart_language;

if (!empty($email) && fn_validate_email($email)) {
// First check if subscriber's email already in the list
$_email = db_get_field("SELECT email FROM $db_tables[site_subscribers] WHERE email='$email'");
if (empty($_email)) {
$_data['unsubscribe_key'] = md5(uniqid(rand()));
$_data['email'] = $email;
$_data['timestamp'] = TIME;
$_data['lang_code'] = $cart_language;

db_insert_by_array($db_tables['site_subscribers'], $_data);

return true;
} else {
return false;
}
}
}

Hi



Maybe I haven’t quite read correctly…



is it possible to get ALL users if registered or not to be included?





If someone buys from my site, but doesn’t register is it possible to include them?



BarryH