2

I have have created a Private messaging system using PHP and mySQL with notification a bit like Facebook.

The database table has following fields( not all listed):

  • MessageID
  • senderUserID
  • RecUserID
  • Message
  • Subject
  • DateTime
  • Status - whether read or not
  • RepliedStatus - how should i use this?
  • DeleteRec - delete from inbox
  • DelSender - delete sender inbox
  • RepliedUserId - When user reply to orginal message this is change to receiver's id

All replies are stored in a second table, since each message create a thread. The second table looks a bit like this:

  • messageID - FK
  • repuserID
  • Mesage
  • DateTime

At the moment when a new message is sent out to a user i change the 'status' of the message to unread, From this can run a count query to list all of the unread messages in notification.

But if the users replies back to that message i cant set the original 'status' field to unread, since this will appear on both users notification. so i created another field called 'RepliedStatus ' but i am unsure how would i use this to show notification on message reply?

thanks guys.

TheDeveloper
  • 890
  • 1
  • 15
  • 37

3 Answers3

2

If you have a replies table then you don't need a replied status column on your first status. By virtue of there existing a record in the replies table you know that a user has replied to a message

plenderj
  • 563
  • 4
  • 15
  • aah one problem having the status in replies tables, both (sender / receiver) users will get the notification, since replies act like thread to main message a bit like face book. example : user A send message to user B, user B replies back to that message than user A replies back to the same message and etc all this is in the same message. – TheDeveloper Jan 11 '12 at 19:08
0

Why dont you add an INT and nullable column to the first table (let's say, "messages" table) named "previous_message"?

ALTER TABLE messages ADD COLUMN previous_message INT DEFAULT NULL;

So every message will have in the same table the previous one, and you can work out the sequence. If it helps you can have a "next_message" column with same definition and update the relevant record on reply.

Doing so you can use the status column on each reply.

If you want to keep the same DB organisation I would suggest to add a column status on the second table (let's say "replies").

Hope this helps

Uboonto
  • 69
  • 4
0

Me I'll put deleted column once and the same things for the read or not-read like:

[{"0":"both", "1":"Sender", "2":"receiver"}];

And then fetching a tread messaging like:

$sql = "SELECT * FROM messagetreads 
WHERE (senderID OR receiverID = ".$_SESSION['MyCurrentId'].")
AND deleted !== 0
ORDER by TreadID AND DateTime ASC";

When a sender "delete" is tread... All tread relatedID in the database change for 1 or 0 if delete colomn is 2...

But I think it's better to creat another colomn for getting of the repeated deleted and notifications data like

  • TreadID (FK_message_Table)
  • delete (0=both deleted UserID=don't appear to this sender or receiver)
  • notify (0=both read UserID=read to this sender or receiver)

Then it's ezee to change a status or a delete statement.

The SELECT will be sometings like this:

$SQL = "SELECT * 
FROM messagetreads
WHERE (senderID OR receiverID = ".$_SESSION['MyCurrentId'].")
IN (SELECT TreadID WHERE delete !== (0 OR ".$_SESSION['MyCurrentId']."))";

If is our member id involve in the colomn delete all the tread don't appear but if is the id of the receiver when the sender will delete 0 can be attributed so that both of the members can "delete" the message. Same thing for notifications!

very far later a cron-job can come delete very-old message read(0)...

PS: this can be notification system too like notifying for a new post in wall or comment on a photos or new events on calendar ... just add more information in column data and faormat it with php or java-ajaxed alike...

Jean-Christophe Duperron
  • 54
  • 5
  • [queue]:http://phpmaster.com/zend-queue/ And I found Interesting for notification way to find it is to store timestamp of the message and of the visit of the member to the message_tread and compare it in a parallel procedure like zend-queue doe's ([zend-queue on PHP-MASTER][queue]http://phpmaster.com/zend-queue/).Also I have separate the participants to the tread in Another Table because I just want to be able to add members to the message tread (just like Facebook...) and all row contain last visited timestamp, user_id, the message_Tread_id – Jean-Christophe Duperron Jan 20 '12 at 11:42