[SOLVED] How to get unread topics from the database

Discussion forum for MOD Writers regarding MOD Development.

[SOLVED] How to get unread topics from the database

Postby davidps » Sun May 30, 2010 2:02 pm

Hello!

How can I get the unread topics/threads from the database?
Which tables and which columns I have to compare?

br, davidps
Last edited by davidps on Sat Jun 05, 2010 3:01 pm, edited 2 times in total.
Image
davidps
Registered User
 
Posts: 39
Joined: Sun May 30, 2010 1:59 pm
Location: Austria, Vienna

Re: Unread topics in the database

Postby stevemaury » Sun May 30, 2010 2:52 pm

Unread by whom?
For REALLY good and VERY inexpensive hosting CLICK HERE

http://www.stevesstocks.com

All unsolicited PMs will be ignored.
User avatar
stevemaury
Support Team Member
Support Team Member
 
Posts: 44096
Joined: Thu Nov 02, 2006 12:21 am
Location: The U.P.
Name: Steve

Re: Unread topics in the database

Postby davidps » Sun May 30, 2010 6:08 pm

Unread by a registered user
It's complicated to understand how the unread post - system works

Can anyone explain it to me, please?

Thanks in advance, davidps
Image
davidps
Registered User
 
Posts: 39
Joined: Sun May 30, 2010 1:59 pm
Location: Austria, Vienna

Re: Unread topics in the database

Postby stevemaury » Sun May 30, 2010 7:25 pm

Unread by ANY registered user? The "View unread posts" link means "View posts you have not read".

The last time a user viewed a forum, that time is marked in the forums_watch table. Same for topics in the topics_watch table. If there was a new topic in a forum or a new post in a topic between last marktime for that user the topic is returned by the search. The forum_last_post_time is compared with the marktime for the user in that forum, or the topic_last_post_time is compared with that users marktime.
For REALLY good and VERY inexpensive hosting CLICK HERE

http://www.stevesstocks.com

All unsolicited PMs will be ignored.
User avatar
stevemaury
Support Team Member
Support Team Member
 
Posts: 44096
Joined: Thu Nov 02, 2006 12:21 am
Location: The U.P.
Name: Steve

Re: Unread topics in the database

Postby davidps » Mon May 31, 2010 6:27 pm

Thanks for your answer, but I haven't found my user_id in the phpbb_topics_track table. So I can't understand, what it is comparing to the topic_last_post_time. When I change the topic_last_post_time to + 1000 it's unread for me.
But what do I need to compare to the topic_last_post_time get all unread posts??

br, davidps
Image
davidps
Registered User
 
Posts: 39
Joined: Sun May 30, 2010 1:59 pm
Location: Austria, Vienna

Re: Unread topics in the database

Postby stevemaury » Mon May 31, 2010 6:55 pm

Let's backup. What is the end game here? What result are you trying to achieve?
For REALLY good and VERY inexpensive hosting CLICK HERE

http://www.stevesstocks.com

All unsolicited PMs will be ignored.
User avatar
stevemaury
Support Team Member
Support Team Member
 
Posts: 44096
Joined: Thu Nov 02, 2006 12:21 am
Location: The U.P.
Name: Steve

Re: Unread topics in the database

Postby davidps » Mon May 31, 2010 7:13 pm

I want do make a program, which alerts the user, when there is a new unread post without refreshing the page in the browser. Thats why I need to understand how the unread-post-system works. And I want to use the database to lookup the unread posts.
I changed the timestamps in the database just for test purposes to see, which are used to mark a post as unread.
Image
davidps
Registered User
 
Posts: 39
Joined: Sun May 30, 2010 1:59 pm
Location: Austria, Vienna

Re: Unread topics in the database

Postby stevemaury » Mon May 31, 2010 11:23 pm

That is beyond the scope of this forum. Moving to MOD Writers' discussion
For REALLY good and VERY inexpensive hosting CLICK HERE

http://www.stevesstocks.com

All unsolicited PMs will be ignored.
User avatar
stevemaury
Support Team Member
Support Team Member
 
Posts: 44096
Joined: Thu Nov 02, 2006 12:21 am
Location: The U.P.
Name: Steve

Re: [unsolved] How to get unread topics from the database

Postby davidps » Tue Jun 01, 2010 2:15 pm

Ok then, any answer in this section?
Image
davidps
Registered User
 
Posts: 39
Joined: Sun May 30, 2010 1:59 pm
Location: Austria, Vienna

Re: [SOLVED] How to get unread topics from the database

Postby davidps » Sat Jun 05, 2010 3:04 pm

I got the answer myself. Here is the query for unread posts:

Code: Select all
SELECT t.topic_id, t.topic_last_post_time, tt.mark_time as topic_mark_time, ft.mark_time as forum_mark_time FROM (phpbb_topics t) LEFT JOIN phpbb_topics_track tt ON (tt.user_id = $user_id AND t.topic_id = tt.topic_id) LEFT JOIN phpbb_forums_track ft ON (ft.user_id = $user_id AND t.forum_id = ft.forum_id) WHERE ( (tt.mark_time IS NOT NULL AND t.topic_last_post_time > tt.mark_time) OR (tt.mark_time IS NULL AND ft.mark_time IS NOT NULL AND t.topic_last_post_time > ft.mark_time) OR (tt.mark_time IS NULL AND ft.mark_time IS NULL AND t.topic_last_post_time > $last_mark) ) AND t.topic_moved_id = 0 AND t.topic_approved = 1 ORDER BY t.topic_last_post_time DESC
 


$last_mark is $user -> user_lastmark.
I posted it if someone needs it.

br, davidps
Image
davidps
Registered User
 
Posts: 39
Joined: Sun May 30, 2010 1:59 pm
Location: Austria, Vienna


Return to [3.0.x] MOD Writers Discussion

Who is online

Users browsing this forum: Bing [Bot] and 9 guests