PHP chat application, listing conversation contacts problem

[ennio2]

Hello!

I’m building a small chat system for my web app. The conversation chat window is working just fine but I can’t figure out a mysql schema listing the conversation partners for a user in my “messages.php” file.

Here is the table for the chat:

chat_usertouser
utu_id,  utu_senderid,  utu_receiverid,  utu_msg,  utu_groupid, utu_datetime,  utu_status

And the users table, of course

users
us_id, us_fullname, us_pic, ...    etc...

I know I need to check if the logged in user ID is in the utu_receiverid or in the utu_senderid column first then I need to group by the utu_groupid column but I cannot figure out how to display the conversation partner name when listing the conversations.

Can anybody help me with this?

[rpkamp]

There may be more efficient ways, but this should do the trick:

SELECT DISTINCT
   utu_senderid
FROM
   chat_usertouser
WHERE
   utu_receiveirid = :userid
UNION ALL
SELECT DISTINCT
   utu_receiveirid
FROM
   chat_usertouser
WHERE
   utu_senderid = :userid

Where :userid is the ID of the user that is currently logged in.

It gives you a list of user IDs where the user was either the sender or the receipient.
You’d need to join those IDs against the users table if you want more information on them.

[ennio2]

Thanks, this helped a lot!

I joined the user table to get their infos and now it works fine.

$userMsgs = $conn->prepare("SELECT DISTINCT a.utu_receiverid, a.utu_senderid, a.utu_groupid, a.utu_msg, b.us_id, b.us_fullname
                       FROM users b
                       INNER JOIN (SELECT a.*, Row_number() OVER (PARTITION BY a.utu_groupid ORDER BY a.utu_datetime DESC )
                       FROM   chat_usertouser a) a
                       ON a.utu_receiverid = b.us_id
                       WHERE a.utu_senderid = ?
                       GROUP BY b.us_id AND a.utu_groupid

                       UNION

                       SELECT DISTINCT c.utu_receiverid, c.utu_senderid, c.utu_groupid, c.utu_msg, d.us_id, d.us_fullname
                       FROM users d
                       INNER JOIN (SELECT c.*, Row_number() OVER (PARTITION BY c.utu_groupid ORDER BY c.utu_datetime DESC )
                       FROM   chat_usertouser c) c
                       ON c.utu_senderid = d.us_id
                       WHERE c.utu_receiverid = ?
                       GROUP BY d.us_id AND c.utu_groupid

");
$userMsgs->bind_param("ii", $userID, $userID);
$userMsgs->execute();
$getUserChat = $userMsgs->get_result();

[ennio2]

Uhmm… actually a little more help

I removed the group by us_id part from the code because sometimes it echoed the same username for 2 users.

But when I just group by the message group id (utu_groupid) it duplicates the result.
It echoes the last two messages, the one sent by the user and the one sent by the partner.
I assume it has to do something with the two SELECT or/and the UNION but the only thing I can do now is scratching my head

$userMsgs = $conn->prepare("SELECT DISTINCT a.utu_receiverid, a.utu_senderid, a.utu_groupid, a.utu_msg, a.utu_datetime, b.us_id, b.us_fullname, b.us_pic
                       FROM users b
                       INNER JOIN (SELECT a.*, Row_number() OVER (PARTITION BY a.utu_groupid ORDER BY a.utu_datetime DESC )
                       FROM   chat_usertouser a) a
                       ON a.utu_receiverid = b.us_id
                       WHERE a.utu_senderid = ?
                       GROUP BY a.utu_groupid



                       UNION ALL

                       SELECT DISTINCT c.utu_receiverid, c.utu_senderid, c.utu_groupid, c.utu_msg, c.utu_datetime, d.us_id, d.us_fullname, d.us_pic
                       FROM users d
                       INNER JOIN (SELECT c.*, Row_number() OVER (PARTITION BY c.utu_groupid ORDER BY c.utu_datetime DESC )
                       FROM   chat_usertouser c) c
                       ON c.utu_senderid = d.us_id
                       WHERE c.utu_receiverid = ?
                       GROUP BY c.utu_groupid



");
$userMsgs->bind_param("ii", $userID, $userID);
$userMsgs->execute();
$getUserChat = $userMsgs->get_result();

[rpkamp]

You could either remove duplicates in PHP (using array_uniq) or wrap the whole thing in another SELECT DISTINCT with this query as a subquery.

[ennio2]

Can you modify my sql like you said? I’ll be honest, I’m not sure I following you…

[DaveMaxwell]

This seems like it’s working too hard. Wouldn’t it be easier to pull in all the chats where the sender or receiver are the user being searched for and attach the appropriate user information for sender/reciever?

SELECT DISTINCT c.utu_receiverid
	      , c.utu_senderid
	      , c.utu_groupid
	      , c.utu_msg
	      , c.utu_datetime
	      , s.us_id
	      , s.us_fullname
	      , s.us_pic
	      , r.us_id
	      , r.us_fullname
	      , r.us_pic
 FROM chat_usertouser c
INNER JOIN users s ON c.utu_senderid = s.us_id
INNER JOIN users r ON c.utu_receiverid = r.us_id
WHERE c.utu_senderid = ?
   OR c.utu_receiverid = ?
GROUP BY c.utu_groupid