Zange-chan Zange-chan - 5 months ago 43
PHP Question

php, mySQL Messaging System: Displaying message status: read and unread (differs from other users)

I'm searching answers about my question here, but I didn't quite find the desirable solution on my problem. Maybe I missed something else on that makes it duplicate.

Anyway, here's the problem: Displaying the message status that differs from each user. Let's say

user1
sends a message to
user2
,
user1
's message status then sets to
read
, while
user2
's message is set to
unread
by default. It will be updated after
user2
clicks the message.

So in these scenario, the message of
user1
(from the inbox) will have a gray-colored font which indicates that the message is set to
read
(since
user1
is the one who is sending). On the other side,
user2
have a bold font that indicates that the message is
unread
.

Here is the first structure of the table:



message(messageid, fromid, toid, message, timestamp, status)





The problem here is that if I update the message status to
read
, it affects the other side (
user2
). So I add another column that will set the status differently from
user1
and
user2
:



message(messageid, fromid, toid, message, timestamp, from_status, to_status)





Here,
from_status
is for the
fromid
and
to_status
is for
toid
. But I'm having a problem on how to use these values to display the status.

The php code of that I use during my first attempt is these:



<?php
$id = $_SESSION['id'];
$query = mysql_query("SELECT m.* FROM message m
LEFT JOIN message m2 ON (
(m.fromid=m2.fromid AND m.toid=m2.toid) OR
(m.fromid=m2.toid AND m.toid=m2.fromid)
) AND m.timestamp<m2.timestamp
WHERE (m.fromid='$id' OR m.toid='$id') AND m2.toid IS NULL ORDER BY timestamp DESC");

while ($message = mysql_fetch_array($query)) {
if ($message['status'] === 'unread') {
// bold font style will be applied
}
else {
// gray-colored font will be applied
}
}
?>





(The query fetches each conversation from every user with the latest conversation.)

These code works fine for the main user, which is
user1
, but affects the other side, which views that the message received from
user2
is set to
read
instead or
unread
.

So, I'm having some trouble on what to do on the modified table, having 2 separate
status
each for each user. How can I get these done?

Answer

@andrewsi comment is quite nice, when you'll have for example many receivers. In your case it's only one additional field, so in my opinion it's not an overflow to use just one table. Regarding your case you can do this in one simple sql:

SELECT m.*,
    CASE 
        WHEN m.fromid = $id THEN m.from_status 
        WHEN m.toid = $id THEN m.to_status
    END as read_status
FROM message m
WHERE
    m.fromid = $id OR m.toid = $id
ORDER BY timestamp DESC;

And in your view you are only checking the read_status field