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

XF 1.5 Moving away from Mandrill - Blacklist/unsubscribed list managmente

Discussion in 'XenForo Questions and Support' started by Breixo, Apr 19, 2016.

  1. Breixo

    Breixo Active Member

    Hi! I'm not sure if this is the right place to ask or at the PHP/Mysql subforum (please move if necessary).
    As most know, Mandrill is changing and I'm no longer want to use it.
    The problem: there was an option to automatically unsubscribe from getting emails. This blacklist is on Mandrillapp, not at Xenforo.
    So if I turn now to another provider (or just default Xenforo system), those users who asked for subscription will get emails from me again.

    I was able to export the blacklist. So now I have around 14k emails of registered users who don't want to get emails from me (this include invalid emails and unsubscribed accounts).

    So I looked for a way to remove these emails from getting emails from Xenforo.
    This includes:
    a) Turn all these users to 0 at receive_admin_email; email_on_conversation; default_watch_state at xf_user_option table
    b) Turn all these users to 0 at email_subscribe at xf_thread_watch table.
    c) Turn all these users to 0 at send_email at xf_forum_watch table.
    d) Don't know if any other columns should be changed or if just with one of them is enough.

    I found some queries by @Jake Bunce (here and here):
    Code:
    UPDATE xf_user_option
    SET default_watch_state = 'watch_no_email'
    WHERE default_watch_state = ''
    Code:
    UPDATE xf_thread_watch
    SET email_subscribe = 0;
    and I guess
    Code:
    UPDATE xf_forum_watch
    SET send_email = 0;
    But this is for ALL users, I just want to change it only for the unsubscribed/blacklist.

    I know that an option like "WHERE user_id IN (123,456,789)" can be used. But I hope there's a smarter way to unsubscribe them. An automatic way to get user ID based on email account?

    I also checked a plugin like this: https://xenforo.com/community/resources/e-mail-address-restrictions.5001/
    But probably most of these users just want to avoid getting new emails but still want to participate in the forum.

    Thanks for your help!

    PS: hope I explained myself correctly... just don't want to send emails to these people who asked for getting no emails.
     
  2. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    Example:

    Code:
    UPDATE xf_thread_watch
    SET email_subscribe = 0
    WHERE user_id IN (
    	SELECT user_id
    	FROM xf_user
    	WHERE email LIKE '%@domain.com'
    )
    
     
    Breixo likes this.
  3. Breixo

    Breixo Active Member

    Great, looks that can solve the problem.
    Instead of one by one, can I use:
    WHERE email LIKE '(name1@domain.com,email2@gmail.com,email3@hotmail.com)'
    Maybe up to 50 emails per query?
    Thanks Jake!
     
  4. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    If you have a list of specific email addresses then it would be like this:

    Code:
    UPDATE xf_thread_watch
    SET email_subscribe = 0
    WHERE user_id IN (
    	SELECT user_id
    	FROM xf_user
    	WHERE email IN ('name1@domain.com','email2@gmail.com','email3@hotmail.com')
    )
    
     
    Breixo likes this.
  5. Breixo

    Breixo Active Member

    Great, thanks a lot Jake :)
     
  6. Breixo

    Breixo Active Member

    I think everything worked smoothly:
    1. Downloaded blacklist from Mandrillapp
    2. Used this formula with LibreOffice to give the correct format: =CONCATENATE("'",A1,"' ,")
      So all emails have this format: 'name1@domain.com','email2@gmail.com','email3@hotmail.com' (ready for queries).
    3. Test in devel
    4. Backup database!
    5. Applied this to queries inserting all emails between ():
    Code:
    UPDATE xf_user_option
    SET receive_admin_email = 0
    WHERE user_id IN (
        SELECT user_id
        FROM xf_user
        WHERE email IN ('name1@domain.com','email2@gmail.com','email3@hotmail.com')
    )
    
    Code:
    UPDATE xf_thread_watch
    SET email_subscribe = 0
    WHERE user_id IN (
        SELECT user_id
        FROM xf_user
        WHERE email IN ('name1@domain.com','email2@gmail.com','email3@hotmail.com')
    )
    
    I just tested it and everything looks normal.
    Thanks a lot Jake!
     
    a.tushkanov likes this.

Share This Page