asked on

mySQL query help

I am creating a simple messaging application using mysql and php.

This is my simple database and I have put some example data in it. You can see user 1 and 4 have been having a conversation, then there is a few other single messages.

I want a list of who has had a conversation with you... like it does on the iPhone…. Eg...

- user 4 should only see 1 entry for the conversation between himself and user 1.
- Then they would click that conversation to show the full messaging history between the 2 users.

From the db above for user 4 I would want to see…..

User 1 / Message 5 / 2017-07-23 10:13:00
User 2 / msg / 2017-07-23 10:20:00

Currently my simple query looks like this:

SELECT *
FROM message
WHERE userIDTo = $userID
OR userIDFrom = $userID

Open in new window

but this query shows a list of 8 messages, but i only want it to show the most recent messages... in this scenario it would be 2.

Hope my explanation makes sense and you can help.

Thanks

Steve

Leonidas Dosas

SELECT *
FROM message
WHERE userIDTo = $userID
OR userIDFrom = $userID
ORDER BY messageTimeDate DESC LIMIT 1;

Open in new window

Steve Tinsley

ASKER

Hi Leonidas,
Thanks for the help.
Unfortunately this only give me 1 record.

This is what i see:

I need to see 1 record for each user that has been having a conversation.
So the above example I think should give me 3 records.

what i want to see:

Pavel Celba

If you would like to see all newest messages outgoing from one user to all other users then you may use following query:

SELECT *
  FROM message
 INNER JOIN (SELECT userIDTo, userIDFrom, MAX(messageID) AS msgID 
               FROM message 
              WHERE userIDFrom = $userID
              GROUP BY userIDTo, userIDFrom) maxIDs
         ON maxIDs.msgID = message.massageID

Open in new window

Pavel Celba

If you would like to see all the newest messages incoming to one user from all other users then you may use following query:

SELECT *
  FROM message
 INNER JOIN (SELECT userIDTo, userIDFrom, MAX(messageID) AS msgID 
               FROM message 
              WHERE userIDTo = $userID
              GROUP BY userIDTo, userIDFrom) maxIDs
         ON maxIDs.msgID = message.massageID

Open in new window

ASKER CERTIFIED SOLUTION

Pavel Celba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Steve Tinsley

ASKER

I would like to see the latest of both, but with the name of the other person.... like on the iphone or android txt message list screen.

Pavel Celba

Test it without names first. Then you may join another table containing the names.

Steve Tinsley

ASKER

Bingo (sorry sent the last message just as your answer came in!

Last issue. I always want it to show the other persons name...
Do you I add this joing
LEFT JOIN user u ON u.userID = m.userIDFrom

SOLUTION

Pavel Celba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Steve Tinsley

ASKER

I think this is perfect!
Will tweak and test, but works!!
Cheers
Steve