c_pattle Posted December 1, 2010 Share Posted December 1, 2010 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. Quote Link to comment Share on other sites More sharing options...
dragon_sa Posted December 1, 2010 Share Posted December 1, 2010 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 Quote Link to comment Share on other sites More sharing options...
c_pattle Posted December 1, 2010 Author Share Posted December 1, 2010 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. Quote Link to comment Share on other sites More sharing options...
dragon_sa Posted December 1, 2010 Share Posted December 1, 2010 just try adding it to the statement like so here WHERE " . $column . " >= " . $random_number . " AND category=film Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.