asked on

Forum topic read/unread server-side tracking

I have a forum modeled after phpBB where I want to track read/unread topics/forums for users server-side. in a MySQL database.

Most of it works correctly except for the piece that marks the forum read once all topics in the forum have been read.

I can't figure out the proper way to check if all topics in the selected forum have been read or marked. If they have been, update the forum_forums_tracking table accordingly.

I'm attaching all code related to this.

Pertinent database information:
users table:
last_mark_time
 
forums_topics:
topic_last_post_time
 
forums_forum_tracking:
userid
forum_id
mark_time
 
forums_topic_tracking:
userid
forum_id
topic_id
mark_time
 
All code related to topic tracking:
index.php (main forum view)
When a user marks all forums read:
	
$timestamp = time();
		
$sqlusers = "UPDATE users ";
$sqlusers .= "SET last_mark_time = " . $timestamp . " ";
$sqlusers .= "WHERE userid = " . $userid;
 
$sqltopic = "DELETE FROM ";
$sqltopic .= "forums_topic_tracking ";
$sqltopic .= "WHERE userid = " . $userid;
 
$sqlforum = "DELETE FROM ";
$sqlforum .= "forums_forum_tracking ";
$sqlforum .= "WHERE userid = " . $userid;
 
mysql_select_db($mysql);
mysql_query("BEGIN");
mysql_query($sqlusers);
mysql_query($sqltopic);
mysql_query($sqlforum);
mysql_query($sql);
mysql_query("COMMIT");
 
viewforum.php (view forum)
When user marks all topics in a forum read:
		
$sqltopic = "DELETE FROM ";
$sqltopic .= "forums_topic_tracking ";
$sqltopic .= "WHERE userid = " . $userid . " AND ";
$sqltopic .= "forum_id = " . $forum_id;
 
$sqlforum = "INSERT INTO forums_forum_tracking ";
$sqlforum .= "SELECT " . $userid . ", ";
$sqlforum .= $forum_id . ", ";
$sqlforum .= "UNIX_TIMESTAMP() ";
$sqlforum .= "ON DUPLICATE KEY ";
$sqlforum .= "UPDATE mark_time = UNIX_TIMESTAMP()";
		
mysql_select_db($mysql);
mysql_query("BEGIN");
mysql_query($sqltopic);
mysql_query($sqlforum);
mysql_query("COMMIT");	
 
viewtopic.php (when a user views a topic/thread)
Here's where the problem lies.
I can't figure out the proper way to check if all topics in the selected forum have been read or marked.  If they have been, update the forum_forums_tracking table accordingly.
 
$timestamp = time();
		
$sql = "INSERT INTO forums_topic_tracking ";
$sql .= "SELECT " . $userid . ", ";
$sql .= $forum_id . ", ";
$sql .= $topic_id . ", ";
$sql .= $timestamp . " ";
$sql .= "ON DUPLICATE KEY ";
$sql .= "UPDATE mark_time = " . $timestamp;
mysql_select_db($mysql);
mysql_query($sql);	
 
//if all threads in forum are read, update forum tracking
$sql = "SELECT mark_time ";
$sql .= "FROM forums_forum_tracking ";
$sql .= "WHERE userid = " . $userid . " AND ";
$sql .= "forum_id = " . $forum_id . " ";
$sql .= "ORDER BY mark_time DESC ";
$sql .= "LIMIT 1";
$recordset = mysql_query($sql);			
if (mysql_num_rows($recordset) == 0)
	{
	$sql = "SELECT last_mark_time ";
	$sql .= "FROM users ";
	$sql .= "WHERE userid = " . $userid;
	$recordset = mysql_query($sql);	
	if (mysql_num_rows($recordset) == 0)
	     { $mark_time = time(); }
	else
	     { $mark_time = mysql_result($recordset, 0); } 			
	}
else
	{ $mark_time = mysql_result($recordset, 0); }
 
$sql = "SELECT * ";
$sql .= "FROM forums_topics ";
$sql .= "WHERE forum_id = " . $forum_id . " AND ";
$sql .= "topic_last_post_time > " . $mark_time . " ";
$sql .= "LIMIT 1";
$recordset = mysql_query($sql);			
if (mysql_num_rows($recordset) == 0)
	{
	$sql = "INSERT INTO forums_forum_tracking ";
	$sql .= "SELECT " . $userid . ", ";
	$sql .= $forum_id . ", ";
	$sql .= $timestamp . " ";
	$sql .= "ON DUPLICATE KEY ";
	$sql .= "UPDATE mark_time = " . $timestamp;			
	mysql_select_db($mysql);
	mysql_query($sql);			
	}
 
//alternate method of checking if topics in forum have been read
$sql = "SELECT t.forum_id "; 
$sql .= "FROM forums_topics t ";
$sql .= "LEFT JOIN forums_topic_tracking tt ";
$sql .= "ON "; 
$sql .= "(";
$sql .= "tt.topic_id = t.topic_id AND ";
$sql .= "tt.userid = " . $userid . " ";
$sql .= ") ";
$sql .= "WHERE t.forum_id = " . $forum_id . " ";
$sql .= "AND t.topic_last_post_time > " . $mark_time . " ";
//something with this line below makes it work sometimes and not others
//for example, if you have more than one unread post in a forum and click one of the topics to read in that forum
//num rows will be > 0 thus marking the whole forum as read, incorrect.  Take this line
//out and the forum never gets cleared.
$sql .= "AND tt.topic_id IS NULL ";
$sql .= "GROUP BY t.forum_id";

Open in new window

Marcus Bointon

This is a strange query:

$sql = "INSERT INTO forums_forum_tracking ";
$sql .= "SELECT " . $userid . ", ";
$sql .= $forum_id . ", ";
$sql .= $timestamp . " ";
$sql .= "ON DUPLICATE KEY ";
$sql .= "UPDATE mark_time = " . $timestamp;

Using a subselect where it's really not necessary. I'd do this:

$sql = "INSERT INTO forums_forum_tracking ";
$sql .= "SET userid = " . $userid . ", ";
$sql .= "forum_id = ".$forum_id . ", ";
$sql .= "mark_time = ".$timestamp . " ";
$sql .= "ON DUPLICATE KEY ";
$sql .= "UPDATE mark_time = " . $timestamp;

It's probably just a paste error, but $sql is not defined in mysql_query($sql); near the start.

Something that may be tripping you up is mixing the use of time() in PHP with UNIX_TIMESTAMP() in MySQL. You should stick with one time source in case they are not perfectly in sync.

ASKER CERTIFIED SOLUTION

HarpuaFSB

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial