php mysql从数据库中选择和排序消息

时间:2022-01-01 11:15:04

if trying to select all the messages in a database and display it to the users of my site.

如果尝试选择数据库中的所有消息并将其显示给我的站点的用户。

The messages is getting selecting and grouped, but the recent message within each group of user_id's the message is always the first message sent.

消息正在进行选择和分组,但每组user_id中的最近消息消息始终是发送的第一条消息。

my current code is:

我目前的代码是:

$result_messages = mysqli_query($con, "SELECT * FROM users_messages WHERE message_sender='". $_SESSION["login_id"] ."' OR message_receiver='". $_SESSION["login_id"] ."' GROUP BY message_receiver ORDER BY message_id DESC") 
    or die(mysqli_error($con));

It's the first message the user ever sent that is being display but i would like the most recent to be selected within the group of user.

这是用户发送的第一条正在显示的消息,但我希望在用户组中选择最近的消息。

I'm getting all the users messages and the rows of the users are sorted by the most recent Conversation.

我收到所有用户的消息,用户的行按最近的对话排序。

But within the recent conversation it selects the first message sent in the conversation

但在最近的对话中,它选择了对话中发送的第一条消息

2 个解决方案

#1


1  

New answer: What you need is a correlated subquery like this:

新答案:你需要的是一个相关的子查询,如下所示:

SELECT um.* FROM user_messages um 
WHERE um.message_id IN 
(
   (SELECT MAX(message_id) FROM users_messages WHERE 
    message_sender='". $_SESSION["login_id"] ."' OR 
    message_receiver='". $_SESSION["login_id"] ."' GROUP BY message_receiver)
)
ORDER BY um.message_id DESC

#2


0  

Order by id that increments with every insert.

按ID排序,每次插入都会递增。

#1


1  

New answer: What you need is a correlated subquery like this:

新答案:你需要的是一个相关的子查询,如下所示:

SELECT um.* FROM user_messages um 
WHERE um.message_id IN 
(
   (SELECT MAX(message_id) FROM users_messages WHERE 
    message_sender='". $_SESSION["login_id"] ."' OR 
    message_receiver='". $_SESSION["login_id"] ."' GROUP BY message_receiver)
)
ORDER BY um.message_id DESC

#2


0  

Order by id that increments with every insert.

按ID排序,每次插入都会递增。