Selecting out of database at Random

software development

#1

I want to create a script that selects a row out of my database at random. the problem i’m having, is that i have huge gaps in the id columns. so i can’t just do a complete random search, or it will bring up an empty set. any suggestions?


#2

SQL: SELECT COUNT(*) AS count FROM table WHERE 1;
pseudocode: random = integer(rand * row.count)
SQL: SELECT * FROM table WHERE id > 0 LIMIT random, 1;

:cool: Perl / MySQL / HTML+CSS


#3

okay, that’s not exactly what i wanted, but i was able to cajole it to work…

my next question is this:

if my SELECT statement returns 0 rows, how can i loop the function to re-run itself?


#4

Well, I neglected to make sure the WHERE clause for both statements were the same in my example. Anyways:

[code]#!/usr/local/bin/perl

use CGI qw(:standard);
use DBI;

my $db_hostname = ‘hostname’;
my $db_username = ‘username’;
my $db_password = ‘password’;
my $db_name = ‘database name’;
my $db_table = ‘table name’;
my $db_where = ‘1’; # SQL WHERE clause to use

print
header,
start_html,
h1(‘Random Database Record’);
my $dbh = DBI->connect(“DBI:mysql:$db_name:$db_hostname”, $db_username, $db_password);
if (defined $dbh) {
my ($sth, $count, $random_num, $row, $try_count, $names);

get number of rows

$sth = $dbh->prepare(qq{SELECT COUNT(*) FROM $db_table WHERE $db_where});
if (defined $sth and $sth->execute) {
if (($count) = $sth->fetchrow_array) {
print p(“There are $count rows in $db_name.$db_table”);
$row = undef;
$try_count = 0;

loop until row returned or too many iterations

while (not defined $row and $try_count < 10) {
$random_num = int(rand($count));
$sth = $dbh->prepare(qq{SELECT * FROM $db_table WHERE $db_where LIMIT $random_num,1});
if (defined $sth and $sth->execute) {
$row = $sth->fetchrow_hashref;
$try_count += 1;
}
else {
print p('MySQL error: ’ . DBI->errstr);
last;
}
}
if (defined $row) {
print p(“It took $try_count tries to pick a random row.”);
$names = $sth->{‘NAME’};
my @keys = @$names;
my @values = map { $$row{$_} } @keys;
print
table({border=>1},
caption(“Row # $random_num”),
Tr({-align=>CENTER,-valign=>TOP},
[
th(@keys),
td(@values)
]
)
);
}
else {
print p(“No row returned after $try_count tries.”);
}
}
}
else {
print p('MySQL error: ’ . DBI->errstr);
}
$dbh->disconnect;
}
else {
print p('Unable to connect to database: ’ . DBI->errstr);
}

print end_html;

exit;[/code]
:cool: Perl / MySQL / HTML+CSS


#5

The basic idea in my examples is to figure out how many rows there are first. Then all you have do is select a row not by its value (WHERE clause) by by its position in the list of rows (LIMIT clause). It seems like it would be the obvious method of picking a random item off a list of items.

In the above method, usually the only reason you would get an empty result is if someone deleted a row from the database after you counted and the random row happened to have been the last row. That is if you use the same WHERE clause for both statements so that the results are consistent.

However, if you had happened to read the MySQL Reference Manual, you would have found this under the mathematical operators section:


ORDER BY RAND() combined with LIMIT is useful for selecting a random sample of a set of rows:

mysql> SELECT * FROM table1, table2 WHERE a=b AND c<d -> ORDER BY RAND() LIMIT 1000;-----------------------------------------------

:cool: Perl / MySQL / HTML+CSS