Random Row

[Anon]

Hello,

I am trying to implement something that's similar to those HotOrNot.com & other similar sites i.e. I need to randomly retrieve a row from a large table many many consecutive times.

I've tried something like

SELECT * FROM Table ORDER BY Rand() LIMIT 1

But somehow this doesn't really work. Weirdly, I would often get the same row drawn consecutively ... which isn't too random! So thinking that MySQL's Rand() function doesn't really work too well, I tried another approach.

First, grab the # of rows in the Table ... and then with this $rowTotal ... do something like

mt_srand((double) microtime() * 1000000);
$rand_id = mt_rand(1,$rowTotal);

then ... SELECT * FROM Table WHERE id=$rand_id

A random row would be retrieved. Now, however, if I need to grab a random row with an ADDITIONAL CONDITION, e.g. WHERE name=$name, say

SELECT * FROM Table WHERE name=$name ORDER BY Rand() LIMIT 1

How can i do this in the mt_rand() way???

Or is there any better way? How did those HotOrNot.com site do this similar tasks?

Thank you very much !!!

[fandelem]

currently i have a random function pretty much just like you suggested first:

    else if ( $do == "random" ) {

    $sql = "SELECT id FROM {$DB_CONF["lyrics"]} order by rand() limit 1";
    // execute query
    $sql_result = mysql_query($sql) or die ("Couldn't execute query. (search()) ".mysql_error());

           while ($row = mysql_fetch_array($sql_result)) {
                  $songid = $row['id'];
           }

           redirect('0',"do=view&songid=$songid",'view.php');

    }

however, i think your current approach needs more refining because what happens if it selects an out of bounds number (ie. a number beyond what your database id numbers have)?

anyways, to answer your question, i think this might work:

SELECT * FROM Table WHERE name=$name AND id=$rand_id

if anyone does have a really good way on doing randomness, i'd like to see it as well :o)