Jump to content
Sign in to follow this  
Lukeidiot

How to order message_groups/messages just like imessage?

Recommended Posts

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?

Share this post


Link to post
Share on other sites

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 by benanamen

Share this post


Link to post
Share on other sites

 

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 by Lukeidiot

Share this post


Link to post
Share on other sites


<?php
foreach ($result as $row)
{
echo htmlentities($row['message']) .'<br>';
}
?>

 

Edited by benanamen

Share this post


Link to post
Share on other sites

 

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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.