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'];

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.

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