Check if one message has been read it for a user

[subzero_mb]

Can anyone help me a little bit how smf databases works to check if one message has been read it for a user?

I am programing many things for my own, and what I need now is to find how is storage in the database this info. I imagined that I found the correct table, "log_topics" because it looks like:

member_id   |   topic_id    |    msg_id

So if I would like to know if current user with id 7 has seen msg 55 in topic 20, for example what I do is check if there is any record in table log_topics where member_id = 7 and topic_id = 20 and msg_id = or > 55, if there is something the post has been it, if not is a new message. But I think that I am missing something because not always works...

Also I have noticed that when I click in "Mark all posts as read" all the records inside table log_topics for user 7 are deleted, so in this case my previous words have no sense. Can anyone give me a hand? Many thanks.

[braddd]

Nice one!

[margarett]

That is some crazy sh*t

I am not sure if a lot of people do know how unread messages do work. I don't, but I was around some Sources files trying to check what's going on and I got lost...

I also searched the forums for related questions and I found pretty much nothing answered, that's why I say that probably not many people know how this works...

[subzero_mb]

Many thanks to answer and to look for a solution margarett. I know this is not the typical question about ssi or theme modifications, so I can imagine just smf creators and some freaks like me asked anytime for something like that. Anyway I am sure I will find it and I promise I will post it here when I have the solution, but if in the meantime anyone has any clue please tell me because I think this is going to take me some time... Thanks!

[Shambles]

You could do worse than have a look at the Topic View Log mod. Some clues in there

[Matthew K.]

You could do worse than have a look at the Topic View Log mod. Some clues in there

Wow, how have I never seen that modification? That's sweet.

[emanuele]

What you probably are missing is that the moment a user uses "mark all messages as read" or "mark (this board as) read" the content of this table is completely erased for that particular user.
So what you are doing is not reliably possible with a normal SMF install.

[margarett]

So he can adjust is code to check the content of the table for the current user. If there are no results, then there are no new posts.
If it's not empty, then we can get what he is now using.

Or am I just dreaming high?

[emanuele]

If there are no results it means that either the user *never* read that topic or marked a board or everything as read.
So, with the current structure, if you don't find a result you can't say anything.

[margarett]

So how does SMF makes such distinction? I mean, when I have a new topic, that I never visited, I get the "new" image. So, some way, it is possible to distinguish either a topics has unread messages or not...

[emanuele]

The answer to that is in log_boards.
Thought again, the fact that log_boards contains a value doesn't mean you read all the topics in that board.

[subzero_mb]

As I promised, here we have the solution. The missing table to check if a message is read it for the user is the "log_mark_read" one. Then the proccess is pretty easy. I leave here the solution if it is helpful for someone:

Koodi [Valitse] Laajenna


function is_msg_read($id_msg, $id_topic, $id_board)
{
global $context;

$res_log_topics = mysql_query("SELECT id_member FROM log_topics WHERE (id_member = '".$context['user']['id']."' AND id_topic = '$id_topic' AND id_msg >= '$id_msg')") or die (mysql_error());
if (mysql_num_rows($res_log_topics))
$is_msg_read = TRUE;
else
{
$res_log_mark_read = mysql_query("SELECT id_member FROM log_mark_read WHERE (id_member = '".$context['user']['id']."' AND id_board = '$id_board' AND id_msg >= '$id_msg')") or die (mysql_error());
$is_msg_read = mysql_num_rows($res_log_mark_read) ? TRUE : FALSE;
}

return $is_msg_read;
}


And that it is all. Sorry for the coding quality, I am just a beginner. If someone can improve my function with just one mysql query it will be great because I am not good with join queries

[davidhs]

Thanks for your code, it is very useful. I need this for my new mod

I rewrote your function with just one SQL query, and valid for multiple messages. This is for SMF 2.0.x

Koodi [Valitse] Laajenna

function is_msg_read($id_msg)
{
global $user_info, $smcFunc;

$ids = is_array($id_msg) ? $id_msg : array($id_msg);
$is_msg_read = array();

if ($user_info['is_guest'])
{
foreach ($ids as $id)
$is_msg_read[$id] = false;
}
else
{
$request = $smcFunc['db_query']('', '
SELECT m.id_msg, (IFNULL(lt.id_msg, IFNULL(lmr.id_msg, 0)) < m.id_msg) AS new
FROM {db_prefix}messages AS m
LEFT JOIN {db_prefix}log_topics AS lt ON lt.id_topic = m.id_topic AND lt.id_member = {int:id_member}
LEFT JOIN {db_prefix}log_mark_read AS lmr ON lmr.id_board = m.id_board AND lmr.id_member = {int:id_member}
WHERE m.id_msg IN ({array_int:id_msg})',
array(
'id_member' => $user_info['id'],
'id_msg' => $ids,
)
);
while ($row = $smcFunc['db_fetch_assoc']($request))
$is_msg_read[$row['id_msg']] = !$row['new'];
$smcFunc['db_free_result']($request);
}

return is_array($id_msg) ? $is_msg_read : $is_msg_read[$id_msg];
}

Result is:
- a boolean if input is a single id message
- an array of boolean array(id_msg1 => is_read1, id_msg2 => is_read2, id_msg3 => is_read3,...) if input is an array of id messages array(id_msg1, id_msg2, id_msg3,...)