试图获得正确的查询来查看我的表,如下所示:
id fromName toName messages
1 user1 me .......
2 user2 me .......
3 user1 me .......
4 user1 me .......
5 user3 me .......
6 user2 me .......
我想做的是获取一个数组,该数组包含来自数组中的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;
}
现在我想限制它返回的行数......例如,所有用户的限制为 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;
}
希望这不会太难解决....提前感谢!
为什么不使用原始查询并在分配另一个数组之前对数组运行计数。
if(count($messages[$row['fromName']]) < 2)
$messages[$row['fromName']][] = $row;
}
您应该在查询中使用GROUP BY
来获取结果。您无需遍历结果并制定数组。
更新的代码:
$query = mysqli_query($link, "SELECT * FROM messages WHERE toName='$to' OR fromName='$to' GROUP BY fromName");
$senderMessages = mysqli_fetch_assoc($query);