message system

[mangel.murti]

i am building a message system for website.i have two table one store user_detail and other store message sent to each other users. you can see main.jpg file where inbox sent etc.. will be display and action takes accordingly.

$query='select m1.id, m1.title, m1.timestamp, count(m2.id) as reps, user_detail.user_id as userid, user_detail.username from pm as m1, pm as m2,user_detail where ((m1.user1="'.$inbox_req_param['pm.user_id'].'" and m1.user1read="yes" and user_detail.user_id=m1.user2) or (m1.user2="'.$inbox_req_param['pm.user_id'].'" and m1.user2read="no" and user_detail.user_id=m1.user1)) and m1.id2="1" and m2.id=m1.id group by m1.id order by m1.id desc';

i am executing above query prob is that there are two records sent by user_id 99 to 100 and one record by user_id 100 --> 99.when i am login with different user account then it is showing both user records?

how to achive this. is my db is desing properly.

[ss125]

My suggestion is that Kindly validate the front end of your program.i.e., by checking whether a particular userid is already active... and then proceed...

And also,
its safe to assure that you are not allowing multiple login at same time for a user. So In that case you can set a flag column in your user database as Isactive(char(1)) where nly y and n to be stored. So that if any problem exits u can track back the code easily.

My PM system approach

msg_id [PK, int]
parent_id [int] = allows this msg to be part of a conversation thread
pmtimestamp [timestamp]
from_id [FK, int]  = appear in this person's outbox/sent folder
to_id [FK, int] = appear in this person's inbox
from_status [(FK?), tinyint] = e.g. 0 (unread, default), 1 (read), 2 (deleted)
to_status [(FK?), tinyint] = e.g. 0 (unread, default), 1 (read), 2 (deleted)
title [varchar]
body [text]

The user

user_id [PK, int]
username [varchar]
...

If you are user 100 and you've sent a msg, then you look at:

SELECT .... FROM .... WHERE pm.from_id = 100 ORDER BY parent_id, pm.pmtimestamp

Then you shouldn't see the messages to 100 from others unless you set it up so that all messages (to and from) were on the same page. In which case you'd do something like:

SELECT .... FROM .... WHERE pm.from_id = 100 OR pm.to_id = 100 ORDER BY parent_id, pm.pmtimestamp