XF 1.2 Query to change userstate and privacy settings for specific email addresses

[snoopy5]

Hi

I have generated a list of around 300 email addresses, which I want to change from "valid" userstate into "awaiting email confirmation (from edit)" userstate.

At the same time, I want to change for the same email adresses the setting for getting admin email to "no", same for email notifications of new postings and email notifications for conversations/PMs

I tried this in the xf_user table in the SQL tab:

UPDATE xf_user AS xfuser
INNER JOIN xf_user_option AS user_option
    ON (xfuser.user_id = user_option.user_id)
INNER JOIN xf_thread_watch AS thread_watch
    ON (xfuser.user_id = thread_watch.user_id)
SET xfuser.user_state = 'email_confirm_edit',
        user_option.receive_admin_email = 0,
        user_option.email_on_conversation = 0,
        thread_watch.email_subscribe = 0
WHERE email IN ('EMAIL1', 'EMAIL2', 'EMAIL3')

But this does not work. It sais that no data are affected by this. Which is not true. So how do i have to write that sql code to achieve this?

Thanks in advance

 

[Chris D]

Most of what you're trying to do is superfluous.

If their account is not valid, XenForo won't attempt to e-mail them anyway.

With that in mind, it's perfectly safe for you to simply run this query:

UPDATE xf_user
SET user_state = 'email_confirm_edit'
WHERE email IN ('email', 'email1', 'email2')

 

[snoopy5]

Most of what you're trying to do is superfluous.

If their account is not valid, XenForo won't attempt to e-mail them anyway.

With that in mind, it's perfectly safe for you to simply run this query:

UPDATE xf_user
SET user_state = 'email_confirm_edit'
WHERE email IN ('email', 'email1', 'email2')

Thanks, I will try that code.

Are you sure that if a user has a userstate of "email_confirm_edit" and in hs privacy settings Receive site mailings turned "on", that he is not getting any emails anymore?

 

[Chris D]

Yeah. I haven't checked every single case... but in the example of Thread Watching (and I presume Forum Watching), this line of code is just before the e-mails are sent out:

if ($user['email_subscribe'] && $user['email'] && $user['user_state'] == 'valid')

They have to be subscribed to e-mails, have an e-mail address and have a user_state of 'valid'.

If one of those isn't true (such as if their state is email_confirm_edit), they will not get an e-mail.