1
\$\begingroup\$

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

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.

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

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

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.