MySQL Random

[c_pattle]

Is there anyway in MySQL you can select a random record in a table?  I normally do this by generating a random number first and then using that number to find a record but was wondering if there is an easier way.  Thanks for any help. 

[dragon_sa]

From greggdev.com

<?php

  //CODE FROM WWW.GREGGDEV.COM

  function random_row($table, $column) {

      $max_sql = "SELECT max(" . $column . ") 

                  AS max_id

                  FROM " . $table;

      $max_row = mysql_fetch_array(mysql_query($max_sql));

      $random_number = mt_rand(1, $max_row['max_id']);

      $random_sql = "SELECT * FROM " . $table . "

                     WHERE " . $column . " >= " . $random_number . " 

                     ORDER BY " . $column . " ASC

                     LIMIT 1";

      $random_row = mysql_fetch_row(mysql_query($random_sql));

      if (!is_array($random_row)) {

          $random_sql = "SELECT * FROM " . $table . "

                         WHERE " . $column . " < " . $random_number . " 

                         ORDER BY " . $column . " DESC

                         LIMIT 1";

          $random_row = mysql_fetch_row(mysql_query($random_sql));

      }

      return $random_row;

  }

  

  //USAGE

  echo '<pre>';

  print_r(random_row('YOUR_TABLE', 'YOUR_COLUMN'));

  echo '</pre>';

?>

 

there is a shorter way to do it but large tables will take a long time to go through this speeds the process up

[c_pattle]

Thank you.  Although I don't think this will work.  I should explain better.  I want to get a random record based on "if category=film" rather than the whole dataset.  I'm not sure if there is an easy way to do it. 

[dragon_sa]

just try adding it to the statement like so here

 

 WHERE " . $column . " >= " . $random_number . " AND category=film