DanDaBeginner Posted February 27, 2007 Share Posted February 27, 2007 I posted a while ago and though that my problem was solve.. I have this: table name: message Fields: message_id | recepient_id | sender_id | content | time_sent | read auto+increment 1 2 hello! 2 1 hi! 1 3 kiss me! 100 1 oopss! 1 200 yikes... -------------------------------------------------------------------------------------- now my problem is how can I query to get the distinct "recepient_id that has a sender_id=1 " and "sender_id that has a recepient_id=1" ------- SELECT DISTINCT sender_id as sid FROM dating_message WHERE recepient_id = 1 UNION SELECT DISTINCT recepient_id as sid FROM dating_message WHERE sender_id = 1 ------- I already try this it is effective but how can I ORDER it according to time_sent so its use less.. Link to comment https://forums.phpfreaks.com/topic/40344-need-advisehelp-with-my-query-2/ Share on other sites More sharing options...
DanDaBeginner Posted February 27, 2007 Author Share Posted February 27, 2007 the output should look like this ORDER BY time_sent: Message: Hello user 1: conversation with 2: 'display the latest message send by bth of you' conversation with 3: 'kiss me!' conversation with 100: 'oopsss' conversation with 200: 'yikes!' Link to comment https://forums.phpfreaks.com/topic/40344-need-advisehelp-with-my-query-2/#findComment-195204 Share on other sites More sharing options...
monk.e.boy Posted February 27, 2007 Share Posted February 27, 2007 SELECT DISTINCT sender_id as sid,recepient_id as rid FROM dating_message WHERE sid=1 or rid=1 ORDER BY time_sent I'd guess you want to join sender_id to the users table to get the names of send+recipient monk.e.boy Link to comment https://forums.phpfreaks.com/topic/40344-need-advisehelp-with-my-query-2/#findComment-195211 Share on other sites More sharing options...
DanDaBeginner Posted February 27, 2007 Author Share Posted February 27, 2007 thanx for the help. but thats not what I want.. sorry if I can't explain it very clearly.. for example the user has an ID=1, when the user clicked on the message page of the site this page will display all the conversation that the user has, displaying the latest message that he has. for example if the user message to Id=3 'HEllo' and ID=1 reply 'HI!' so the latest message is 'HI' between the conversation of ID=1 and ID =3 so the output will be like this: Conversation with 3: 'HI' --- Link to comment https://forums.phpfreaks.com/topic/40344-need-advisehelp-with-my-query-2/#findComment-195218 Share on other sites More sharing options...
DanDaBeginner Posted February 27, 2007 Author Share Posted February 27, 2007 kindly check this one: ------------- SELECT MAX(time_sent) as latest_msg, recepient_id FROM dating_message WHERE sender_id = 1 GROUP BY recepient_id UNION SELECT MAX(time_sent) as latest_msg, recepient_id FROM dating_message WHERE sender_id = 1 GROUP BY recepient_id ORDER BY latest_msg DESC -------------- im not sure on this one is this reasonable enough? Link to comment https://forums.phpfreaks.com/topic/40344-need-advisehelp-with-my-query-2/#findComment-195229 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.