Select random row using PDO

Question

What do you think of this way to generate a random number from the total rows in a table? I would like to create a page that random.php precisely generates a random ID and shows it.

require 'includes/config.php';
$pdo->query('SELECT id FROM xxxx');
$pdo->resultset();
$total = $pdo->rowCount();
//echo random id
$id = rand(1, $total);
$pdo->query('SELECT * FROM xxxxx WHERE id = :id AND status = :status');
$pdo->bind(':id', $id);
$pdo->bind(':status', 1);
$rand = $pdo->single();
echo $rand['id'];

• 200_success: Please specify which database server(s) you are targeting (e.g. mysql).

Answer 1

This query will return a random id from the DB with one simple SQL statement instead of using PHPs built in rand function.

MySQL:

$pdo->query('SELECT id FROM xxxxx ORDER BY RAND() LIMIT 1');

MS SQL:

$pdo->query('SELECT id FROM xxxxxx order by NEWID()');

I'm not entirely sure what database you're using, but this would eliminate a few lines of code.

Also, I'm not sure if you're using a database class to wrap around PHPs PDO class but I generally build an array and store my bound parameters in it then execute it. Looks a little neater.

• Marco: I wanted to avoid using rand () because I read that puts us too long to load if the lines are many, which is the best way?
• jon.r: You can use mt_rand which is supposedly 4 times faster (php.net/manual/en/function.mt-rand.php) . However I would rely on your databases native random function to return the ID. As @webNeat said, you are not guaranteed to get an actual ID from your current code if you ever delete from your 'xxxxxxx' table.
• Marco: Hello, I tried with mt_rand () but it does not work, it returns a blank page.
• jon.r: Why not use the random function of your database to return the ID? I don't understand why you're relying on PHP to generate a potentially inaccurate ID.

Answer 2

You are supposing that ids is the table are 1, 2, 3, ... But what if a row was deleted ? Your code can give you an id which is not in the table in this case; because you're generating based on the rows count.

It will be better to save all ids in an array then take a random element from it or just use the RAND() of SQL as @Waragi suggested on his answer