PHP MySQL select random rows
Solution 1
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.
Solution 2
The best way I've found to select any number of random records is with OFFSET in the query.
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.
$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),
$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.
Solution 3
Never mind, I figured it out :)
Had to use while not for :'D
Endre Hovde
Updated on July 18, 2022Comments
-
Endre Hovde almost 2 years
I have a problem selecting 6 random friends
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
Stuck on this for a while now :/
Thanks for any help :)
-
Endre Hovde almost 14 yearsdo I have to use that query in a num_rows or fetch_array?
-
cbednarski almost 14 yearsOrder by rand is dangerously slow with lots of data.
-
cbednarski almost 14 yearsThere's an article here with details and alternatives: titov.net/2005/09/21/…