CODEWITHSUNDEEP
phpmysql
abhiburk
abhiburk

Reputation: 2320

Creating Message System in PHP

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

Upvotes: 3

Views: 1015

Answers (1)

CAllen
CAllen

Reputation: 836

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

Upvotes: 2

Related Questions