[mysql] Selecting Random Selection Of Rows From A Database Table

The begining of this story is a PHPNuke module. The module has a block that picks a random item from a database table and displays it. The content of the table never changes. Here is how the programmer of the block does it:

He knows that there are 6899 rows because the last id in the table has a value of 6899. So with the "rand" PHP function, he picks a random number from 1 to 6899 and then queries the database table with a WHERE clause that checks if the value of the id is equal to the picked number:

srand(time());
$random = (rand(1,6899));
$res = mysql_query ("select * from my_table where id=$random");

The problem here is that because of some deleted rows (he has forgotten he deleted them) in the table, the id is not a continuous sequence from 1 to 6899 and that's why some times the picked number (as a value of the id) not exists in the table and the PHPNuke block displays an empty box.

The RAND function in MySQL is precisely there to prevent this kind of problem. Instead of picking a random id with a PHP code, let the server do it for you:

SELECT * FROM my_table ORDER BY RAND() LIMIT 1;

This query returns a random row from the table my_table, no matter how many rows there are and no matter if the id is a continuous sequence of numbers or not. With this query you are sure you get a random row every single time.

Conclusion:
Never reinvent the wheel.