Flag a message as viewed

[ceci]

Hi.

 

This is general question but what's the best way to capture if someone has viewed a message or an item?

 

I am working on a project and want to capture if it has been read or not almost like this forum captures how many times it has been viewed.

 

thanks

 

C

[litebearer]

just a rough idea

 

if its a page then a session variable that increments each time the page is viewed.

 

if its a message in a database table , then a field that you increment each time that message is sent to screen

 

[ceci]

yes, it will be on a mysql table.

 

do you have an example to increment the field everytime the message is sent to screen?

 

thanks

 

C

[havenpets]

Is it a global message? Meaning is it viewable to more than 1 person?

 

E.g. If I personal mail you, and you read it, it goes from "unread" to "read".

OR

Is it like user 1 viewed it, they see "read" but user 2 hasn't so they see "unread" ?

 

well if it's the 2nd one... easy.

 

user_viewed (table name)

id (auto)

uid (user who is viewing)

type (viewing a message or item)

typeid (message or item id being viewed)

 

When they view the page make sure you update it:

$item_viewed = mysql_query("SELECT * FROM `user_viewed` WHERE uid='$uid', type='item', typeid='$item_id'");
$message_viewed = mysql_query("SELECT * FROM `user_viewed` WHERE uid='$uid', type='message', typeid='$msg_id'");

if(mysql_num_rows($item_viewed) < 1){
mysql_query("INSERT INTO `user_viewed`(uid, type, typeid) VALUES ('$uid', 'item', '$item_id')");
}

if(mysql_num_rows($item_viewed) < 1){
mysql_query("INSERT INTO `user_viewed`(uid, type, typeid) VALUES ('$uid', 'message', '$msg_id')");
}

 

Now on the page to echo Read or Unread... here is the code snippet.

(if on same page as sql update, combine the two...)

 

$item_viewed = mysql_query("SELECT * FROM `user_viewed` WHERE uid='$uid', type='item', typeid='$item_id'");
$message_viewed = mysql_query("SELECT * FROM `user_viewed` WHERE uid='$uid', type='message', typeid='$msg_id'");
if(mysql_num_rows($message_viewed) < 1){
echo 'Message Unread';
}else{
echo 'Message Read';
}

if(mysql_num_rows($item_viewed) < 1){
echo 'Item Unread';
}else{
echo 'Item Read';
}

 

And if you want it to go back to unread to all users when the message or item is updated... simply add this code to the updating code:

 

mysql_query("DELETE FROM `user_viewed` WHERE type='message' AND typeid='$msg_id'");
mysql_query("DELETE FROM `user_viewed` WHERE type='item' AND typeid='$item_id'");

 

And now you can use the same table for multiple things, rather than just the "forum messages".

 

If that makes sense.

[ignace]

havenpets do you have any idea what database normalization is? Or database design for that matter?

 

What do you exactly mean by message or item? Message as in PM? Or as in thread? And item as in product?

 

If you mean message as in PM:

 

messages (..)

users (..)

inbox (message_id, sender_id, is_reply_to, recipient_id, flag_status, ..)

 

SELECT .. FROM inbox i1

JOIN messages m1 ON i1.message_id = m1.id

LEFT JOIN messages m2 ON i1.is_reply_to = m2.id

LEFT JOIN users u1 ON i1.sender_id = u1.id

WHERE (flag_status = 'unread' OR flag_status = 'read') AND recipient_id = $uid

 

Afterwards when the user clicks a message:

 

UPDATE inbox SET flag_status = 'read' WHERE message_id = $mid

 

The reason for flag_status is that you may want to introduce more then just read and unread (cf deleted, marked_as_spam, ..)

 

If you mean message as in thread/topics

 

topics (.., date_last_entry)

topics_viewed (thread_id, user_id, date_last_viewed)

 

When the user requests all unread topics

 

SELECT .. FROM topics t1

JOIN topics_viewed t2 ON t1.id = t2.thread_id

WHERE t2.date_last_viewed < t1.date_last_entry AND t2.user_id = $uid

 

You apply the same logic for an item but instead of date_last_entry you set date_last_update.

[ceci]

That works. Thank you.