[SOLVED] How to get unread topics from the database

Discussion forum for MOD Writers regarding MOD Development.
davidps
Registered User
Posts: 39
Joined: Sun May 30, 2010 1:59 pm
Location: Austria, Vienna
Contact:

[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

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

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

All unsolicited PMs will be ignored.

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

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

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

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

All unsolicited PMs will be ignored.

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

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

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

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

All unsolicited PMs will be ignored.

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

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

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

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

All unsolicited PMs will be ignored.

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

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
Contact:

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


Return to “[3.0.x] MOD Writers Discussion”

Who is online

Users browsing this forum: No registered users and 10 guests