[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Forum topic read/unread server-side tracking

Posted on 2008-02-10
2
Medium Priority
?
1,322 Views
Last Modified: 2008-02-20
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

0
Comment
Question by:HarpuaFSB
2 Comments
 
LVL 25

Expert Comment

by:Marcus Bointon
ID: 20865235
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
 

Accepted Solution

by:
HarpuaFSB earned 0 total points
ID: 20875443
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
Article by: Tammy
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief forma…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question