As you are not provided much information about your requirement,I am assuming that you what to create a chat room for two users and these two users can only participate in this room.For this you should not user this schema.
You need to create different tables of different entities and there
relationship.
Here you have two entities.
- User
- room
And one relation between them.
- Chat room
room_table(Insert when room is created)
id -room id
name -room name
created_at -time of creation
status -is this room is active or not
etc
user_table(Insert when user created)
id -user id
name -user name
password -password
created_at -time of creation
status -is this user is active or not
etc
chat_room(Insert when user is assigned in room)
id -user id
room_id -room_id//reference of room.id
user1_id -user_id//reference of user.id
user2_id -user_id//reference of user.id
created_at -time of creation
status -is this chat room is active or not
etc
now for any user you to get the chat_room by:
select id from chat_room where room_id = '$room_id' and user1_id ='$user_id' or user2_id ='$user_id';
Hope this will help you and other. I am wrong any where any one correct me.
Edit:
messages(Insert when sending message)
id -user id
chat_room_id -id of chat room//reference of chat_room.id
user_id -user_id//reference of user.id
message_text -text message
created_at -time of creation
status -is this chat room is active or not
etc
Now for any chat room you can get messages by :
select message_text from messages where chat_room_id = 'id_you_got_from_above_query';