I m trying to create message system where I want to list down the users with whom one has interacted with recent chat on top I know this question is duplicate as well but i cant figure out where the problem is Here is my message table structure
user_message table
msgID sentBy sentTo message sentTime
1 3 1 Hii B 1493306882
2 3 1 Hi A 1493309615
1 1 3 Hmm 1493306882
2 1 3 Yeah 1493309615
and the query i tried is
QUERY
SELECT * FROM user_message_status LEFT JOIN users ON user_message_status.userID=users.userID
LEFT JOIN user_message ON user_message_status.msgID=user_message.msgID WHERE user_message_status.userID='".$_SESSION['userid']."'
GROUP BY user_message_status.msgID ORDER BY user_message.msgID DESC
PROBLEM:
1) When userA send msg to userB, userA name is printed in chat list instead of recipient(userB)
2) The amount of msg sent or receive equals to the number of users are listed in the chat.
user_message_status: table
msgstatusID msgID userID box read readTime
1 1 3 outbox 0 0
2 1 1 inbox 0 0
This is status table to read unread messages or delete
First update the query to the below:
SELECT *
FROM user_message_status s
LEFT JOIN users u USING userID
LEFT JOIN user_message m USING msgID
WHERE s.userID='".$_SESSION['userid']."'
GROUP BY user_message_status.msgID
ORDER BY user_message.msgID DESC
Because both tables have the same column names, to make you sql shorter and neater, you can use the USING function
Both LEFT JOIN users ON user_message_status.userID=users.userID and LEFT JOIN user_message ON user_message_status.msgID=user_message.msgID was changed. We then renamed the tables to just a single letter so you don't have to write the entire table name.
Also why your userA is sent is because, your query will return, lets say I am id '3',
msgstatusID msgID userID box read readTime
1 1 3 outbox 0 0
So from the joins, the user_message will return
msgID sentBy sentTo message sentTime
1 3 1 Hii B 1493306882
1 1 3 Hmm 1493306882
So you would have to use an if statement or turnary operation for the name and say:
if (sentBy == $_SESSION['userid'])
$name = sentTo;
else
$name = sentBy;
OR
$name = (sentBy == $_SESSION['userid']) ? sentTo : sentBy;
Basically to show the proper name of who you are talking to, you need to first check which column name to use
AS for problem 2, if you want to limit the amount then you need to add more condition. Maybe you only want outbox so you need to put AND box = 'outbox'
The below sql should help you out
SELECT
s.msgId
, m.message
, (
SELECT CONCAT(firstName, ' ', lastName)
FROM users
WHERE userId = CASE WHEN m.sentTo = s.userId
THEN m.sentBy
ELSE m.sentTo END
) AS chatWith
, m.sentTime
, s.read
FROM user_message_status s
LEFT JOIN user_message m USING (msgID)
WHERE s.userID='".$_SESSION['userid']."'
ORDER BY m.msgID DESC
Remove LEFT JOIN users u USING userID because it will be sub queried
s.msgID // used for deleteing the chat conversation
m.sentBy, m.sentTo, m.message, m.sentTime // all important data for your messages