Random MySQL

edraynham

How do you pick out a random table line?

[deleted]

SELECT *
FROM table
ORDER BY rand()
LIMIT 1;

Note: this is a MySQL thing, real database engines don't do this.

edraynham

Thanks Vincent!

Is this a recent thing or has it been around for ages?

[deleted]

it's been around for a few months/a year.

Anon

just to show you another way of doing it.. (for more understanding)

$sql = "select * from table";
$result = mysql_query($sql, $db);
$rows = mysql_num_rows($result);

$feild1 = mysql_result($result, rand() % $rows, 0);

like that...

-f0d

Anon

actually..

do it like this..

$rand = rand() % $rows;
$feild1 = mysql_result($result, $rand, 0);

then the rand will stay the same...

[deleted]

Note that this method is a very VERY uneconomical (read: bad) way of doing it.
"SELECT * FROM table" will select ALL rows, while you only need one. (imagine doing that on a 5 million-row table)

Istead, select all the record-IDs, use PHP's randomize functions to grab a random ID, and then select the record with that ID.

Anon

Note that the ways that have been shown by vincent and f0d do 2 different things.

vincent's way simply randomizes the rows that get send back to your result from MySQL.

f0d's picks a random row out of whatever rows MySQL sent back to the result.

[deleted]

Quite correct.
There are three methods to do this:

f0d's method is: select all records from the table and fetch a random record from thr resultset.
This means that the entire table is downloaded to your script. (do not do this if you have 50million rows. Heck, not even with 100k rows (100k times 250 bytes = 25Megabytes of data!)
Tell MySQL to select all records and order them by a random number and retreive only the first record.
SELECT * FROM table ORDER BY rand() LIMIT 1;
MySQL will fetch all records from the table, and order them by a random number. That means that the records are in random order. The LIMIT 1 will make sure that your script receives exactly one record, wich will be random
Select all available record's Primary-key values, pick a random one from the resultset, and do a second query to fetch the record that has that ID.
This means you script will receive all the ID's and issue a second query to actually fetch the desired record, but the datbase does not have to do any ordering, and the resultset you get is very small.

I suggest you try the various methods and see which one works best.