How to order message_groups/messages just like imessage?

[Lukeidiot]

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?

[benanamen]

Give this a shot. Without a datadump I couldnt test it.

SELECT
u.username,
u.email,
u.`password`,
m.message,
g.group_id
FROM
users AS u
INNER JOIN messages AS m ON m.username = u.username
INNER JOIN message_group AS g ON g.username = m.username
GROUP BY
g.group_id
ORDER BY
m.`timestamp` ASC

Edited March 23, 2016 by benanamen

[Lukeidiot]

 

Give this a shot. Without a datadump I couldnt test it.

SELECT
u.username,
u.email,
u.`password`,
m.message,
g.group_id
FROM
users AS u
INNER JOIN messages AS m ON m.username = u.username
INNER JOIN message_group AS g ON g.username = m.username
GROUP BY
g.group_id
ORDER BY
m.`timestamp` ASC

Can you explain how I would use this in a while loop a bit? I added WHERE u.username = '$username' as well.

 

for example: $row['m.message']; ?

Edited March 23, 2016 by Lukeidiot

[benanamen]

<?php

    foreach ($result as $row)

        {

        echo htmlentities($row['message']) .'<br>';

        }

?>

 

Edited March 23, 2016 by benanamen

[Lukeidiot]

 

Give this a shot. Without a datadump I couldnt test it.

SELECT
u.username,
u.email,
u.`password`,
m.message,
g.group_id
FROM
users AS u
INNER JOIN messages AS m ON m.username = u.username
INNER JOIN message_group AS g ON g.username = m.username
GROUP BY
g.group_id
ORDER BY
m.`timestamp` ASC

 

I dont think this is working correctly.

 

Im willing to paypal you some money if you could help a bit more. Skype: Lukecode