php - 检索与输入的用户名匹配的所有用户的最后一条消息

Question

我想检索最后发送或接收的与通过 AJAX 在文本框中输入的用户名相匹配的消息，但我无法编写正确的 MySQL 查询。

这是我的代码:

    $name=$_GET['name']; 
    $query_get_user_id = "SELECT id from login WHERE email = '".$_SESSION['login_user']."'"; 
    $result_get_userid=$mysqli->query($query_get_user_id);
    $user_row = mysqli_fetch_array($result_get_userid);
    $user_id=$user_row['id'];

    $query_second_user_id = "SELECT id from login WHERE name LIKE '".$name."%'"; 
    $result_second_get_userid=$mysqli->query($query_second_user_id);
    $user_second_row = mysqli_fetch_array($result_second_get_userid);
    $second_user_id=$user_second_row['id'];

    $query_get_all_users ="SELECT DISTINCT sender_id,receiver_id from message WHERE ((sender_id='".$user_id."' AND receiver_id='".$second_user_id."') OR (sender_id='".$user_id."' AND receiver_id='".$second_user_id."')) order by timestamp desc";

   $result_get_all_users=$mysqli->query($query_get_all_users);
   while($retrieve_user_row = mysqli_fetch_array($result_get_all_users)){  

这是我的表结构:

Answer 1

抱歉，我弄错了，我只为登录用户或一个用户开发了查询，现在我明白你的意思了。

请尝试此更新的查询。我在本地数据库上测试了它并且按预期工作。

<小时/>

    SELECT *
FROM message mm
INNER JOIN
    (SELECT sender, MAX(createdtime) AS MaxDateTime
    FROM message
    WHERE sender IN 
    (select id from user where name like 'search_filter%')
    GROUP BY sender) groupedmm
ON mm.sender = groupedmm.sender 
AND mm.createdtime = groupedmm.MaxDateTime;