I'm developing a chat application (android + PHP) and I have the following mysql database:
TABLE User: username (UNIQUE), psw, email (PK), location, user_id (unique), name
TABLE chat_user: id_chat_user, user_id1, user_id2
TABLE chat: id_chat (PK), id_chat_user, user_id, message, timestamp
User_id is generated with the php uuid function and then encrypted in md5.
Now I should handle the roles to make sure that a user needs to read only his chats.
What method can I use?
You can do the following:
To show a logged in user his all chats:
SELECT * FROM chat_user WHERE user_id1 = loggedInUserId OR user_id2 = loggedInUserId LIMIT 10; where loggedInUserId is id of the logged in user.
To show a logged in user all messages from one of his chats
SELECT t2.* FROM chat t1 JOIN chat_user t2 ON (t1.id_chat_user = t2.id_chat_user) WHERE (t1.user_id1 = loggedInUserId OR t2.user_id2 = loggedInUserId) AND t2.id_chat = chat_id ORDER BY timestamp DESC LIMIT 100; where loggedInUserId is id of the logged in user and chat_id is the select chat's id.