嵌套的sql查询，它按每个组返回的有限行进行分组

Trying to get the right query to look through my table which looks like this:

试图通过我的表查看正确的查询，如下所示：

id    fromName    toName    messages
1     user1       me        .......
2     user2       me        .......
3     user1       me        .......
4     user1       me        .......
5     user3       me        .......
6     user2       me        .......

What I want to do is get an array which has all the messages from user1 in an array, user2 in an array, user3...etc. I had a query that did that:

我想要做的是获取一个数组，其中包含来自user1的所有消息，数组中的user2，user3等等。我有一个查询，它做到了：

$query = mysqli_query($link, "SELECT * FROM messages WHERE toName='$to' OR fromName='$to' order by id desc");
$messages = array();
while ($row = mysqli_fetch_assoc($query)) {
    $messages[$row['fromName']][] = $row;
}

Now I want to put a limit on how many rows it gets back...for example a limit of 2 rows from all users. Do I need a nested query using "group by"? This query isn't working:

现在我想对它返回的行数进行限制...例如，所有用户限制为2行。我是否需要使用“分组依据”的嵌套查询？此查询无效：

$query = mysqli_query($link, "SELECT * FROM messages WHERE toName='$to' OR fromName='$to' group by fromName order by id desc limit 2");
//or this one
    $query = mysqli_query($link, "SELECT * FROM messages WHERE toName='$to' OR fromName='$to' AND id IN(SELECT * FROM messages group by fromName)");
$messages = array();
while ($row = mysqli_fetch_assoc($query)) {
    $messages[$row['fromName']][] = $row;
}

Hope this isn't too difficult to solve....Thanks in advance!

希望这不是太难解决....提前感谢！

2 个解决方案

#1

Why not use your original query and run a count on the array before assigning another.

为什么不在分配另一个查询之前使用原始查询并对数组运行计数。

if(count($messages[$row['fromName']]) < 2)
    $messages[$row['fromName']][] = $row;
}

#2

You should really use GROUP BY in your query to get results. You will not need to iterate through the results and formulate an array.

您应该在查询中使用GROUP BY来获得结果。您不需要遍历结果并制定数组。

Updated code:

更新的代码：

$query = mysqli_query($link, "SELECT * FROM messages WHERE toName='$to' OR fromName='$to' GROUP BY fromName");
$senderMessages = mysqli_fetch_assoc($query);

#1