1. 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

Discussion in 'XenForo Questions and Support' started by snoopy5, Sep 5, 2013.

  1. snoopy5

    snoopy5 Well-Known Member


    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
  2. Chris D

    Chris D XenForo Developer Staff Member

    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')
  3. snoopy5

    snoopy5 Well-Known Member

    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?
  4. Chris D

    Chris D XenForo Developer Staff Member

    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.
    snoopy5 likes this.

Share This Page