Select Random Row from SQL Using PHP

Question

I want to request 5 random rows from my SQL table using php. for instance, i need to:

mysql_query("SELECT * FROM catalogue >> not sure what goes here << LIMIT 5");

Answer 1

SELECT * FROM catalogue order by RAND() LIMIT 5

Edit:

For what its worth, Please note that using rand() on a table with large number of rows is going to be slow. This can actually crash your server.

Some Solution:

MediaWiki uses an interesting trick (for Wikipedia's Special:Random feature): the table with the articles has an extra column with a random number (generated when the article is created). To get a random article, generate a random number and get the article with the next larger or smaller (don't recall which) value in the random number column. With an index, this can be very fast. (And MediaWiki is written in PHP and developed for MySQL.)

But this is for a single random row only.

Answer 2

Assuming the table has AUTO INCREMENT on you could get the biggest ID with

SELECT id FROM catalogue ORDER BY id DESC LIMIT 1

and the smallest ID with

SELECT id FROM catalogue ORDER BY id ASC LIMIT 1

making it possible to do this

$randomId = mt_rand($smallestId, $biggestId);
$randomSql = mysql_query("SELECT * FROM catalogue WHERE id='$randomId'");

For five rows you could create the random ID five times.

Answer 3

If you're selecting random rows from a very large table, you may want to experiment with the approaches in the following link:

http://akinas.com/pages/en/blog/mysql_random_row/

note: just to share other options with everyone