PHP MySQL 选择随机行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3340061/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PHP MySQL select random rows
提问by Endre Hovde
I have a problem selecting 6 random friends
我在随机选择 6 个朋友时遇到问题
This is the query I've got so far:
这是我到目前为止的查询：
$result = num_rows("SELECT * FROM friends WHERE member_id = '".$_SESSION['userid']."'");
if($result >= 6) {
$f_num = 6;
} else {
$f_num = $result;
}
for($i = 1; $i <= $f_num; $i++) {
$q_get_member_friends = mysql_query("SELECT * FROM friends WHERE member_id = '".$_SESSION['userid']."' ORDER BY rand() LIMIT 1");
$r_get_member_friends = mysql_fetch_array($q_get_member_friends);
echo $r_get_member_friends['friend_with'];
}
I want to select 6 random friends if the logged in user has more or equal to 6 friends
如果登录用户的好友多于或等于 6 个，我想随机选择 6 个好友
Stuck on this for a while now :/
现在坚持了一段时间：/
Thanks for any help :)
谢谢你的帮助 ：）
回答by OMG Ponies
If you use:
如果您使用：
SELECT *
FROM friends
WHERE member_id = '".$_SESSION['userid']."'
ORDER BY rand()
LIMIT 6
If the person only has 3 friends, the query will only show those three - it doesn't mean that the query will always return six rows.
如果此人只有 3 个朋友，则查询将仅显示这三个 - 这并不意味着查询将始终返回六行。
回答by Jan Michaels
The best way I've found to select any number of random records is with OFFSET in the query.
我发现选择任意数量的随机记录的最佳方法是在查询中使用 OFFSET。
Let's say you want 6 random records, so I'll borrow from an answer above and count the total number of friends in the database.
假设您想要 6 条随机记录，那么我将借用上面的答案并计算数据库中的朋友总数。
$sql = mysql_query("SELECT COUNT(*) AS total FROM friends WHERE member_id='". $_SESSION['userid'] ."'");
$get_count = mysql_fetch_array($sql); // Fetch the results
$numfriends = $get_count['total']; // We've gotten the total number
Now we'll get the 6 random records out of the total above (hopefully it's > 6),
现在我们将从上面的总数中取出 6 个随机记录（希望大于 6），
$query = mysql_query("SELECT * FROM friends WHERE member_id='". $_SESSION['userid'] ."' LIMIT 6 OFFSET " . (rand(0, $numFriends));
while ($rows = mysql_fetch_array($query))
{
/// show your $rows here
}
Using OFFSET may not be the best or most efficient, but it's worked for me on large databases without bogging them down.
使用 OFFSET 可能不是最好或最有效的，但它在大型数据库上对我有用而不会使它们陷入困境。
回答by Endre Hovde
Never mind, I figured it out :)
Had to use whilenot for:'D
没关系，我想通了:)
不得不使用whilenot for:'D
回答by Marcus Adams
Instead of SELECT *at the beginning, try SELECT COUNT(*)and use the actual return value instead of num_rows().
而不是SELECT *在开始时，尝试SELECT COUNT(*)使用实际的返回值而不是 num_rows()。
Your loop could generate duplicates. I would suggest trying OMG Ponies answer.
您的循环可能会生成重复项。我建议尝试 OMG Ponies 的答案。
There is a whole chapter about random selection in the book SQL Antipatterns.
SQL Antipatterns一书中有一整章是关于随机选择的。
回答by PIM
First select the number of friends that the user has:
首先选择用户拥有的好友数量：
"SELECT COUNT(*) as numFriends FROM friends WHERE member_id='".$_SESSION['userid']."'
...put that into a variable, let's call it "$numFriends" Then:
...把它放到一个变量中，我们称之为“$numFriends” 然后：
for($z=0;$z<6;$z++)
{
$randomFriendIndex = rand(1,$numFriends);
//Get the friend at that index
}
回答by Dagg Nabbit
change limit 1to limit 6on the eighth line.
更改limit 1为limit 6第八行。
