7 replies to this topic

[MichaelR]

Hello,

I've created my own forum and everything is going very well but some have suggested a feature to subscribe to threads, and then they can have a notification for when a new post is made in that thread. Also some have wanted a way to distinguish threads they have thread from those they haven't and those who have new unread posts. But currently I'm stuck and I'm not sure how I could go about this without having to do a billion MySQL queries.

Any help would be greatly appreciated! :)

[Brandon W]

SQL would be the quickest way to do it. But we need to know the logic behind your forum so we can understand it to do the code.

Or we can tell you how to do it?

[WingedPanther]

Clever use of count and distinct can get you a lot of that information as part of a single query.

[MichaelR]

Brandon W said:

SQL would be the quickest way to do it. But we need to know the logic behind your forum so we can understand it to do the code.

Or we can tell you how to do it?

Well to put it short, there are 7 tables in a database named "club." They are: categories, forums, threads, posts, invite codes, members, and members online.

Whenever someone creates a new thread it inserts the title, author, forum id, and date into the thread table and then inserts the author, post, thread id, forum id, and date into the post table. And when someone posts a reply it will do the same as creating a new thread, by inserting into the post table, and increasing the replies count by 1 for that specific thread in the thread table.

So I'd need some way for after someone makes a post I can show the user there is a new post. I'm thinking making "no new posts" threads in italics, "new posts" threads in bold, and then threads they haven't read in just regular text, but I'm not sure how I should go about keeping track of everything. :confused:

If you need any other information I'll be glad to give it.

[WingedPanther]

Most forums, like this one, also keep track of the last time you read each thread (say within the last month) and compare that against the most recent post in each thread.

[MichaelR]

WingedPanther said:

Most forums, like this one, also keep track of the last time you read each thread (say within the last month) and compare that against the most recent post in each thread.

So I should create a new table? One that has an id, member_id, thread_id, last_visit? But then on the view forum page that would be a lot of queries wouldn't it? Because I'd have to do one per thread.

[WingedPanther]

The key to keeping the queries down is to use joins. You can query the threads with a join to last post read. Then compare the results to the last login date/time and display anything with a null last read or newer post than last login as a new item.