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

HarpuaFSBAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Marcus BointonCommented:
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.

0
HarpuaFSBAuthor Commented:
I figured this out myself.

Below is the code that correctly marks the forum read if all topics in a forum are read.
		//if all threads in forum are read, update forum tracking
		$sql = "SELECT last_mark_time ";
		$sql .= "FROM users ";
		$sql .= "WHERE userid = " . $userid;
		$recordset = mysql_query($sql);
		$last_mark_time = mysql_result($recordset, 0, "last_mark_time");
		
		$sql = "SELECT mark_time ";
		$sql .= "FROM forums_forum_tracking ";
		$sql .= "WHERE userid = " . $userid . " AND ";
		$sql .= "forum_id = " . $forum_id;
		$recordset = mysql_query($sql);
		if (mysql_num_rows($recordset) > 0)
			{ $forum_mark_time = mysql_result($recordset, 0, "mark_time"); }
		
		$mark_time = (!empty($forum_mark_time)) ? $forum_mark_time : $last_mark_time;
 
		$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 . " AND ";
		$sql .= "t.topic_last_post_time > " . $mark_time . " AND ";
		$sql .= "tt.topic_id IS NULL ";
		$sql .= "GROUP BY t.forum_id";
		$recordset = mysql_query($sql);
		if (mysql_num_rows($recordset) == 0)
			{
			$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 .= $timestamp . " ";
			$sqlforum .= "ON DUPLICATE KEY ";
			$sqlforum .= "UPDATE mark_time = " . $timestamp;
			
			mysql_select_db($mysql);
			mysql_query("BEGIN");
			mysql_query($sqltopic);
			mysql_query($sqlforum);
			mysql_query("COMMIT");					
			}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.