How to Stop Email Notifications for Non-active Members

[warmweer]

What are "back ticks"?

a punctuation mark, also know as front quote or left quote or open quote
` <---

[P_I]

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.

[Jobertim]

Great! Thanks for your replies, and thank you all for helping with this SQL query.

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