Simply query

[johnsmith153]

 

primary_id / from_user_id / to_user_id / time / message

 

1 / 33 / 189 / 1334865983  / "Hello there"

2 / 44 / 76 /  1334897600 / "Hi Dave"

3 / 63 / 33 / 1334907887  / "Hello, message here"

4 / 33 / 63 / 1334908903 / "Hi again"

 

I have a simple table like above for storing messages sent between 2 users.

 

I need to display one record for each 'conversation' that a user has, so in this example, user 33 has had 2 conversations:

- conversation 1: one message sent by user 33 in record 1 to user 189 (no reply messages or other messages between the two users)

- conversation 2: one message received by 33 in record 3 and one sent in record 4, both to user 63

 

I want to display the most recent message and show it like a header page where they click and view all messages between the two users.

 

I only need help on the query to retrieve the data from table above so I can display like below. I have everything else under control.

 

I am thinking GROUP BY, but how do I return the latest message (and time field) and how do I GROUP BY considering the user id could be in either the from_user_id or to_user_id field?

 

So I need to show the data like this in a header page (I just need help on the query that will sort the data to allow me to do this):

 

----

 

User 33's messages:

-------------------------

 

Conversation number 1 (with user 189)

(date = 1334865983) Hello there (shows latest message)

 

Click HERE to View

 

---------------------------------

 

Conversation number 2 (with user 63)

(date = 1334908903) Hi again (shows latest message)

 

Click HERE to View

[Barand]

Try

SELECT 'Incoming:' as Direction, from_user_id as Userid, time, message
FROM message
WHERE to_user_id = 33
UNION
SELECT 'Outgoing:' as Direction, to_user_id as Userid, time, message
FROM message
WHERE from_user_id = 33
ORDER BY time

[johnsmith153]

Thanks Barand, but this isn't going to work.

 

I need it to show a header list of each set of messages / conversation (for messages between two users) so when clicked it then shows all of them. I'm sure there needs to be some sort of GROUP BY function.

 

I think yours will return a load of messages.

 

Another forum provided this which I think is closer to it: http://www.sitepoint.com/forums/showthread.php?847726-Simple-query

 

Thanks again though.

[fenway]

Another forum provided this which I think is closer to it: http://www.sitepoint.com/forums/showthread.php?847726-Simple-query

 

Thanks again though.

Please don't do that -- don't troll multiple forum web-sites for answers -- otherwise, you're just wasting our time.