HELP! desperately need help..."RE:" in messages.

[kendallkamikaze]

I've been working on a code for my messaging system on my game... this is the code i have for messages, which is functional:

<?php

include 'header.php';

?>
<BR><BR>
<br><center>
<table border="1" bordercolor="#000000" cellpadding="2"
cellspacing="0" height="400" width="415">
  <tbody>
    <tr>
      <td class="page" valign="top">
      <div style="border: 0pt none ; height: 400px; overflow: auto;">


<?php

$MessageID=$_GET['id']; 


$sql="SELECT * FROM messages WHERE id='$MessageID' ";
$result=mysql_query($sql);

while($r=mysql_fetch_array($result))
{	

$Recipient=$r["Recipient"];
$Message=$r["Message"];
$Sender=$r["Sender"];
$Subject=$r["Subject"];

echo " 
<form name='reply' action=message_composep.php method='post'> 
<input type='hidden' value='$Sender' name='Recipient'>
<input type='submit' value='Reply' </form><br><br>
From: $Sender <br>
Subject: $Subject
<br><br>
$Message

";
}
?>
<br><br><br><br><br>
<font size="1">
<?php 

$MessageID=$_GET['id']; 

$query="update messages SET new='no' WHERE id='$MessageID'";
$checkresult = mysql_query($query);
if ($checkresult) echo 'this message has been marked as read.';
else echo 'update query failed';

mysql_close();
?>  
</div>
      </td>
    </tr>
  </tbody>
</table>


<?php

include 'footer.php';

?>

 

we've been trying to make it so after you recive a message, then respond, the message you get back has the pervious message converstation...im probably not wording it right but basically i want it to appear like this:

 

12/02/08

user 1: not much

=====================

12/01/08

User 2: whats up?

=====================

12/01/08

User 1: hey

[mrdamien]

Are you tracking what Message ID each message is in reply to?

 

If not, you'll probably need something along these lines:

conversation(
     convoID,
     replyTo,
     messageID
)

 

When a user sends a new message, create a new row.

This row should contain (a new convoID, null replyTo, and the id of the message)

When a user replies to a message, use the convoID from the replying message.

 

This way your data looks like this:

 

(user1/2 and user3/4 are conversing)

messages:
1, user1, 'yo'
2, user2,' hey'
3, user1, 'whats up?'
4, user2, 'not much'
5, user3, 'I need help'
6, user4, 'too bad'

conversations:
1, null, 1 // first conversation, not a reply, message1
1, 1, 2 // first convo, replying to message1, message2
1, 2, 3 // first convo, replying to message2, message3
1, 3, 4 // first convo, replying to message3, message4
2, null, 5 // 2nd convo, not a reply, message5
2, 5, 6 // 2nd convo, replying to message5, message6

 

Find convoID of the message "SELECT * FROM convos WHERE replyID = $messageID"

Find last convoID = "SELECT convoID FROM convos ORDER BY convoID DESC LIMIT 1"

get list of messageIDs in a convo =

"SELECT * FROM convos, messages

INNER JOIN messages

ON convos.messageid = messages.messageid

WHERE convoID = $id"