'Notify me upon replies by default' set to YES by default?

Get help with installation and running phpBB 3.0.x here. Please do not post bug reports, feature requests, or MOD-related questions here.
Scam Warning
Forum rules
END OF SUPPORT: 1 January 2017 (announcement)
Pony99CA
Registered User
Posts: 4783
Joined: Thu Sep 30, 2004 3:13 pm
Location: Hollister, CA
Name: Steve
Contact:

Re: 'Notify me upon replies by default' set to YES by defaul

Post by Pony99CA »

jelo77 wrote:
No. Once again, the topics_watch table stores topic subscription information. Why is this hard to believe? You need to track each topic that a user has subscribed to, which requires storing both a user ID and a topic ID. Look at the definition of topics_watch.
I did look at the table. My board has 2884 posts in 447 topics. In the table topic_watch I have 395 entries, most of them have a notify_status=0. I was under the impression that each user that posts will get an entry with the correct notification status of 0 or 1 for that user id and topic id, but apparently that is not the case. I will check if I can run an SQL command to select all users in one topic to then set the notification for those users only for each of my topics.
I looked at the code a while back, so I'll try to explain my recollection of how things work (although I may have some details wrong).

When somebody subscribes to a topic, that topic and user ID get inserted into the topics_watch table with notify_stat indicating that the person should be notified when a new post is made. When somebody posts to that topic, the notify_stat value is changed to indicate that no more notifications should be sent until the user visits again. When the user does visit, notify_stat is reset to indicate that the person should be notified again when a new post is made. Notifications are only sent to people in that state. If the user unsubscribes from a topic, his entry for that topic is deleted from topics_watch.

As I said, the details may be off a bit, but that's the gist. That's why a simple UPDATE query won't work; if people posted in a topic but aren't subscribed, you need to insert those people/topic pairs into topics_watch.

Steve
Silicon Valley Pocket PC (http://www.svpocketpc.com)
Creator of manage_bots and spoof_user (ask me)
Need hosting for a small forum with full cPanel & MySQL access? Contact me or PM me.
User avatar
postcd
Registered User
Posts: 93
Joined: Sun Oct 14, 2007 3:42 pm
Contact:

Re: 'Notify me upon replies by default' set to YES by defaul

Post by postcd »

Nice, thanks for solution.

What if i want to set email notifications to all users to all they replies they already made? What SQL Query should i use?
I have Health Forum and also webmaster Hosting Forum. Interested in making money? Try money maker forum.
Pony99CA
Registered User
Posts: 4783
Joined: Thu Sep 30, 2004 3:13 pm
Location: Hollister, CA
Name: Steve
Contact:

Re: 'Notify me upon replies by default' set to YES by defaul

Post by Pony99CA »

postcd wrote: What if i want to set email notifications to all users to all they replies they already made? What SQL Query should i use?
That's basically what jelo77 wanted to do, isn't it?

Anyway, I decided to investigate this some more and came up with the following query (if your table prefix isn't phpbb_, update the query with your table prefix):

Code: Select all

INSERT INTO phpbb_topics_watch(topic_id, user_id, notify_status)
SELECT tp.topic_id, u.user_id, 0 
FROM phpbb_topics_posted tp, phpbb_users u
WHERE u.user_id = tp.user_id
  AND u.user_type <> 2
  AND tp.topic_posted = 1
  AND NOT EXISTS (
     SELECT tw.topic_id, tw.user_id, 0
     FROM phpbb_topics_watch tw
     WHERE tw.topic_id = tp.topic_id
       AND tw.user_id = tp.user_id
     )
;
Basically, it selects all topic_id/user_id pairs from the topics_posted table that aren't from guests and aren't already in the topics_watch table and inserts those rows into the topics_watch table with notify_status set to zero (meaning the user will get notified on the next post).

One thing that I'm not sure of is if the AND tp.topic_posted = 1 is needed, because I don't know what a value of zero means there.

NOTE: I tested this on my test board, but not extensively. Make sure that you back up your database before running any SQL query that changes the database like this.

Steve
Last edited by Pony99CA on Sun Jul 29, 2012 9:19 pm, edited 1 time in total.
Silicon Valley Pocket PC (http://www.svpocketpc.com)
Creator of manage_bots and spoof_user (ask me)
Need hosting for a small forum with full cPanel & MySQL access? Contact me or PM me.
Kyza
Registered User
Posts: 90
Joined: Fri Jun 25, 2010 12:18 pm
Contact:

Re: 'Notify me upon replies by default' set to YES by defaul

Post by Kyza »

I was wondering, where does it show up like does it have like:

Alerts: 1

if not how can i do it?
Image
User avatar
Oyabun1
Former Team Member
Posts: 23162
Joined: Sun May 17, 2009 1:05 pm
Location: Australia
Name: Bill

Re: 'Notify me upon replies by default' set to YES by defaul

Post by Oyabun1 »

Kyza wrote:I was wondering, where does it show up like does it have like:

Alerts: 1

if not how can i do it?
What are you talking about, alerts of what? This whole topic is about the settings for receiving email notifications of posts.
                      Support Request Template
3.0.x: Knowledge Base Styles Support MOD Requests
3.1.x: Knowledge BaseStyles SupportExtension Requests
rekabis
Registered User
Posts: 8
Joined: Sat Nov 10, 2007 7:50 am
Location: Kelowna, British Columbia, Canada
Contact:

Re: 'Notify me upon replies by default' set to YES by defaul

Post by rekabis »

Pony99CA wrote:
dhecker wrote:That will work, but wouldn't it ignore the user's setting in their profile?
I would like them to be able to turn off the feature if they want.
You're referring to the UCP option, not the posting page option, right?

If so, try this in \includes\functions_user.php:

Find:

Code: Select all

'user_notify'			=> 0,
Replace with:

Code: Select all

'user_notify'			=> 1,
That will make reply notification the default for new users.

If you want to make reply notification the default for existing users, run the following SQL query:

Code: Select all

UPDATE phpbb_users 
SET user_notify = 1
WHERE user_type <> 2;
Of course, you should back up your database before running an UPDATE query.

Steve
I would also go so far as to set the default value of the user_type field to 1, because the default in the database is 0.

The actual SQL statement above only changed existing entries. It did not change the default value.

Granted, user_notify is supposed to set user_type to 1 with new registrations, but I like to have all my bases covered.
User avatar
HGN
Former Team Member
Posts: 4706
Joined: Wed Dec 03, 2008 1:53 pm
Location: The Netherlands
Name: Alfred
Contact:

Re: 'Notify me upon replies by default' set to YES by defaul

Post by HGN »

What are you saying?
rekabis wrote:I would also go so far as to set the default value of the user_type field to 1, because the default in the database is 0.
What has the user_type to do with the question in this topic?
rekabis wrote:The actual SQL statement above only changed existing entries. It did not change the default value.
It is stated that the SQL query is to change the existing users.
To change the default for new users, the line with user_notify has to be modified in /includes/functions_user.php.
rekabis wrote:Granted, user_notify is supposed to set user_type to 1 with new registrations, but I like to have all my bases covered.
user_notify really has nothing to do with the user_type. The only reason to exclude user_type 2 in the query is that user_type 2 are the bots and bots have no e-mail address. ;)

Whether a user becomes user_type 0 or 1 at registration depends on the User registration settings in the ACP. If Account activation: is set to "No activation" a user will become user_type 0 (being a normal user). If it is set to any other, the user will be user_type 1 (being an inactive user).
therainbowrepublic
Registered User
Posts: 15
Joined: Wed Oct 08, 2014 1:17 am

Re: 'Notify me upon replies by default' set to YES by defaul

Post by therainbowrepublic »

Pony99CA wrote:
jelo77 wrote:
No. Once again, the topics_watch table stores topic subscription information. Why is this hard to believe? You need to track each topic that a user has subscribed to, which requires storing both a user ID and a topic ID. Look at the definition of topics_watch.
I did look at the table. My board has 2884 posts in 447 topics. In the table topic_watch I have 395 entries, most of them have a notify_status=0. I was under the impression that each user that posts will get an entry with the correct notification status of 0 or 1 for that user id and topic id, but apparently that is not the case. I will check if I can run an SQL command to select all users in one topic to then set the notification for those users only for each of my topics.
I looked at the code a while back, so I'll try to explain my recollection of how things work (although I may have some details wrong).

When somebody subscribes to a topic, that topic and user ID get inserted into the topics_watch table with notify_stat indicating that the person should be notified when a new post is made. When somebody posts to that topic, the notify_stat value is changed to indicate that no more notifications should be sent until the user visits again. When the user does visit, notify_stat is reset to indicate that the person should be notified again when a new post is made. Notifications are only sent to people in that state. If the user unsubscribes from a topic, his entry for that topic is deleted from topics_watch.

As I said, the details may be off a bit, but that's the gist. That's why a simple UPDATE query won't work; if people posted in a topic but aren't subscribed, you need to insert those people/topic pairs into topics_watch.

Steve
FINALLY! I've been scouring the Internet for a solution to this problem. "Notify me on replies" only sends an email to the user on new posts, it DOES NOT subscribe them to the topic!!

And I can't find any MOD that can do this =/

This is supposed to be standard forum behavior: You post in a thread, and you are automatically subscribed to the thread. Because NO ONE ever remembers to subscribe! It's supposed to be automatic. You don't have to receive notifications when replies are made (so it is NOT spamming), but when you go to your subscribed topics, you should see the threads you posted in. That's what keeps users coming back to reply to one another in a thread. Ughh so frustrated.

Maybe I can make a MOD request for something that will create a record in the phpbb_topics_watch table whenever a user makes a post in a thread (if it doesn't already exists, of course). The notify flag would be 0, so the user doesn't receive emails for every thread they post in.

Edit: I was wrong, "Notify me on replies" does in fact subscribe you to topics, but only on NEW posts. If you want this to be default behavior, you must change it in functions.php. To change the default setting for all existing users, you must run a query. To subscribe existing users to past topics, run Pony99CA's query.
Last edited by therainbowrepublic on Thu Oct 09, 2014 9:33 am, edited 1 time in total.
therainbowrepublic
Registered User
Posts: 15
Joined: Wed Oct 08, 2014 1:17 am

Re: 'Notify me upon replies by default' set to YES by defaul

Post by therainbowrepublic »

Pony99CA wrote:
postcd wrote: What if i want to set email notifications to all users to all they replies they already made? What SQL Query should i use?
That's basically what jelo77 wanted to do, isn't it?

Anyway, I decided to investigate this some more and came up with the following query (if your table prefix isn't phpbb_, update the query with your table prefix):

Code: Select all

INSERT INTO phpbb_topics_watch(topic_id, user_id, notify_status)
SELECT tp.topic_id, u.user_id, 0 
FROM phpbb_topics_posted tp, phpbb_users u
WHERE u.user_id = tp.user_id
  AND u.user_type <> 2
  AND tp.topic_posted = 1
  AND NOT EXISTS (
     SELECT tw.topic_id, tw.user_id, 0
     FROM phpbb_topics_watch tw
     WHERE tw.topic_id = tp.topic_id
       AND tw.user_id = tp.user_id
     )
;
Basically, it selects all topic_id/user_id pairs from the topics_posted table that aren't from guests and aren't already in the topics_watch table and inserts those rows into the topics_watch table with notify_status set to zero (meaning the user will get notified on the next post).

One thing that I'm not sure of is if the AND tp.topic_posted = 1 is needed, because I don't know what a value of zero means there.

NOTE: I tested this on my test board, but not extensively. Make sure that you back up your database before running any SQL query that changes the database like this.

Steve
This is perfect. I'm going to run this periodically until I can get someone to build a mod, or figure out how to do it myself.
therainbowrepublic
Registered User
Posts: 15
Joined: Wed Oct 08, 2014 1:17 am

Re: 'Notify me upon replies by default' set to YES by defaul

Post by therainbowrepublic »

Oh, praise heaven.

I did it. I created a MySQL trigger on the phpbb_topics_posted table so whenever there is an INSERT, it runs @Pony99CA's code

Code: Select all

CREATE TRIGGER topic_watch_when_posted
AFTER INSERT ON phpbb_topics_posted FOR EACH ROW
    INSERT INTO phpbb_topics_watch(topic_id, user_id, notify_status)
        SELECT tp.topic_id, u.user_id, 0 
        FROM phpbb_topics_posted tp, phpbb_users u
        WHERE u.user_id = tp.user_id
            AND u.user_type <> 2
            AND tp.topic_posted = 1
            AND NOT EXISTS (
                SELECT tw.topic_id, tw.user_id, 0
                FROM phpbb_topics_watch tw
                WHERE tw.topic_id = tp.topic_id
                    AND tw.user_id = tp.user_id
            );
Capture3.PNG
Capture3.PNG (42.54 KiB) Viewed 980 times
Works perfectly! Users automatically watch the topics they post on. Now that I think of it, this code is probably overkill, but I don't know much about SQL, so I'm not going to fiddle with it. I think I will still request that MOD, for everyone else who has been trying to do this.

Edit: Lol. Don't do this. This is a bad idea. My database host started freaking out from all the open connections after every post. Yes, I clearly didn't think this through, lol.

Periodically running the query manually for now. I'm afraid to set up a cron job lol
Last edited by therainbowrepublic on Thu Oct 09, 2014 12:17 am, edited 1 time in total.
User avatar
Lumpy Burgertushie
Registered User
Posts: 69223
Joined: Mon May 02, 2005 3:11 am
Contact:

Re: 'Notify me upon replies by default' set to YES by defaul

Post by Lumpy Burgertushie »

the only reason to subscribe to a topic or forums is so you will get an email when there is a new post /topic made.

therefore, if you are automatically subscribed when you make a post, you will automatically get an email when someone replies/makes a new post.

opt in means you choose to get email, opt out means you get it when you did not ask for it and that would be considered spam in many circles.

not sure where you got this idea:
This is supposed to be standard forum behavior: You post in a thread, and you are automatically subscribed to the thread.
certainly has never been the standard forum behaviour here.


robert
Premium phpBB 3.3 Styles by PlanetStyles.net

I am pleased to announce that I have completed the first item on my bucket list. I have the bucket.
therainbowrepublic
Registered User
Posts: 15
Joined: Wed Oct 08, 2014 1:17 am

Re: 'Notify me upon replies by default' set to YES by defaul

Post by therainbowrepublic »

Lumpy Burgertushie wrote:the only reason to subscribe to a topic or forums is so you will get an email when there is a new post /topic made.

therefore, if you are automatically subscribed when you make a post, you will automatically get an email when someone replies/makes a new post.

opt in means you choose to get email, opt out means you get it when you did not ask for it and that would be considered spam in many circles.

not sure where you got this idea:
This is supposed to be standard forum behavior: You post in a thread, and you are automatically subscribed to the thread.
certainly has never been the standard forum behaviour here.


robert
You have the option of not receiving emails on subscribed topics. I believe thats what the notify_user colum in the topics_watch table is for.

Yes, in many forms that I participate in, I am subscribed by default to topics I post in. Perhaps it depends on the kind of forums you visit. Users of chat/discussion forums EXPECT this. No one has time to click subscribe on each thread they post in.

And no, you don't receive emails from subscribed topics.
Pony99CA
Registered User
Posts: 4783
Joined: Thu Sep 30, 2004 3:13 pm
Location: Hollister, CA
Name: Steve
Contact:

Re: 'Notify me upon replies by default' set to YES by defaul

Post by Pony99CA »

therainbowrepublic wrote:Yes, in many forms that I participate in, I am subscribed by default to topics I post in. Perhaps it depends on the kind of forums you visit. Users of chat/discussion forums EXPECT this. No one has time to click subscribe on each thread they post in.
Do they have time to turn on automatic notifications using the Notify me upon replies by default option in the UCP?
therainbowrepublic wrote:And no, you don't receive emails from subscribed topics.
Yes, you do. That's what subscribing does (unless you've turned that off and selected Jabber notifications). In phpBB 3.1, you'll also have the option to just get notifications in the notifications area (sort of like the Private Message pop-up without the pop-up).
therainbowrepublic wrote:
Pony99CA wrote:
postcd wrote: What if i want to set email notifications to all users to all they replies they already made? What SQL Query should i use?
That's basically what jelo77 wanted to do, isn't it?

Anyway, I decided to investigate this some more and came up with the following query (if your table prefix isn't phpbb_, update the query with your table prefix):

Code: Select all

INSERT INTO phpbb_topics_watch(topic_id, user_id, notify_status)
SELECT tp.topic_id, u.user_id, 0 
FROM phpbb_topics_posted tp, phpbb_users u
WHERE u.user_id = tp.user_id
  AND u.user_type <> 2
  AND tp.topic_posted = 1
  AND NOT EXISTS (
     SELECT tw.topic_id, tw.user_id, 0
     FROM phpbb_topics_watch tw
     WHERE tw.topic_id = tp.topic_id
       AND tw.user_id = tp.user_id
     )
;
Basically, it selects all topic_id/user_id pairs from the topics_posted table that aren't from guests and aren't already in the topics_watch table and inserts those rows into the topics_watch table with notify_status set to zero (meaning the user will get notified on the next post).

One thing that I'm not sure of is if the AND tp.topic_posted = 1 is needed, because I don't know what a value of zero means there.

NOTE: I tested this on my test board, but not extensively. Make sure that you back up your database before running any SQL query that changes the database like this.
This is perfect. I'm going to run this periodically until I can get someone to build a mod, or figure out how to do it myself.
You should note that my SQL query is only necessary to subscribe existing users to existing topics that they've posted in (if I recall correctly). The information in my previous post is all you need to do to turn subscribing on by default for new (the first part) and existing (the SQL) users. There should be no need to keep running the SQL that you quoted.

Steve
Silicon Valley Pocket PC (http://www.svpocketpc.com)
Creator of manage_bots and spoof_user (ask me)
Need hosting for a small forum with full cPanel & MySQL access? Contact me or PM me.
User avatar
Oyabun1
Former Team Member
Posts: 23162
Joined: Sun May 17, 2009 1:05 pm
Location: Australia
Name: Bill

Re: 'Notify me upon replies by default' set to YES by defaul

Post by Oyabun1 »

therainbowrepublic wrote:You have the option of not receiving emails on subscribed topics. I believe thats what the notify_user colum in the topics_watch table is for.
No. In phpBB users are only notified of the first unread post since their last visit to the topic, that field is to track that.

In phpBB the sole purpose of subscribing to a topic or forum is to be notified of other posts, therefore, setting the "Notify me upon replies by default" option achieves the same thing, for those topics, as automatically subscribing a user to a topic they have posted in.

There is no option to subscribe to a topic but not receive notifications. However, to make note of a topic, but not receive notification of new posts you bookmark it, you don't subscribe to it.

Also, the topic icon indicates whether you have posted in a topic whether you have bookmarked or subscribed to it or not, so you don't need to make a separate listing to achieve that.
                      Support Request Template
3.0.x: Knowledge Base Styles Support MOD Requests
3.1.x: Knowledge BaseStyles SupportExtension Requests
therainbowrepublic
Registered User
Posts: 15
Joined: Wed Oct 08, 2014 1:17 am

Re: 'Notify me upon replies by default' set to YES by defaul

Post by therainbowrepublic »

Pony99CA wrote:
therainbowrepublic wrote:Yes, in many forms that I participate in, I am subscribed by default to topics I post in. Perhaps it depends on the kind of forums you visit. Users of chat/discussion forums EXPECT this. No one has time to click subscribe on each thread they post in.
Do they have time to turn on automatic notifications using the Notify me upon replies by default option in the UCP?
therainbowrepublic wrote:And no, you don't receive emails from subscribed topics.
Yes, you do. That's what subscribing does (unless you've turned that off and selected Jabber notifications). In phpBB 3.1, you'll also have the option to just get notifications in the notifications area (sort of like the Private Message pop-up without the pop-up).
therainbowrepublic wrote:
Pony99CA wrote:
postcd wrote: What if i want to set email notifications to all users to all they replies they already made? What SQL Query should i use?
That's basically what jelo77 wanted to do, isn't it?

Anyway, I decided to investigate this some more and came up with the following query (if your table prefix isn't phpbb_, update the query with your table prefix):

Code: Select all

INSERT INTO phpbb_topics_watch(topic_id, user_id, notify_status)
SELECT tp.topic_id, u.user_id, 0 
FROM phpbb_topics_posted tp, phpbb_users u
WHERE u.user_id = tp.user_id
  AND u.user_type <> 2
  AND tp.topic_posted = 1
  AND NOT EXISTS (
     SELECT tw.topic_id, tw.user_id, 0
     FROM phpbb_topics_watch tw
     WHERE tw.topic_id = tp.topic_id
       AND tw.user_id = tp.user_id
     )
;
Basically, it selects all topic_id/user_id pairs from the topics_posted table that aren't from guests and aren't already in the topics_watch table and inserts those rows into the topics_watch table with notify_status set to zero (meaning the user will get notified on the next post).

One thing that I'm not sure of is if the AND tp.topic_posted = 1 is needed, because I don't know what a value of zero means there.

NOTE: I tested this on my test board, but not extensively. Make sure that you back up your database before running any SQL query that changes the database like this.
This is perfect. I'm going to run this periodically until I can get someone to build a mod, or figure out how to do it myself.
You should note that my SQL query is only necessary to subscribe existing users to existing topics that they've posted in (if I recall correctly). The information in my previous post is all you need to do to turn subscribing on by default for new (the first part) and existing (the SQL) users. There should be no need to keep running the SQL that you quoted.

Steve
Ohhh. Now I see what the problem was. I tried the thing with changing the notify by default in functions.php before I found your SQL update query. When it didn't subscribe existing posts, I assumed it was for something else, and undid it. Silly me! Thanks for your patience lol

Oh, happy days. *dance*
Locked

Return to “[3.0.x] Support Forum”