• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

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

snoopy5

Well-known member
#1
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:

Code:
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

XenForo developer
Staff member
#2
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:

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

snoopy5

Well-known member
#3
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:

Code:
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

XenForo developer
Staff member
#4
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:

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