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

Breixo

Well-known 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.
 
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!
 
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!

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')
)
 
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!
 
Top Bottom