I have a messaging system on my site and I'm trying to group the messages into conversations, similar to how Facebook does. The following is the PHP code I'm using:
<form name="myform" action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post" enctype="multipart/form-data">
<a href="#new_message" data-toggle="modal" class="btn btn-primary pull-right">New Message </a> <input type="submit" name="deleteBtn" class="btn btn-danger pull-right" id="deleteBtn" value="Delete Selected" />
<br /><br /> <?php
///////////End take away///////////////////////
// SQL to gather their entire PM list
$sql = mysqli_query($db_conx,"SELECT * FROM (SELECT * FROM private_messages WHERE to_id='$my_id' AND recipientDelete='0' ORDER BY time_sent DESC) AS tmp_table GROUP BY LOWER(from_id)");
while($row = mysqli_fetch_array($sql, MYSQLI_ASSOC)){
$item_date = $row["time_sent"];
$convertedTime = ($myObject -> convert_datetime($item_date));
$date = ($myObject -> makeAgo($convertedTime));
//$date = strftime("%b %d, %Y",strtotime($row['time_sent']));
if($row['opened'] == "0"){
$textWeight = 'msgDefault';
} else {
$textWeight = 'msgRead';
}
$fr_id = $row['from_id'];
// SQL - Collect username for sender inside loop
$ret = mysqli_query($db_conx,"SELECT id, username, firstname, lastname FROM bs_mem_base389 WHERE id='$fr_id' LIMIT 1");
while($raw = mysqli_fetch_array($ret, MYSQLI_ASSOC)){ $Sid = $raw['id']; $Sname = $raw['username']; $Sfirst = $raw['firstname']; $Slast = $raw['lastname'];
if ($Sfirst != "") {$Sname = "$Sfirst $Slast";} } //}
?>
<a href="message.php?id=<?php echo $fr_id; ?>" class="<?php echo $textWeight; ?>">
<p class="pull-right"><?php echo $date; ?> <input type="checkbox" name="cb<?php echo $row['id']; ?>" id="cb" value="<?php echo $row['id']; ?>" /></p>
<h4><?php echo $Sname; ?></h4>
<p><?php echo stripslashes(wordwrap(nl2br($row['message']), 54, "
", true)); ?></p></a>
<?php
}// Close Main while loop
?></form>
It's grouped them all together just fine, but they are not in the correct order. They're all jumbled. Any idea on how to order them so that the convo with the most recent message is first?
change this:
$sql = mysqli_query($db_conx,"SELECT * FROM (SELECT * FROM private_messages WHERE to_id='$my_id' AND recipientDelete='0' ORDER BY time_sent DESC) AS tmp_table GROUP BY LOWER(from_id)");
to this:
$sql = mysqli_query($db_conx,"SELECT * FROM (SELECT * FROM private_messages WHERE to_id='$my_id' AND recipientDelete='0' ORDER BY time_sent DESC) AS tmp_table GROUP BY LOWER(from_id) order by time_sent desc");
In MySQL, the GROUP BY performs an implicit ORDER BY. Sometimes, we can improve performance by suppressing the automatic sort, by adding an ORDER BY NULL.
But, if you need the rows returned in a specific sequence, then add an ORDER BY to the query.
Also note, due to way MySQL processes inline views, the resultset for the inline view gets materialized as a temporary MyISAM table (MySQL calls it a "derived table").
I don't believe your query is returning the resultset you are expecting.
I'm going to assume that one side of a "convo" is identified by the tuple (to_id,from_id), and the corresponding side of the convo is also identified by inverse tuple (from_id,to_id).
I'm also going to assume that you want to return all the private messages, and that by "grouped by" you mean that you want each convo to appear sorted together, with the convo with the latest private message listed first.
We can first get the latest "time_sent" of messages sent to us from each "from_id", with a query like this:
SELECT n.from_id
, n.to_id
, MAX(n.time_sent) AS latest_time_sent
FROM private_messages n
WHERE n.to_id = '$my_id'
AND n.recipientDelete='0'
GROUP BY n.from_id, n.to_id
ORDER BY MAX(n.time_sent) DESC, n.from_id DESC
If you are looking at this, and thinking, hey, we don't really need to return the to_id column, because it's always going to be the same value, you are right. As long as we can insure that $my_id doesn't contain some clever string, such as:
$my_id = "14' OR 'a'='a";
Of course, $my_id could contain even more nefarious SQL text, which we would probably really want to avoid including in our statement. But we'll set the SQL Injection issue aside, and focus on the question you asked.
Note that if we also wanted to consider messages "from" us, in terms of the latest_time_sent for the convo, (the inverse side, this query could be modified to do that. But, for now, we're assuming that we're looking only at the latest time a message was sent to us.
If we want to get just the latest message sent to us in each convo, the "trick" is to join the resultset from that previous query with the messages table, something like this:
SELECT p.*
FROM private_messages p
JOIN ( SELECT n.from_id
, n.to_id
, MAX(n.time_sent) AS latest_time_sent
FROM private_messages n
WHERE n.to_id = '$my_id'
AND n.recipientDelete='0'
GROUP BY n.from_id, n.to_id
ORDER BY MAX(n.time_sent) DESC
) o
ON o.from_id = p.from_id
AND o.to_id = p.to_id
AND o.time_sent = p.latest_time_sent
WHERE p.recipientDelete='0'
ORDER BY o.latest_time_sent DESC, o.from_id DESC
If we wanted to get all the messages in those convo, the trick is to match the rows in the message table with convo from the inline view, so that each row from messages gets "tagged" with the "latest_time_sent" for the convo.
Something like this:
SELECT p.*
FROM private_messages p
JOIN ( SELECT n.from_id
, n.to_id
, MAX(n.time_sent) AS latest_time_sent
FROM private_messages n
WHERE n.to_id = '$my_id'
AND n.recipientDelete='0'
GROUP BY n.from_id, n.to_id
ORDER BY MAX(n.time_sent) DESC
) o
ON ( o.from_id = p.from_id AND o.to_id = p.to_id )
OR ( o.from_id = p.to_id AND o.to_id = p.from_id )
WHERE p.recipientDelete='0'
ORDER BY o.latest_time_sent DESC, o.from_id DESC, p.time_sent DESC
(Note that we are repeating the predicate on the recipientDelete column; and we are "matching" the rows from message to the rows from the inline view on the (from_id,to_id) tuple, as well as the inverse, so we get both sides of the convo.
Then, when we go to sort the rows, we do it first based on the latest_time_sent. Next, we order based on the identifier of the convo, that (from_id,to_id) tuple. (Now it should be more clear the real reason we included the to_id from the inline view. (The other option would be reference $my_id again, in the join predicates.)
Finally, once the rows are ordered by convo, with the one with latest_time_sent first, we also order by time_sent.