I've got a database table called 'mesg' with the following structure:
receiver_id | sender_id | message | timestamp | read
Example:
2 *(«me)* | 6 *(«nice girl)* | 'I like you, more than ghoti' | yearsago | 1 *(«seen it)*
2 *(«me)* | 6 *(«nice girl)* | 'I like you, more than fish' | now | 1 *(«seen it)*
6 *(«nice girl)* | 2 *(«me)* | 'Hey, wanna go fish?' | yearsago+1sec | 0 *(«she hasn't seen it)*
It's quite a tricky thing that I want
to achieve.
I want
to get: the most recent message(=ORDER BY time DESC) + 'contact name' + time for each 'conversation'.
Contact name = uname WHERE uid = 'contact ID' (the username is in another table)
Contact ID = if(sessionID*(«me)*=sender_id){receiver_id}else{sender_id}
Conversation is
me = receiver OR
me = sender
For example:
From: **Bas Kreuntjes** *(« The message from bas is the most recent)*
Hey $py, How are you doing...
From: **Sophie Naarden** *(« Second recent)*
Well hello, would you like
to buy my spam? ... *(«I'll work on that later >p)*
To: **Melanie van Deijk** *(« My message
to Melanie is 3th)*
And? Did you kiss him? ...
That is a rough output.
QUESTION: Could someone please help
me setup a good SQL command.
This will
be the while loup
<?php
$sql = "????";
$result = mysql_query($sql);
while($fetch = mysql_fetch_assoc($result)){ ?>
<div class="message-block">
<h1><?php echo($fetch['uname']); ?></h1>
<p><?php echo($fetch['message']); ?></p>
<p><?php echo($fetch['time']); ?></p>
</div>
<?php } ?>
I hope my explanation is good enough, if not, please tell
me.
Please don't mind my English, and the Dutch names (I am Dutch myself)
Feel free
to correct my English
UPDATE1: Best I've got until now:
But I do not want more than one conversation
to show up...
u=user table
m=message table
SELECT u.uname, m.message, m.receiver_uid, m.sender_uid, m.time
FROM m, u
WHERE (m.receiver_uid = '$myID' AND u.uid = m.sender_uid)
OR (m.sender_uid = '$myID' AND u.uid = m.receiver_uid)
ORDER BY time DESC;
