One small forum running on phpBB needed email subscriptions on all forums applied to all members. While it is easy to find a mod adding this feature, it is much more complicated to install a modifications to phpBB rather than vBulletin. Today I’m sharing SQL query accomplishing subscription task on database side.
insert into prefix_forums_watch select distinct f.forum_id, u.user_id, 0 from prefix_forums f, prefix_users u where not exists (select 1 from prefix_forums_watch where forum_id = f.forum_id and user_id = u.user_id) and u.user_type = 0
Instead of prefix correct value set during installation has to be entered. Default is phpbb.
Forum subscriptions are stored on forums_watch table which consists of three fields. Only two are important – forum identifier forum_id and user identifier user_id. Forums and users are stored on forums and users tables respectively.
I found this query at Stack Overflow and made one modification – added condition user_type = 0 which enables subscriptions only for regular registered users. Search bots are stored on the same table but there is no need to enable subscriptions for them.