Reputation: 1664
I have two tables
participants : thread_id, participant_id
messages : message_id, thread_id, message_poster_id, message
Provided a thread_id how do I make sure someone that is not a participant does not read a thread they do not belong in? Is this possible in one query or must I run one query then use php to say whether or not they are allowed to run the 2nd query to fetch messages?
The two variables I have is the sessionid and the thread_id.
My logic is SELECT message.* WHERE thread_id = '1' and participant is PART (not and) of thread_id = '1' FROM participants table.
Problem with that is that there are other participants and I need to show all the messages for that thread, not only the session holders. In other words I am looking for a query to get all messages in a given thread that the session holder is a participant in, even if he didn't post a message (participants are given in participants table).
Upvotes: 0
Views: 51
Reputation: 4957
SELECT m.* FROM messages m INNER JOIN participants p
ON m.thread_id=p.thread_id
WHERE m.thread_id = '1' AND p.participant_id='the-session-holder-id'
If the above query return 0, it would means session-holder-id is not a participants.Otherwise, the query will return all the messages belongs to that thread.
Upvotes: 3