I'm not sure how you've constructed your system. So I couldn't tell you where to store the info. It would probably be best to store it in the subscribed table (if you have one) as you will need a new value for each user.
All you're worried about is finding posts that are new to the user. This means that you need to know which posts the user has seen. It doesn't matter whether or not the user has or hasn't posted. If you record the last time a view of the thread/forum was sent to them you can then find all new posts since that time and construct a list to give to the user.
So you're suggesting I create a log similar to what YaBB has.
Each time a user views a post it get's logged, and each time they view a forum the log is checked to see which post is new and which one isn't by comparing their last view date with the last post date.
A good system, the only thing that worries me is how fast it'll run if the forum has a lot of members.
One way to keep the log small is to erase all entries that are a week old, so it's kept small.
1. create a cookie with the date/time of the last post.
2. have a field in the member table and update that field on every page load.
3. When they come back you grab this date from either the cookie or the member table and then if that date is older then the last post for each forum then make a flag stating the forum has new posts. Your table that lists your forums should have a column for the last post date.
4. When you click on the forum you do the same thing for each topic. You flag each one that has a post which is newer then the date of the last time the person views your forum. Your topic table should have a column for the last post date.
Since you should be validating the person at every load anyways I would have it also update the employee table with the most current date/time.
The number of entries is limited to the number of users*number of threads. Whenever a thread is removed remove any entries corresponding to it. This a minimal enough thing that you should be able to have half the population of the US use your forum and you should still be able to operate just fine.
If I just loaded this page, it would then be concidered new correct, even if I don't post in it?
Yes it would be. Every time you load your forum you should be validating that the member logged in has rights to view the site. At the same time you do this (at every page load) you will also do an update on your member table and update the late page load date.
You then use this date to figure out what is new from the last time they view the board. This takes relatively very little processing power and you do not need to have any special table for it.
For the permissions I'm using userlevels, and each userlevel is given a unique five digit id with an astrisk in front and behind of it (*00000*) for example.
In the categories and forums tables thereís a column that holds the viewing permissions, so if a userlevel is in that viewing permissions column they can view it (There can be countless userlevels in the one cell so it's very efficent), itís also not rank dependent, meaning that the admin or mods canít see a thread and a guest can. Some forums have it set so the higher up you are in the rankings the more forum access you have.
Anyways, back on topic, I'm going to try and avoid cookies so if a user is at another computer or they reformat their HDD they won't have to filter through all the posts to get caught up.
So along the lines of what you two are saying I already have a last visit column in the members table, and I'm validating each user and setting their permissions with each page, but I don't see how linking their last visit with the last view would work.
What I think Iím going to do is mimicking what I did with the viewing permissions. In each thread, forum, sub forum and category table there will be one column called viewed_by, this column can hold everyoneís id (*0* for instance).
Whenever someone views a thread, forum or category, if their id isn't in that column, it gets added, and whenever thereís a new post, that column is emptied.
This way, if your ID is in that column you've seen the post, and if it isn't, it means it's the column has been whipped clean because someone made a new post.
This should be the most accurate system to date (Not to sound coincided or anything but even vBulletin isnít all that accurate. Iíve logged into a forum made one post and left. When I came back all the posts were marked as viewed, even though I havenít viewed them) and easy on the processing.
An interesting approach. For highly active forums it will certainly be more efficient. My one suggestion would be don't worry about doing check before you add the user's id to the list. Just add it. And when you're checking to see if there's something new just stop at the first one. Should work just fine. The one problem is that you won't have very much resolution. All you will know is that I haven't seen a message in this thread. You won't know how many or which ones. But maybe you don't need that, all you need to know is that I haven't seen something.
I think you have a good point about just adding the id to the list, it'll be much quicker and won't take up much space.
As for the threads themselves (When you enter the forum Programming Discussion you view all the threads in it) they'll be highlighted fine, and so will the forums, but you did bring up a good point about having difficulty knowing which ones are new and which ones aren't.
I think it can be done by searching the threads for all posts where the user hasn't viewed them, I'm not sure if MySQL supports any query like "where missing $id". It'll be something to look into for sure.
Okay, this is how it works. Hopefully it makes sense....
--This is the date of when the user last viewed a page--
--This is the date of the last post made in the forum--
When the page first loads you grab the last_page_load_date and assign it to a variable we will call dte_last_page_load. Once you have that in a variable you then update the tbl_member table with the current date.
Now you have your variable of dte_last_page_load which is the date of when the person last viewed your forum.
Now when you return your list of forums from tbl_forum you need to also pull the last_post_date.
When you loop through each forum you do a check like this
If(last_post_date > dte_last_page_load)
You indicate on your page that new posts are in that forum
no new posts have been made since the last time they have viewed the forum.
so tbl_member.last_page_load_date is one table, with a column called last_page_load correct?
If this is correct I'll have to serialize an array that holds all the threads the member has posted in, or make a table to hold just the thread views (There could be thousands of threads that are viewable).
It would be accurate and wouldn't need too much in the way of processing.
How would I grab the new posts though? Any post that has a newer last post date then their last login date which is missing their last viewed record should work (Poorly explained I know).
Thanks for the help, I think I should be able to devise a good system now.
---so tbl_member.last_page_load_date is one table, with a
---column called last_page_load correct?
tbl_member is the table with your member info
last_page_load is a column in the table
---If this is correct I'll have to serialize an array that holds all the
---threads the member has posted in, or make a table to hold
---just the thread views (There could be thousands of threads
---that are viewable).
If all you want to know is if new posts have been made since the last time the person has visited the forum then why do you need to know all the threads the member has posted in?
I am assuming you have 2-20 forums in your board and this list of forums is in your forum table (we will call tbl_forum). Every time a post is made you update tbl_forum with the date of the post. So say a person posts in forum 3 you would have...
Now when another use views the board you will go into tbl_member to see when they last viewed your board. Save this date in a cookie and then update that field with the current date. We will call this cookie cookie_last_load_date. If the last_post_date from tbl_forum is after cookie_last_load_date then you know that new posts exist in this forum.
Now when you click in the forum you will display a list of topics and you want to know which topics have new posts from the last time the person logged in. What you do is compare the last_post_date from your topic table with cookie_last_load_date.
As for your cookie, it should expire when the window is closed.
before you started creating your forum/board did you do any documentation? at the very least you want to create an ERD of your tables and data dictionary of all your fields in all your tables. this way it will be much easier for you to see what you need to do.
I'm currently drafting a layout and skinning the board (Luckily thereís a great templating system so skinning the board is easy) so I can do all the documentation on-line as I make it.
I'm also planning to have the new post notifications in the actual forums as well, so you can see what threads are new and which ones aren't. This is why I wanted something quick and easy on the processing.
documentation should be done before you do any coding. having a prototype (skinning) is also important, but that does not show your data structure and if you do not have a strong understanding of this layer you will hit a lot of problems. Another thing you need to keep in mind is security. Building a forum is more then just creating topics and posts. The security, validation, and moderation tools can take up 1/3 of all the work easy.
Thereís a fairly good security system built into the forum. It uses a forum wide access code that's put into a session, so if anyone does guess what the code is, you simply change it and they need to guess it again to gain access. Considering that it's also md5 encrypted it can be hard to guess. Thereís also a couple other checks that are done to ensure security.
The reason I'm doing everything generally at once is because I already planned how it's going to work together, I just don't have it well documented. Just the new post notification was giving me a bit of trouble, I couldn't think of anything efficient.
You're right about making the moderator permissions; I think that'll take a long time.