在android chatapp中实现规则的最佳方法[关闭]

Question

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?

Answer 1

You can do the following:

1. 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.

2. 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.