How to Stop Email Notifications for Non-active Members

Get help with installation and running phpBB 3.2.x here. Please do not post bug reports, feature requests, or extension related questions here.
User avatar
warmweer
Jr. Extension Validator
Posts: 6544
Joined: Fri Jul 04, 2003 6:34 am
Location: Van Allen Bel ... gium

Re: How to Stop Email Notifications for Non-active Members

Post by warmweer »

Jobertim wrote:
Mon Feb 22, 2021 7:40 pm
What are "back ticks"? :?:
a punctuation mark, also know as front quote or left quote or open quote
` <---
Spelling is freeware, which means you can use it for free.
On the other hand, it is not open source, which means you cannot change it or publish it in a modified form.
User avatar
P_I
Registered User
Posts: 1501
Joined: Tue Mar 01, 2011 8:35 pm
Location: Staying home - Western Canada
Contact:

Re: How to Stop Email Notifications for Non-active Members

Post by P_I »

stevemaury wrote:
Mon Feb 22, 2021 6:50 pm
gpraceman wrote:
Sun Feb 23, 2020 7:48 am

Code: Select all

UPDATE `phpbb3_user_notifications` SET `notify`=0 
WHERE `method`="notification.method.email" AND `user_id` IN(
SELECT `user_id` FROM `phpbb3_users` WHERE `user_lastvisit` > 0 AND`user_lastvisit` < 1487884864
)
Here's the down and dirty query that I came up with. Anyone that hasn't been active in the board for more than 3 years had their notifications removed.

In my case that was a whopping 6,045 records affected.
You don't need the back ticks, and I would add AND user_type <> 2 to exclude Anonymous and bots.
And don't hard-code the timestamp, change the last line to

Code: Select all

SELECT `user_id` FROM `phpbb3_users` WHERE `user_lastvisit` > 0 AND`user_lastvisit` < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 3 YEAR))
Note, this SQL assumes the $table_prefix is phpbb3.
Normal people… believe that if it ain’t broke, don’t fix it. Engineers believe that if it ain’t broke, it doesn’t have enough features yet. – Scott Adams
Jobertim
Registered User
Posts: 270
Joined: Wed Dec 28, 2011 2:25 pm

Re: How to Stop Email Notifications for Non-active Members

Post by Jobertim »

Great! Thanks for your replies, and thank you all for helping with this SQL query. :D
User avatar
P_I
Registered User
Posts: 1501
Joined: Tue Mar 01, 2011 8:35 pm
Location: Staying home - Western Canada
Contact:

Re: How to Stop Email Notifications for Non-active Members

Post by P_I »

Thinking out loud based on my board membership. For a variety of reasons I'm aware that some of my members don't login when they are reading the forum, they only login to post.

Thus their last visit information doesn't necessarily give an accurate view of their activity. If I were implementing something like this I would definitely definite a longish time period such as the three (3) years used in the examples when determining when a member was last active.
Normal people… believe that if it ain’t broke, don’t fix it. Engineers believe that if it ain’t broke, it doesn’t have enough features yet. – Scott Adams
Post Reply

Return to “[3.2.x] Support Forum”