What would be the best way to make a list of all the topics a member has viewed?

[Biology Forums]

Could this be done via tables that already exist in the database?

Or, would I have to create a new table that records every single member's topic viewed.

[Arantor]

You would have to create a new table because the read tracking at topic level can be partially purged by the tracking at board level, so you can't rely on what's available at topic level.

(i.e. if you read a topic, an entry will go in the topic level read log to say you read that topic up to that point, but if you then mark the board as read, the per-topic log is removed as the entire board is marked as read up to that point)

[Biology Forums]

Thanks

[Biology Forums]

Another question,

What would be the smartest way to go about doing this?

I figured I would create another column inside the members table and collect the topic ID each time a topicis viewed (separated by a comma).

Or, a new table with three columns (topic ID, member ID, and date)

Thoughts

[Arantor]

Firstly, new table. There is absolutely no sane way to keep it all in the members table. (The ONLY way to do it there is to have a text field and somehow bring all the topic ids together into a single text value to shuffle in and out of the DB which is not sane for any sizeable forum)

The structure of the table depends on one thing: whether you want the entire history or simply a track of whether a user has seen a topic or not (+last visit time)

If you just want to say 'user last visited topic on date', you need three columns: member id, topic id, date, and make member id + topic id a combined primary key across both columns, then it's a simple REPLACE INTO... query to handle that (as opposed to INSERT INTO)... ideally use a MyISAM table for this. Then you can just REPLACE INTO log table (id_member, id_topic, last_visit) VALUES (1, 1, 1234567890), obviously putting the right values in, and do this at the end of Display.php's display function.

If you want to track all the visits to any topic by a member, things get more complicated. You still have the same table structure but this time you don't make a primary key across id_member, id_topic; you create a table of four columns, id_visit (autoincrement), id_member, id_topic, last_visit, making id_visit the primary key and having a regular non-unique index across id_member, id_topic. Then at the foot of every page do an insert. This will let you see individual visits to topics over time, and you can query that in various ways to get ideas of popular topics.

You might go more extravagant and have a fifth column to indicate start point in the topic (whether that's 0 for first page, or 20 for second page, etc.) so you can work out if it's people reading through a topic and making multiple visits to read subsequent pages or not.

Ultimately it kind of comes down to what you're actually hoping to achieve from it.

[Biology Forums]

Hi Arantor,

Appreciate your response. I created a table called smf_topics_viewed with four columns as suggested.

I then went into display.php, found:

Code Select Expand

// Add 1 to the number of views of this topic.
if (empty($_SESSION['last_read_topic']) || $_SESSION['last_read_topic'] != $topic)
{
db_query("
UPDATE {$db_prefix}topics
SET numViews = numViews + 1
WHERE ID_TOPIC = $topic
LIMIT 1", __FILE__, __LINE__);

$_SESSION['last_read_topic'] = $topic;

and added

Code Select Expand


if($ID_MEMBER > 0)
{
// Insert a record into the search log
$addtime = time();
db_query("INSERT INTO {$db_prefix}topics_viewed
(ID_MEMBER, ID_TOPIC, date)
VALUES ($ID_MEMBER,$topic,$addtime)", __FILE__, __LINE__);
}

The only problem is that when you view the topic more than once after the session is over, it records a new row.

How do I write a query that replaces the row containing the same topic ID and member ID with the new timestamp?

[Biology Forums]

Bump!