CODEWITHSUNDEEP
phpmysqlsqlmessage
JoOlay
JoOlay

Reputation: 39

MySql query for direct message

Hi I'm trying to create a direct message inbox in PHP. I tried to make a query that would get the latest message between the logged in users and everyone that the logged in user has either messaged or had a message sent to the logged in user. Similar to how Insatagram and twitter show in their inboxes a list of all the conversations between the logged in user and people that have message them or they have messaged.

As of now my query shows all the conversations between the logged in user and the people the have had conversations with when I only want the latest message between the logged in user and any another users. I hope I have explained this well enough.

My query statement as of now is:

SELECT *
FROM dm
WHERE(receiver="user") or (sender="user")
ORDER BY senttime DESC;

My direct message table looks like this: 

CREATE TABLE IF NOT EXISTS dm (  
    id INT(11) NOT NULL AUTO_INCREMENT,  
    receiver VARCHAR(100) NOT NULL,  
    sender VARCHAR(100) NOT NULL,  
    senttime DATETIME NOT NULL,  
    message TEXT NOT NULL,  
    PRIMARY KEY (id)
)

For example:

id  receiver sender      senttime            message
1     Jack   Will     2016-07-20 20:59:27      Hi
2     Jack   Bob      2016-07-21 20:59:27      What's up
3     Bob    Jack     2016-07-22 20:59:27      Hanging out what about

should return the following if Jack is the user in the query

id  receiver sender      senttime            message
3     Bob    Jack     2016-07-22 20:59:27      Hanging out what about you?
1     Jack   Will     2016-07-20 20:59:27      Hi

Upvotes: 0

Views: 389

Answers (1)

alexander.polomodov
alexander.polomodov

Reputation: 5534

Try something like this (see sqlfiddle):

SELECT * FROM dm
INNER JOIN
(
  SELECT MAX(id) as id FROM ( 
    SELECT MAX(id) as id, receiver as contact
    FROM dm
    WHERE sender="Jack"
    GROUP BY receiver
    UNION ALL
    SELECT MAX(id) as id, sender as contact
    FROM dm
    WHERE receiver="Jack"
    GROUP BY sender
  ) t GROUP BY contact
) d
ON dm.id = d.id
ORDER BY senttime DESC;

Output will be:

id  receiver    sender  senttime                message 
3   Bob         Jack    July, 22 2016 20:59:27  Hanging out what about
1   Jack        Will    July, 20 2016 20:59:27  Hi

Run this code for creating test data:

CREATE TABLE IF NOT EXISTS dm (  
    id INT(11) NOT NULL AUTO_INCREMENT,  
    receiver VARCHAR(100) NOT NULL,  
    sender VARCHAR(100) NOT NULL,  
    senttime DATETIME NOT NULL,  
    message TEXT NOT NULL,  
    PRIMARY KEY (id)
);

INSERT INTO dm (receiver, sender, senttime, message) VALUES
('Jack', 'Will', '2016-07-20 20:59:27', 'Hi'),
('Jack', 'Bob', '2016-07-21 20:59:27', 'What\'s up'),
('Bob', 'Jack', '2016-07-22 20:59:27', 'Hanging out what about');

Upvotes: 3

Related Questions