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.

[code lang=”sql”] 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[/code]

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.