[SOLVED] Im stuck on this query

[SirChick]

I had a query idea but i cant work out how to do it... the idea is to find all "messages" by a userID who could be either the sender of the message or the receiver....

 

And with this query i then need to group by the UserID of the person that is "recieving" or has sent to that person the mail. Sorry thats the best i could explain it... heres my query so far:

 

$GetMessages = mysql_query("SELECT * FROM messages WHERE Sender='$ID' OR Reciever='$ID' GROUP BY....something?")
or die(mysql_error());

 

The idea then is to create a list of all the user's that $ID has contacted how ever.. say there are 2 messages to userid : 23

It will only show the ID 23 once.. at the moment it is showing it twice because there are two messages so i need to group by something but its hard to figure out what..

 

 

So say the guy had messaged userid 24, 6 times... userid 36 , 2 times and userid 3, 12 times the list will still show:

23

36

3

[wildteen88]

Looks like all you need to do is group by the urser id column within the message table.

[SirChick]

Yeh but he could of been "contacted" or could have "contacted them" so they could be in 2 different fields... so how would you group it then ?

 

My table is like this:

 

Sender  |  Receiver  |  Message  |  TimeOfMessage

  1              72          test                etc

  72              1            test              etc

 

 

As you see here ^  I have to group it so that 1 is only going to show once in the list not twice..but how can i group it if its to do with two fields ?

Basically im trying to create a list of all users the person has had a conversation with regardless of weather they made first contact to them or vice versa..

[SirChick]

No one ? ?

[Psycho]

OK, let me paraphrase so I undersdtand this correctly.

 

You want to find all messages that were sent to or sent from user MICHAEL. Then you want to group those messages so that all messages that MICHAEL sent or received from ADAM are grouped together and all messages MICHAEL sent or received from BOB are grouped together, and so on.

 

Then all you need to do is add another value to the result list for "otherparty". I would use an IF/ELSE parameter to set the value of otherPartyID to the opposite of the one which matched the user ID you are searching for.

 

SELECT *, IF(Sender='$ID', Reciever, Sender) as otherPartyID

FROM messages

WHERE Sender='$ID'
   OR Reciever='$ID'

GROUP BY otherPartyID

[SirChick]

so if i while out the fetch assoc it should make a list like:

 

People MICHEAL has had a conversation with:

ADAM

BOB

[Psycho]

If all you want is the name of the people a person has had conversations with then remove the "* ," from the query and it will give you just that.