Random Row From mySQL Table

[Anon]

Hi,
I'm using PHP3, does anyone know how I can pull a random row from a mySQL table?

Girish Gupta

PS - Can you please give me code, not just tell me what to do as I am a beginner!

[Anon]

You have to obtain the number of the last row in your database table:
the query can look like this:
SELECT max(id) FROM your_table_name

id - name of the column where you store the number

after that you have to generate a random number between 0 and your max number id

function random($max) {
$x=rand();
$y=getrandmax();

$r=$x/$y * ($max-1);

$r=round($r++);
return $r;
}
$rand_number=random(the_number_of_rows_in_your_table);

Using the function random(max_id_from_your_table) you can obtain random number between 0 and max number in your table. So you can use this number and get the respective row.

SELECT * FROM your_table_name WHERE id='$rand_number'

Hope it's enough.

SaS.

[Anon]

Actually I found a nifty way to get a random row from the database using only MySQL whilst hunting for a similar problem

---

Here is the code for the newbies

Say you have a table named people with name and age. You want a random name to pop up.

SQL should be

Select name, (age*0+RAND()) as random_col FROM people ORDER BY random_col

if you want to have just one come out add
LIMIT 0,1

---

Understanding to learn

trick is the MYSQL optimiser works to strip out ORDER BY if you dont refference the RAND() to some part of the table you wish to order. Simply having select name, rand() order by rand() wont work, but effectively times it by 0 (the age*0 part above) and MYSQL loves it.

this is really simple and will give you one or many randomly sorted rows, enjoy it!.

---