eva2000
Well-known member
Updated the Xenforo part of script to also allow outputting MySQL SQL queries ready for batch importing into XF database too https://github.com/centminmod/validate-emails#xenforo
Use jq tool to filter for xf_sql_batch only. You can pipe or place this output into a update.sql file and import into your Xenforo MySQL database to batch update the user's user_state
Code:
python validate_emails.py -f user@domain1.com -l emaillist.txt -tm all -xf -xfdb xenforo -xfprefix xf_ | jq -r '.[] | select(.xf_sql_batch) | .xf_sql_batch'
UPDATE xf_user SET user_state = 'email_bounce' WHERE email = 'user@mailsac.com';
UPDATE xf_user SET user_state = 'email_bounce' WHERE email = 'xyz@domain1.com';
UPDATE xf_user SET user_state = 'email_bounce' WHERE email = 'abc@domain1.com';
UPDATE xf_user SET user_state = 'email_bounce' WHERE email = '123@domain1.com';
UPDATE xf_user SET user_state = 'email_bounce' WHERE email = 'pop@domain1.com';
UPDATE xf_user SET user_state = 'email_bounce' WHERE email = 'pip@domain1.com';
UPDATE xf_user SET user_state = 'email_bounce' WHERE email = 'user@tempr.email';
UPDATE xf_user SET user_state = 'email_bounce' WHERE email = 'op999@gmail.com';Tried this on 5 Xenforo forums software and all good - roughly between 5-15% of the member emails are bad emails so batch imported an SQL file with these to stop emailing those users
Use jq tool to filter for newly added xf_sql_user only. This allows you to run on SSH command line the Xenforo database lookup for the Xenforo user details for the specific email address
Code:
python validate_emails.py -f user@domain1.com -l emaillist.txt -tm all -xf -xfdb xenforo -xfprefix xf_ | jq -r '.[] | select(.xf_sql_user) | .xf_sql_user'
mysql -e "SELECT user_id, username, email, user_group_id, secondary_group_ids, message_count, register_date, last_activity, user_state, is_moderator, is_admin, is_banned FROM xf_user WHERE email = 'user@mailsac.com'\G" xenforo
mysql -e "SELECT user_id, username, email, user_group_id, secondary_group_ids, message_count, register_date, last_activity, user_state, is_moderator, is_admin, is_banned FROM xf_user WHERE email = 'xyz@domain1.com'\G" xenforo
mysql -e "SELECT user_id, username, email, user_group_id, secondary_group_ids, message_count, register_date, last_activity, user_state, is_moderator, is_admin, is_banned FROM xf_user WHERE email = 'abc@domain1.com'\G" xenforo
mysql -e "SELECT user_id, username, email, user_group_id, secondary_group_ids, message_count, register_date, last_activity, user_state, is_moderator, is_admin, is_banned FROM xf_user WHERE email = '123@domain1.com'\G" xenforo
mysql -e "SELECT user_id, username, email, user_group_id, secondary_group_ids, message_count, register_date, last_activity, user_state, is_moderator, is_admin, is_banned FROM xf_user WHERE email = 'pop@domain1.com'\G" xenforo
mysql -e "SELECT user_id, username, email, user_group_id, secondary_group_ids, message_count, register_date, last_activity, user_state, is_moderator, is_admin, is_banned FROM xf_user WHERE email = 'pip@domain1.com'\G" xenforo
mysql -e "SELECT user_id, username, email, user_group_id, secondary_group_ids, message_count, register_date, last_activity, user_state, is_moderator, is_admin, is_banned FROM xf_user WHERE email = 'user@tempr.email'\G" xenforo
mysql -e "SELECT user_id, username, email, user_group_id, secondary_group_ids, message_count, register_date, last_activity, user_state, is_moderator, is_admin, is_banned FROM xf_user WHERE email = 'op999@gmail.com'\G" xenforoJust in case you're curious about the user details attached to the email address you're about to update for
Code:
mysql -e "SELECT user_id, username, email, user_group_id, secondary_group_ids, message_count, register_date, last_activity, user_state, is_moderator, is_admin, is_banned FROM xf_user WHERE email = 'pop@domain1.com'\G" xenforo
*************************** 1. row ***************************
user_id: 11
username: pop
email: pop@domain1.com
user_group_id: 2
secondary_group_ids: 3,4,6,8
message_count: 191817
register_date: 1400868747
last_activity: 1715011284
user_state: email_bounce
is_moderator: 1
is_admin: 1
is_banned: 0
That's magnificent work, thanks for sharing it with us!
