# Select random row using PDO

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'];  • Please specify which database server(s) you are targeting (e.g. mysql). – 200_success Apr 29 '16 at 10:10 ## 2 Answers 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.

• 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? – Marco Apr 28 '16 at 13:40
• 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. – Waragi Apr 28 '16 at 13:44
• Hello, I tried with mt_rand () but it does not work, it returns a blank page. – Marco Apr 28 '16 at 15:13
• 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. – Waragi Apr 28 '16 at 16:21

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