Search the Community

Here my code that will display all chat message groups and messages ORDERED BY when the group was created. I am trying to instead order the message_group list by last message received instead (exactly how imessage or any text message works) <?php $sql_messages = mysqli_query($connect, "SELECT DISTINCT group_id FROM message_group WHERE username = '$username' || recipients = '$username' ORDER BY id DESC;"); $num_messages = mysqli_num_rows($sql_messages); if($num_messages == 0){ ?> <div id="messagegroups"> <div align="center" style="padding: 5px;" id="nomsg">You have no messages at this time.</div> <div id="messagegroupholder"> </div> <?php } else { ?> <div id="messagegroups"> <?php while($row_messages = mysqli_fetch_assoc($sql_messages)){ ?> <?php $snm = mysqli_query($connect, "SELECT * FROM message_group WHERE group_id = '$row_messages[group_id]' ORDER BY id DESC"); $rm = mysqli_fetch_assoc($snm); $nm = mysqli_num_rows($snm); $ur = mysqli_query($connect, "SELECT * FROM users WHERE username = '$rm[recipients]' ORDER BY id DESC;"); $rr = mysqli_fetch_assoc($ur); $um = mysqli_query($connect, "SELECT * FROM messages WHERE group_id = '$row_messages[group_id]' ORDER BY id DESC;"); $rrr = mysqli_fetch_assoc($um); ?> <div id="messagegroupholder"> <div class="qo cj ca js-msgGroup groupclick" id="msg" data-id="<?php echo $rm['group_id']; ?>"> <a class="b"> <div class="qf"> <span class="qj"> <img class="cu qh" src="./img/<?php echo $rr['image']; ?>" style="height: 42px; width: 42px;"> </span> <div class="qg"> <strong><?php echo $rr['username']; ?></strong> and <strong><?php echo $nm - 1 ?> others</strong> <div class="aof"> <?php echo substr($rrr['message'], 0, 60); ?> … </div> </div> </div> </a> </div> </div> <?php } ?> <?php } ?> </div> MySQL Structure: -- -------------------------------------------------------- -- -- Table structure for table `messages` -- CREATE TABLE IF NOT EXISTS `messages` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(100) NOT NULL, `message` text NOT NULL, `ipaddress` varchar(100) NOT NULL, `timestamp` int(11) NOT NULL, `group_id` varchar(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=318 ; -- -------------------------------------------------------- -- -- Table structure for table `message_group` -- CREATE TABLE IF NOT EXISTS `message_group` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(100) NOT NULL, `recipients` varchar(255) NOT NULL, `group_id` varchar(20) NOT NULL, `ipaddress` varchar(100) NOT NULL, `timestamp` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=82 ; -- -- Table structure for table `users` -- CREATE TABLE IF NOT EXISTS `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(50) NOT NULL, `email` varchar(50) NOT NULL, `password` varchar(50) NOT NULL, `user_level` int(11) NOT NULL DEFAULT '0', `name` varchar(100) NOT NULL, `image` varchar(100) NOT NULL DEFAULT 'default.png', `profile_background` varchar(255) NOT NULL DEFAULT 'default_background.png', `description` varchar(255) NOT NULL, `went_to_school` varchar(100) NOT NULL, `worked_at` varchar(100) NOT NULL, `lives_in` varchar(100) NOT NULL, `from_originally` varchar(100) NOT NULL, `expires` datetime NOT NULL, `ipaddress` varchar(50) NOT NULL, `browser` varchar(100) NOT NULL DEFAULT 'n/a', `show_email` int(11) NOT NULL DEFAULT '1', `timestamp` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=32 ; Preview of how the data is stored: http://imgur.com/fdG2n0g Recap: So how can I instead order the message_groups by when the last message was received instead of when the last group was created?