Facebook like messaging system - Remove Duplicate Sender (i.e. one message per user, not duplicates.)

Question

I am building a messaging system similar to facebook's (where it displays messages as threads).

Basically I need to specify at least 1 member id in order to retrieve from the list. So either it's the "to" column so that you're able to get messages you've received from a specific member or use the "from" column in order to retrieve messages sent to you. Either or, the command must specify a member ID somewhere and be able to filter out the duplicate entries.

In this case: https://i.stack.imgur.com/C6liw.png Since i'm member ID 1, i want to be able to retrieve the messages sent to me, but filtering out the duplicate messages sent to me by user ID 50.

So that the end result becomes something like: https://i.stack.imgur.com/eIghG.png

My current function is:

SELECT * FROM `cometchat` WHERE `to` = 1 or `from` = 1 ORDER BY `sent` DESC

This is the output of the above code: i.imgur.com/zQhNNcu.png

What am I doing wrong?

@Sathya Your command results in duplicates still but from the sent folder: http://i.imgur.com/9mMPj62.png — Ronnie Matson, Mar 28 '16 at 08:41

score 0 · Answer 1 · edited Mar 28 '16 at 08:48

0

Did you try

SELECT DISTINCT SENDER_ID, MESSAGE_TEXT FROM ...

at the bottom query? Note using "MESSAGE" = message_text instead of MESSAGE_ID.

It should help if you add several independent messages (several MESSAGE_ID's) for the same message with multiple receivers.

edited Mar 28 '16 at 08:48

Sathyajith Bhat

21,321
22
95
134

answered Mar 28 '16 at 08:25

Alexander Khomenko

569
1
5
13

1

This is what displays when i'm trying to run my current command: http://i.imgur.com/dtIi8SA.png – Ronnie Matson Mar 28 '16 at 08:32
So the problem is you always add two rows for the same message, with sent_id = 0 and the real sent_id. Try "SELECT DISTINCT mess_id, mess_by, mess_text FROM ... " and the rest of you query ;-) – Alexander Khomenko Mar 28 '16 at 08:40
Hm.. i'm curious now, i'll give it a try. – Ronnie Matson Mar 28 '16 at 09:39

score 0 · Answer 2 · edited Jan 08 '17 at 22:11

0

You need to query such that all the conversation between the users are retrieved similar to how we do it in Logs in CometChat administration panel. No change is needed in the way entries are inserted into the database.

For example, to get the conversations between $userid1 and $userid2:

$sql = ("select * from cometchat where (`from` = ".mysqli_real_escape_string($dbh,$userid1)." and `to` =  ".mysqli_real_escape_string($dbh,$userid2).") or (`to` =  ".mysqli_real_escape_string($dbh,$userid1)." and `from` =  ".mysqli_real_escape_string($dbh,$userid2).") order by id desc");

If you need further assistance, feel free to email us at support@cometchat.com.

edited Jan 08 '17 at 22:11

halfer

19,824
17
99
186

answered Mar 29 '16 at 06:20

CometChat

57
3

Managed to use your code for my script and it worked (thanks, although there is one small thing that bothers me and maybe you can help.) -- Basically what i'm trying to do right now is gather all the conversations a user has had with another members (similar to the admin panel of CC, but not display duplicate usernames. Just one grouped.) – Ronnie Matson Apr 04 '16 at 17:53
If you are using MySQLi, would it not be a good idea to recommend parameter binding rather than escaping, Robert? As I understand it, there are some edge cases involving character set configuration problems that can allow SQL injections to creep in even if parameters have been escaped, and that parameterisation is safer. – halfer Jan 08 '17 at 22:11

Facebook like messaging system - Remove Duplicate Sender (i.e. one message per user, not duplicates.)

2 Answers2