[PHP] category/topic markers?

liam_d · 06-15-2008, 08:39 AM

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 · 06-15-2008, 09:49 AM

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...

Code:

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.
????: NamePros.com http://www.namepros.com/programming/481956-php-category-topic-markers.html

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:

Code:

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 · 06-15-2008, 09:55 AM

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 · 06-19-2008, 08:02 AM

Anyone got any other ideas?

06-15-2008, 08:39 AM	THREAD STARTER #1 (permalink)
liam_d The original NP Emo Kid Join Date: Jan 2005 Location: Plymouth, UK Posts: 1,693 Follow @upurtweet	[PHP] category/topic markers? 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? __________________ Gaming On Linux - Because Linux is Fun!

06-15-2008, 09:49 AM	#2 (permalink)
Bruce_KD NamePros Member Join Date: Sep 2006 Posts: 99	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... Code: 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. ????: NamePros.com http://www.namepros.com/programming/481956-php-category-topic-markers.html 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: Code: 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 __________________ Need a CAPTCHA? www.KingdomsDivided.com

06-15-2008, 09:55 AM	THREAD STARTER #3 (permalink)
liam_d The original NP Emo Kid Join Date: Jan 2005 Location: Plymouth, UK Posts: 1,693 Follow @upurtweet	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. __________________ Gaming On Linux - Because Linux is Fun!

06-19-2008, 08:02 AM	THREAD STARTER #4 (permalink)
liam_d The original NP Emo Kid Join Date: Jan 2005 Location: Plymouth, UK Posts: 1,693 Follow @upurtweet	Anyone got any other ideas? __________________ Gaming On Linux - Because Linux is Fun!

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)