选择具有Group和If语句以及Order by的两个用户之间的最后一条消息

I am selecting the last message between two users of a chat system. However, though I am able to select it well, I am not able to order the message according to message that have not been read.

This is what my chat table looks like

CREATE TABLE IF NOT EXISTS `mychat_table` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `from_id` int(10) unsigned NOT NULL,
  `to_id` int(10) unsigned NOT NULL,
  `message` text NOT NULL,
  `sent` int(10) unsigned NOT NULL DEFAULT '0',
  `read_statu` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `is_deleted` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `to` (`to_id`),
  KEY `from` (`from_id`),
  KEY `direction` (`is_deleted`),
  KEY `read` (`read_statu`),
  KEY `sent` (`sent`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=58 ;

Now to select the last message between the two users I did this

$recup_id = mysqli_query($connection, "SELECT MAX(`id`) AS id FROM mychat_table WHERE  (`from_id`='$id' OR `to_id`='$id') AND 

(is_deleted!='$id' AND is_deleted >=0) ORDER BY read_statu ASC GROUP BY (IF(`to_id`='$id', `from_id`, `to_id`))    LIMIT 5  ") or die(mysqli_error($connection));

Problem

Normally I thought when I do ORDER BY read_statu ASC it will display the message with the status 0 first and so on and so forth but it does not.

How to display the messages between those users by ordering in such a way that it will first display messages which read_statu=0 before displaying those which read_statu are superior to 0 ?

You are right, that ORDER BY read_status ASC will give you a list ordered by read status, BUT

1. you can not do the ORDER BY before the GROUP BY clause
2. you are using the MAX(id) function, this will return the largest id of the selected group no matter what order you used
3. you are using GROUP BY which will reduce the result to just one entry per user so you will not be able to access ALL last five messages

To actually get the result you want, you could simplify your query in order to display the last five messages giving unread ones precedence:

SELECT * FROM mychat_table 
WHERE (`from_id`='$id' OR `to_id`='$id') AND (is_deleted!='$id' AND is_deleted >=0)
ORDER BY read_statu ASC, id DESC
LIMIT 5

If you want the result tidy for display you could use this

SELECT (IF(`to_id`='$id', `from_id`, `to_id`)) AS contact, message, read_statu 
FROM mychat_table 
WHERE (`from_id`='$id' OR `to_id`='$id') AND (is_deleted!='$id' AND is_deleted >=0)
ORDER BY read_statu ASC, id DESC, contact
LIMIT 5

if you need preference to the grouping and not the read_status, then just place contact at the beginning of the ORDER BY clause:

SELECT (IF(`to_id`='$id', `from_id`, `to_id`)) AS contact, message, read_statu 
FROM mychat_table 
WHERE (`from_id`='$id' OR `to_id`='$id') AND (is_deleted!='$id' AND is_deleted >=0)
ORDER BY contact, read_statu ASC, id DESC
LIMIT 5

I don't have your data to play around with, so please try the order parameter in different order until you have the desired result, this should get you there.

If you just want to see the last message (preferably unread) from each user (only one message per user) and you can live with just having user id and message text (which should be enough for display) then try this:

SELECT DISTINCT from_id, (
    SELECT message FROM mychat_table m2 
    WHERE to_id='$id' AND m2.from_id=m1.from_id
    ORDER BY read_statu ASC, id DESC
    LIMIT 1
) AS message FROM mychat_table m1
WHERE to_id='$id'
LIMIT 5