[PHP] Category/topic markers?

[liam_d]

I am making a simple forum and wish to show read/unread forum and topic markers like all other forums seems to have, any dieas on the best way?

 

[Bruce_KD]

Assuming you're going by user, not just everyone? (Eg, if Billy reads the topic, it will still be marked as unread for Bobby).

I'm not sure if it would be the best way to go about it, but...
I would have the table of Forums (each has an id, title, etc)
A table for the threads (each has an id, title, then the forum it belongs to)
And a table for replies (also containing an id and the thread it belongs to)

You could then create a table for views, which would contain the user, thread id, and time of view.The first time they view, you would insert a new row. The next, you would update that row.

When deciding if they've viewed or not:

Assuming you had $userid as their id...

SELECT forums.id AS the_id, forums.title, (
  SELECT Count( threads.id )
  FROM threads
  WHERE threads.forum = the_id
) - (
  SELECT Count( views.thread )
  FROM views
  LEFT JOIN threads ON views.thread = threads.id
  WHERE views.user = '$userid'
  AND threads.forum = the_id
  LIMIT 1 
) AS not_viewed
FROM `forums`

That will return the forum id, the forum title, and the number of threads within the forum that the user has not read. If "not_viewed" is greater than zero, there are unread threads.

Now, if you want to have it mark something as unread every time someone posts in that thread, you could do it a few times...

1. In the thread table, you could have a last_post time. When selecting what they've read, check that last_post time against their last read, eg:

SELECT forums.id AS the_id, forums.title, (
  SELECT Count( threads.id )
  FROM threads
  WHERE threads.forum = the_id
  LIMIT 1
) - (
  SELECT Count( views.thread )
  FROM views
  LEFT JOIN threads ON views.thread = threads.id
  WHERE views.user = '$userid'
  AND threads.forum = the_id
  AND views.time > threads.last_post
  LIMIT 1 
) AS not_viewed
FROM `forums`

2. When someone posts in a thread, just delete all entries in the views table with that thread.

3. You could bring the views table down to the post level, though this would be messy. I'd choose #1 or #2.


I used subqueries instead of joins because I often find when dealing with large table, it can be fast. Doing 2 subqueries for maybe 10 forums is probably faster than joining up 10 forums with 1,000 threads and 100,000 views.

Personally, I test all my queries for timing to find the fastest (which usually means most efficient). I often insert a realistic amount of fake data in before doing so.

This may not be (and probably isn't, but I've never bothered to code forums when there are decent ones available already) the best way to do it, but it should work.

The code is untested. It is possible it may not work (Or your server might blow up.)


Bruce

 

[liam_d]

See i thought about that way but then that is pretty database intensive. I will give it a go later and see how it works out though.

 

[liam_d]

Anyone got any other ideas?