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
Creating facebook style messages
Moderator: General Moderators
-
Zander1983
- Forum Newbie
- Posts: 20
- Joined: Mon Mar 21, 2011 2:26 pm
Re: Creating facebook style messages
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.
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.