Page 1 of 1

Selecting a range of rows and then selecting one at random

Posted: Tue Mar 07, 2006 11:40 pm
by Citizen
Currently, I use this code:

Code: Select all

$sql="SELECT * FROM `accounts`";
$result=mysql_query($sql);
$mnumber = mysql_num_rows($result);
$rnumber = rand(1, $mnumber);
$sql="SELECT * FROM `accounts` WHERE account_id = $rnumber";
I select a random row from the entire list.

What I want to do is select a random row from ONLY the rows with a points variable > 1.

Posted: Tue Mar 07, 2006 11:51 pm
by feyd
once you get your selection down, add "ORDER BY RAND() LIMIT 1" to it.

Posted: Wed Mar 08, 2006 12:19 am
by Citizen
So should my code look like this?

Code: Select all

$sql="SELECT * FROM `accounts` WHERE points > 1 ORDER BY RAND() LIMIT 1";
$result=mysql_query($sql);
$mnumber = mysql_num_rows($result);

Posted: Wed Mar 08, 2006 1:59 am
by s.dot
that will return 1 result, (LIMIT 1) so there's no point in counting the rows with mysql_num_rows. according to your criteria, it will select a random row where your criteria is met.

if you need to get the total number of rows returned, and then select a random one, you could do something like this

Code: Select all

## count the rows
$result = mysql_query("SELECT count(*) FROM `table` WHERE `foo` = '$bar'");
$num_results = mysql_result($result,0);
mysql_free_result($num_results);

## select a random row
$random = mysql_query("SELECT `value` FROM `table` WHERE `foo` = '$bar' ORDER BY rand() LIMIT 1");