Page 1 of 1

Creating facebook style messages

Posted: Fri Apr 08, 2011 10:53 am
by Zander1983
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

Re: Creating facebook style messages

Posted: Fri Apr 08, 2011 7:12 pm
by gooney0
So far it sounds right to me. I guess you'll want to group data for each message by it's ID.

You could either do this logic for this in PHP or perhaps two queries. One to get the messages, and other to get the TO and FROM data.