Am creating a messaging system model after facebook's messages. The message inbox is threaded, but am having issues with my query for viewing for a message and other messages exchanged in thread. db tables look like this.
messages
messag_recips
Note: the MID column is the message ID. Think of each initial message as the beginning of a thread. Instead of having unique message IDs that are all parent/children, I decided to have one main thread (MID) and then have messages in it – which are all defined by the SEQ (sequence number) column. The rest is pretty self explanatory. The created_by and all subsequent user identifications will be numeric ID’s that may point to a different user ID somewhere else. The last thing to note is that the primary key is based on the MID and the SEQ – you will never have more than one entry per MID/SEQ. STATUS column – which will be N for new, A for active (or read) and D for deleted
this is my query
$sql = "select m.mid, m.seq, m.created_on, m.created_by, m.body, r.status from message_recips r
inner join messages m on m.mid=r.mid and m.seq=r.seq
where r.uid='$uid' and m.mid='$mid' and r.status in ('A', 'N')";
but it returns only messages sent to the current user, i want it to return message also sent by the current user. How should i modify the query?
It seems like you are trying to get both messages sent to the current user, as well as messages sent by the current user... an OR relationship.
Try this:
where (r.uid='$uid' OR m.created_by='$uid') and m.mid='$mid' and r.status in ('A', 'N')";