Creating facebook style messages
Posted: Fri Apr 08, 2011 10:53 am
Hi
Im created a market place website, sort of like eBay. I want to allow the shop owners to be able to message the user who has purchased an item from them and
vice-versa. I want it to be like Facebook where a user sends a message, and if they reply, it becomes a thread which they reply to.
But if a user send a message thats not in reply to a thread, a new thread is formed
What I'm looking for is the correct model to use. I set up this table:
Message table
MessageID -> primary key
ReplyToID -> this keep track of all messages that belong to the same thread
FromID -> id of sender
ToID -> id of receiver
Message -> the text of the messsage
Date -> date message was sent
On the page where i display a thread of messages, my sql is
SELECT ms.MessageID, ms.ReplyToID, ms.Message, ms.FromID, ms.ToID, m.UserName FROM messages ms
join member m on (m.MemberID = ms.FromID or m.MemberID = ms.ToID)
WHERE ReplyToID = 8
But say there are 2 messages in the thread, the sql returns 4 rows as it needs to return 2 rows for each message to return the to and from usernames...
Do I need to use a cursor here?
Really, I want to know if I on the right track with this? Or is there a different way of doing it?
Thanks in advance
Im created a market place website, sort of like eBay. I want to allow the shop owners to be able to message the user who has purchased an item from them and
vice-versa. I want it to be like Facebook where a user sends a message, and if they reply, it becomes a thread which they reply to.
But if a user send a message thats not in reply to a thread, a new thread is formed
What I'm looking for is the correct model to use. I set up this table:
Message table
MessageID -> primary key
ReplyToID -> this keep track of all messages that belong to the same thread
FromID -> id of sender
ToID -> id of receiver
Message -> the text of the messsage
Date -> date message was sent
On the page where i display a thread of messages, my sql is
SELECT ms.MessageID, ms.ReplyToID, ms.Message, ms.FromID, ms.ToID, m.UserName FROM messages ms
join member m on (m.MemberID = ms.FromID or m.MemberID = ms.ToID)
WHERE ReplyToID = 8
But say there are 2 messages in the thread, the sql returns 4 rows as it needs to return 2 rows for each message to return the to and from usernames...
Do I need to use a cursor here?
Really, I want to know if I on the right track with this? Or is there a different way of doing it?
Thanks in advance