Enabling 1 click opt out for existing users after upgrading to InstantForum 2016


With InstantForum 2016 we introduced the ability for users to opt -out of both forum and topic notifications by clicking a unique link within each of these notifications. Previously this required users to have to login to opt-out of notifications which didn't offer a great experience.. 

This works great and provides an better experience for the end user however by default the new 1 click opt out will only work if users subscribe to forums or topics after you've updated to InstantForum 2016. This won't be enabled by default for existing subscriptions. 

To enable 1 click opt-out for all existing users within your community who have subscribed to forums or topics before you upgraded to 2016 you can use the SQL script below. This should be ran against your upgraded InstantForum 2016 or above database to enable 1 click out-out for all existing subscriptions. 

The script is very simple and will traverse through your InstantForum_ForumSubscriptions table and InstantForum_TopicSubscriptions table row by row adding a random GUID to the new SubscriptionGuid column within each of these tables. If a GUID is present for the subscription within the SubscriptionGUID column the out-opt link within the subscription email will use the GUID to allow the user to opt-out of further notifications without the need to login. 

I hope some of you may find this useful. 

Note: This script has only been tested with SQL Server 2012 / 2014 and will only work once you've upgraded your database to InstantForum 2016 or above. If your using SQL Server 2008 we would strongly suggest you first test this script within a development environment before running in production. . 

-- add a random GUID to forum subscriptions table ensuring 
-- support for one click out-out of forum subscription emails
DECLARE @intForumSubscriptionID int
DECLARE MSGCURSOR CURSOR FOR
SELECT ForumSubscriptionID FROM InstantForum_ForumSubscriptions

OPEN MSGCURSOR

FETCH NEXT FROM MSGCURSOR
INTO @intForumSubscriptionID

WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE @strSubscriptionGuid nvarchar(100)
SEt @strSubscriptionGuid = CONVERT(varchar(100), NEWID())

UPDATE InstantForum_ForumSubscriptions SET
SubscriptionGuid = @strSubscriptionGuid
WHERE ForumSubscriptionID = @intForumSubscriptionID

FETCH NEXT FROM MSGCURSOR
INTO @intForumSubscriptionID

END
-- tidy cursor
CLOSE MSGCURSOR
DEALLOCATE MSGCURSOR

GO


-- add a random GUID to forum subscriptions table ensuring
-- support for one click out-out of forum subscription emails
DECLARE @intTopicSubscriptionID int
DECLARE MSGCURSOR CURSOR FOR
SELECT TopicSubscriptionID FROM InstantForum_TopicSubscriptions

OPEN MSGCURSOR

FETCH NEXT FROM MSGCURSOR
INTO @intTopicSubscriptionID

WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE @strSubscriptionGuid nvarchar(100)
SEt @strSubscriptionGuid = CONVERT(varchar(100), NEWID())

UPDATE InstantForum_TopicSubscriptions SET
SubscriptionGuid = @strSubscriptionGuid
WHERE TopicSubscriptionID = @intTopicSubscriptionID

FETCH NEXT FROM MSGCURSOR
INTO @intTopicSubscriptionID

END
-- tidy cursor
CLOSE MSGCURSOR
DEALLOCATE MSGCURSOR

GO


If I can assist with any questions of course please don't hesitate to respond, 

http://www.instantasp.co.uk/images/line.gif
Kindest Regards,

Ryan Healey



ryan@instantasp.co.uk
www.instantasp.co.uk
Blog | Community | Docs

http://www.instantasp.co.uk/core/assets/images/email/facebook.png http://www.instantasp.co.uk/core/assets/images/email/twitter.png http://www.instantasp.co.uk/core/assets/images/email/google.png http://www.instantasp.co.uk/core/assets/images/email/linkedin.png
General
GO

Login
Existing Account
Email Address:


Password:


Social Logins

Select a Forum....








InstantASP Forums


Search