mysql连接无法正常工作

Question

I have 3 tables in mysql database

chat
user1, user2, chat_key

chat_seen
msgid, userid, viewed

users
username, status, key

So im trying to display the chats ordering them by the users online then messages seen. But its not working. this is what i have so far, iv only got it to work with chat and users, i dont know how to add order by chat seen

("SELECT u.random, u.status, c.chat_key, c.seen, u.username
                                     FROM chat c, users u
                                     WHERE CASE 
                                     WHEN c.user1 = ?
                                     THEN c.user2 = u.random
                                     WHEN c.user2 = ?
                                     THEN c.user1= u.random
                                     END 
                                     AND (
                                     c.user1 = ?
                                     OR c.user2 = ?)
                                     ORDER BY u.status DESC LIMIT 50");

So the results should be

username - online - (2 messages)
username - online - (1 message)

username - offline - (20 messages)
username - offline - (2 messages)
username - offline - (o messages)

At the moment i have

username - online - (0 messages)
username - online - (0 messages)
username - offline - (2messages)
username - offline - 20 messages)

Answer 1

Try this query below:

("SELECT 
u.random, 
u.status, 
c.chat_key, 
c.seen, 
u.username

FROM chat c, users u

WHERE 
(c.user1 = ? AND c.user2 = u.random)
OR
 (c.user2 = ? AND c.user1 = u.random)
AND 
(c.user1 = ? OR c.user2 = ?)

ORDER BY u.status DESC LIMIT 50");

Let me know if it works. :D