Selecting a range of rows and then selecting one at random

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Citizen
Forum Contributor
Posts: 300
Joined: Wed Jul 20, 2005 10:23 am

Selecting a range of rows and then selecting one at random

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

once you get your selection down, add "ORDER BY RAND() LIMIT 1" to it.
Citizen
Forum Contributor
Posts: 300
Joined: Wed Jul 20, 2005 10:23 am

Post 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);
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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");
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
Post Reply