I could use some help with my SQL command
- by SuperSpy
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;
