random row from MySQL

[Pauls74462 0]

I have the below code or at least a start. I need to select a random row

<?php // Get db connectionsinclude ("config.php");// Make a MySQL Connection$link2 = mysql_connect( $dbhost, $dbuser, $dbpass)or die("Could notconnect: ".mysql_error());mysql_select_db($dbname) or die(mysql_error());// Query the database and get the count $result = mysql_query("SELECT * FROM Members"); $num_rows = mysql_num_rows($result); $NewNum = rand(1, $num_rows);$sql = "SELECT * FROM Members WHERE somefield='something' ORDER BY RAND() LIMIT 1"; // select random row?>

[End User 0]

If you have a small number of rows you can simply ORDER BY RAND() LIMIT 1 and get a row. If you have lots of rows (~50,000 or more), you'll want to use something else:LOCK TABLES foo READ; SELECT FLOOR(RAND() * COUNT(*)) AS rand_row FROM foo; SELECT * FROM foo LIMIT $rand_row, 1; UNLOCK TABLES; Or this, found on greggdev.com (never used it myself, so I can't swear by it):

  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;  }

[Pauls74462 0]

Don't know if it will work, I only have 1 row in my db now, but with the 1 row it works. No sorting is needed.Thanks for all who posted help!

$result = mysql_query("SELECT * FROM Members"); $num_rows = mysql_num_rows($result); $NewNum = rand(1, $num_rows);$result = mysql_query("SELECT * FROM Members where ID = $NewNum");

[End User 0]

Don't know if it will work, I only have 1 row in my db now, but with the 1 row it works. No sorting is needed.

Wait, what? You're selecting a random row from a table with a single row? I think the "randomness" will be somewhat reduced if you do that....

[Pauls74462 0]

Wait, what? You're selecting a random row from a table with a single row? I think the "randomness" will be somewhat reduced if you do that....

Just at the time of the testing, I will have that table filled up data I hope in time.I later put some fake data in it to test, it works as wanted.