query problem...thnx in advance

[DanDaBeginner]

I have this table:

message

--------+-----------+-------------+----------+-------+-----------+

msg_id  + sender_id  + recepient_id +  content  + read  + time_sent  +

--------+-----------+-------------+----------+-------+-----------+

auto            1                2              hello!          0        4pm ---------the value of this 

incre-          2                1                hi!            0        5pm              field is TIMEDATE

ment          3                1              cute!          0        10am            this is just a sample

                  1              100            how r u        0        5pm

                  4                1              lol!              0        6am

                  1                  4            c u!            0          3am

                  5                  1            hehe!          0          1am

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

 

Im so stuck on this one.

for example the user is 1:

how can I query all the messages send and receive by ID  = 1,

for example I received  message from ID=2 and then ID=1  reply..

how can I get the last message sent or receive by ID=1.

in this example the sample output shoul look lyk this with conversation

with ID=2:

output: login as user 1

----------- 

Message:

Conversation with: 2 "hi!"

-----------

on the output sample above "hi!" was display because this is the latest message since "hello!"

was sent 4pm while "hi!" was 5pm

-----------

 

so the whole output would be: ORDER BY message_id

 

Message:

Conversation with: 2 "hi!"

Conversation with: 3 "hehe"

Conversation with: 100 "how r u"

Conversation with: 4 "lol!"

Conversation with: 5 "lol!"

[skali]

how can I query all the messages send and receive by ID  = 1,
$query = "select * from message where sender_id = 1 OR receiver_id = 1 order by time_sent DESC

how can I get the last message sent or receive by ID=1.
$query = "select * from message where sender_id = 1 OR receiver_id = 1 order by time_sent DESC LIMIT 0,1

[DanDaBeginner]

thank u skali .

I think you didnt get the point..  sorry if my explanation was not clear..I guess its really hard to explain

the logic that plays to your head..

 

but if im going to use this query the output willbe:

 

Message:

Conversation with: 2 "hi!"

Conversation with: 2 "hello!"

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

 

[DanDaBeginner]

I come up with this query but how can I ORDER it by message_id:

 

SELECT sender_id as sid FROM dating_message

WHERE recepient_id = 1 and

sender_id IN (SELECT sender_id FROM dating_message WHERE recepient_id = 1 )

GROUP BY sid

UNION

SELECT recepient_id as sid FROM dating_message

WHERE sender_id = 1 and

recepient_id IN (SELECT recepient_id FROM dating_message WHERE sender_id = 1 )

GROUP BY sid