Get a Random Row from Database

November 29th, 2008 by Peter Anselmo Leave a reply »

I had an app the other day that required a single random row to be displayed from the database. It’s an easy task, but I tripped on it for a second. Here was my impulse:

//Get the total number of rows from the MySQL database
$query = "SELECT COUNT(*) as total FROM table";
$result = mysql_query( $query);
$rows = mysql_fetch_assoc( $result );

//Let PHP choose a random row
//pick a number between 1 & the total
$row = rand(1, $rows['total'])

//Now get that row
$query = "SELECT * FROM table LIMIT($row, 1)";
$result = mysql_query( $query);

After typing that out, I realized that I had take the long way round the problem. Very similar to taking the Misty Mountain Path rather than the Mines of Moria. Here’s a better way:

//Get a random row from the database
$query = "SELECT * FROM table ORDER BY RAND() LIMIT 1";
$result = mysql_query( $query );

Much better. Gimley the dwarf would be proud

Leave a Reply