PHP MySQL select random rows

Question

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 :)

A bit off topic but you can save time by using braces in strings. Like instead of '".$_SESSION['userid']."'" You can use ` '{$_SESSION['userid']}'"` — Shubham
– Shubham, Commented Jul 27, 2010 at 1:12
@Shubham If it's only a one-dimensional array, it's even enough to write "'$_SESSION[userid]'". :) — deceze
– deceze ♦, Commented Jul 27, 2010 at 1:55

OMG Ponies · Accepted Answer · 2010-07-27 01:06:59Z

25

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.

answered Jul 27, 2010 at 1:06

community wiki

OMG Ponies

Sign up to request clarification or add additional context in comments.

2 Comments

cbednarski Over a year ago

Order by rand is dangerously slow with lots of data.

cbednarski Over a year ago

There's an article here with details and alternatives: titov.net/2005/09/21/…

Subin · Accepted Answer · 2016-02-14 17:36:21Z

5

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.

edited Feb 14, 2016 at 17:36

Subin

3,5731 gold badge36 silver badges65 bronze badges

answered Mar 5, 2013 at 14:24

Jan Michaels

511 silver badge1 bronze badge

1 Comment

Furkan ozturk Over a year ago

It may not have any record after some rows if you use offset as random. I think thats better to get the results as array and then we can use shuffle method to mix the elements of the array. In order to get first 6 elements, we can use array_slice($array, 0, 6);

nfc · Accepted Answer · 2010-07-27 01:32:04Z

1

Never mind, I figured it out :)
Had to use while not for :'D

answered Jul 27, 2010 at 1:32

nfc

6443 gold badges7 silver badges22 bronze badges

Comments

PIM · Accepted Answer · 2010-07-27 01:07:25Z

0

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:

for($z=0;$z<6;$z++)
{
   $randomFriendIndex = rand(1,$numFriends);
   //Get the friend at that index
}

answered Jul 27, 2010 at 1:07

PIM

3141 silver badge3 bronze badges

1 Comment

nfc Over a year ago

do I have to use that query in a num_rows or fetch_array?

Dagg Nabbit · Accepted Answer · 2010-07-27 01:08:00Z

0

change limit 1 to limit 6 on the eighth line.

answered Jul 27, 2010 at 1:08

Dagg Nabbit

77k19 gold badges115 silver badges142 bronze badges

Comments

Marcus Adams · Accepted Answer · 2010-07-27 01:48:45Z

0

Instead of SELECT * at the beginning, try SELECT COUNT(*) and use the actual return value instead of num_rows().

Your loop could generate duplicates. I would suggest trying OMG Ponies answer.

There is a whole chapter about random selection in the book SQL Antipatterns.

answered Jul 27, 2010 at 1:48

Marcus Adams

54k9 gold badges98 silver badges145 bronze badges

Collectives™ on Stack Overflow

PHP MySQL select random rows

6 Answers 6

2 Comments

1 Comment

Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

2 Comments

1 Comment

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related