Reset phpbb_user_notifications

Get help with installation and running phpBB 3.2.x here. Please do not post bug reports, feature requests, or extension related questions here.
Post Reply
Timorad
Registered User
Posts: 7
Joined: Wed Aug 02, 2017 6:20 pm

Reset phpbb_user_notifications

Post by Timorad » Tue Jan 23, 2018 10:48 am

After a migration from an older version of phpbb I noticed that the user notification email settings are empty for all migrated users. It seems the phpbb_user_notifications table is pretty much empty. For new users, the settings are entered correctly in the table, as are the settings for users that pro-actively change their settings in the UCP.

As I want the migrated users to get email notifications as well per default, I wonder if there's a convenient way to "repopulate" or reset to defauls the phph_user_notifications table for all users.

Is there an SQL command that loops through all users ID's, and generates default notification setting for each user? i.e. each user should have an entry like this in phph_user_notifications.

Code: Select all

item_type	             item_id         user_id      method                        notify
notification.type.topic      0               user_id      notification.method.email         1
notification.type.post       0               user_id      notification.method.email         1
Some help or pointers are greatly appreciated :)

User avatar
stevemaury
Support Team Member
Support Team Member
Posts: 48399
Joined: Thu Nov 02, 2006 12:21 am
Location: The U.P.
Name: Steve
Contact:

Re: Reset phpbb_user_notifications

Post by stevemaury » Tue Jan 23, 2018 3:52 pm

Backup the user_notifications table. Then run this SQL:

Code: Select all

INSERT INTO phpbb_user_notifications (item_type, item_id, user_id, method, notify) SELECT 'notification.type.topic', 0, user_id, 'notification.method.email', 1 FROM phpbb_users WHERE user_type <> 2 AND user_id NOT IN(SELECT user_id FROM phpbb_user_notifications);
INSERT INTO phpbb_user_notifications (item_type, item_id, user_id, method, notify) SELECT 'notification.type.post', 0, user_id, 'notification.method.email', 1 FROM phpbb_users WHERE user_type <> 2 AND user_id NOT IN(SELECT user_id FROM phpbb_user_notifications)
Change the table prefix if yours is not phpbb_
For REALLY good and VERY inexpensive hosting CLICK HERE

All unsolicited PMs will be ignored.

Timorad
Registered User
Posts: 7
Joined: Wed Aug 02, 2017 6:20 pm

Re: Reset phpbb_user_notifications

Post by Timorad » Tue Jan 23, 2018 8:56 pm

Thanks, that works great for the first query ;)

However, as the first query already generates new users_id in the phph_user_notification table, the second query "NOT IN" condition is always true and therefore doesn't work. I've tried googling on combining INSERTS, but so far I've have managed to forge an actual working SQL.

janus_zonstraal
Registered User
Posts: 2067
Joined: Sat Aug 30, 2014 1:30 pm

Re: Reset phpbb_user_notifications

Post by janus_zonstraal » Tue Jan 23, 2018 9:04 pm

Sorry! My English is bat ;) !!!

User avatar
stevemaury
Support Team Member
Support Team Member
Posts: 48399
Joined: Thu Nov 02, 2006 12:21 am
Location: The U.P.
Name: Steve
Contact:

Re: Reset phpbb_user_notifications

Post by stevemaury » Fri Jan 26, 2018 4:51 pm

Try this for the second query: As always, backup the user_notifications table first.

Code: Select all

INSERT INTO phpbb_user_notifications (item_type, item_id, user_id, method, notify) SELECT 'notification.type.post', 0, user_id, 'notification.method.email', 1 FROM phpbb_users WHERE user_type <> 2 AND user_id NOT IN(SELECT user_id FROM phpbb_user_notifications WHERE method = 'notification.type.post' AND 'notification.method.email' AND notify = 1)
That should exclude users who have already selected to be notified of posts by email.
For REALLY good and VERY inexpensive hosting CLICK HERE

All unsolicited PMs will be ignored.

Timorad
Registered User
Posts: 7
Joined: Wed Aug 02, 2017 6:20 pm

Re: Reset phpbb_user_notifications

Post by Timorad » Tue Jan 30, 2018 1:16 pm

stevemaury wrote:
Fri Jan 26, 2018 4:51 pm
Try this for the second query:
Thanks! This worked perfectly. I did get an error: "Duplicate key name 'itm_usr_mthd' ", but after clearing the index described as here all went smoothly.

Post Reply

Return to “[3.2.x] Support Forum”

Who is online

Users browsing this forum: Baidu [Spider], janus_zonstraal, KYPREO, sealilly, Serenity Joe, twm49, uygar2580 and 32 guests