PHP select random id from database

Question

I want to select a random userid from this query where level = 1 (Normal user) and alive = Yes (User is alive and can play)

$getaid = $sql->query("SELECT userid FROM `users` WHERE `level`='1' AND `alive`='yes'");

I know I can use

$totalusers = mysql_num_rows($getaid);
$randomuserid = rand(1,$totalusers);

to select a random userid but in that code there's a chance that it select a user that is dead (alive = No) or a staff member (level >= 1). is there a better way I can select a random userid without a chance to grab staff members/dead members?

TJL · Accepted Answer · 2015-10-04 12:58:14Z

4

You can do

"SELECT userid FROM `users` WHERE `level`='1' AND `alive`='yes' ORDER BY RAND() LIMIT 1"

answered Oct 4, 2015 at 12:58

TJL

1798 bronze badges

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

1 Comment

Vladimir Ch Over a year ago

order by rand() too slow on big tables !

Mureinik · Accepted Answer · 2015-10-05 08:24:40Z

2

Instead of selecting all the records and trying to choose a random one in PHP's side, let the database do the heavy lifting for you:

SELECT   userid
FROM     `users`
WHERE    `level` = '1' AND `alive` = 'yes'
ORDER BY RAND()
LIMIT    1

edited Oct 5, 2015 at 8:24

answered Oct 4, 2015 at 12:56

Mureinik

316k54 gold badges400 silver badges405 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2015-10-04 12:56:03Z

Your method is about the worst of all possible worlds -- you are bringing all the data back from the database to an array in PHP and then choosing a random value there. But, it should work, because you are only bringing back the appropriate users.

One way of doing this in the database that is simple enough is:

SELECT userid
FROM `users`
WHERE `level`='1' AND `alive` = 'yes'
ORDER BY rand()
LIMIT 1;

However, if you have more than a few hundred rows (or a few thousand), this starts to get too expensive. There are definitely other methods, but bringing all the data back is not a good idea.

One simple fix is to get approximately 100 rows and then randomize them:

SELECT userid
FROM `users` CROSS JOIN
     (SELECT COUNT(*) as cnt FROM users  `level` = '1' AND `alive` = 'yes') x
WHERE `level` = '1' AND `alive` = 'yes' AND
      rand() < 100 * 1 / x
ORDER BY rand()
LIMIT 1;

For performance, you want an index on `users(level, alive).

Collectives™ on Stack Overflow

PHP select random id from database

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related