Random row from mySQL DB. [Archive] - FutureQuest Community

View Full Version : Random row from mySQL DB.

ilya

02-21-2000, 03:02 AM

How can i get a random row from mySQL table?
Say, i have a table with 30 records in it. I need to get a random one and print it in browser. How do i randomize using Perl??

dean

02-21-2000, 11:01 AM

I have been doing this for a long time - it is not the most modern way (latest PHP version) - Using the function[nbsp][nbsp]mySQL is a version 2 way of doing things. I will work on a version 3+ method today, but this works just fine

<?
$dbName = "xsitename";
mysql_connect("localhost","xsitename","yourpassword");
Echo "Random from a DB";
echo "
";
$SelectStr = "SELECT * FROM table_name";
$Results =[nbsp][nbsp]MySQL($dbName,$SelectStr);
$RowCount = MySQL_NUMROWS($Results);
echo "There are $RowCount items in the list";
echo "
";
$random_num = rand() % MySQL_NUMROWS($Results);
$display_var[nbsp][nbsp]= MySQL_RESULT($Results,$random_num,"title");
echo "Random pick was $display_var";
?>

Naturally need to change the variables:
"xsitename"
"yourpassword"
table_name
"title"

hope this helps,
Dean

dean

02-21-2000, 11:03 AM

whoops I just saw you said PERL not PHP- I feel so dumb :)

I would have deleted it but there is no delete option under edit

Dean
whoops jumped the gun again

urban

02-21-2000, 11:17 PM

You could also count the rows, generate your random number,
and then use the MySQL LIMIT clause...

my $dbh=MySQL->connect(localhost","xsitename","yourpassword");

$SQL= "select count(*) from table";
my $sth = $dbh->prepare($SQL);
$sth->execute;
my $row_count = $sth->fetchrow_array;
$random_row = int(rand($row_count));

SQL = "select * from table LIMIT $random_row,1";
my $sth = $dbh->prepare($SQL);
$sth->execute;

etc...

This should return only the one row.

ilya

02-22-2000, 01:07 AM

I might be able to convert this to Perl..
Can you tell me if this is right?:
use DBI;

$dbh=MySQL->connect(localhost","xsitename","yourpassword");
print "Random from a DB\n";
print "
";
$SQL= "SELECT * FROM table_name";
$sth=$dbh->prepare($SQL);
$sth->execute;
$RowCount = $sth->rows;
print "There are $RowCount items in the list";
print "
";
$random_num = rand() % MySQL_NUMROWS($Results);
$display_var[nbsp][nbsp]= MySQL_RESULT($Results,$random_num,"title");
print "Random pick was $display_var";

heath

02-26-2000, 11:48 PM

mySQL has many functions - one of which is RAND() whose entire purpose is to return a random row.

See the mysql DOCS...

Heath

urban

02-27-2000, 03:33 PM

Heath is correct, you can use the built-in MySQL RAND() function...

The purpose or the RAND function is not to retrieve a random row, it's purpose is to return random floating-point number.[nbsp][nbsp]However, starting in MySQL version 2.23.3, you can use RAND() in an ORDER BY clause (combined with LIMIT 1) to retrieve a random row.

I don't know about all the servers, but I do know that the FutureQuest MySQL Host (mysql01...), Nine, and Taz are all running version 2.22.26a.[nbsp][nbsp]This version of MySQL does not support the ORDER BY RAND() functionality.

You can still use the RAND() function to help extract a random row, however.

There are probably other ways, but this example appears to work, although it will only work if the table has an AUTO_INCREMENTING PRIMARY KEY...

create table randtest (
[nbsp][nbsp]keycol integer not null auto_increment primary key,
[nbsp][nbsp]valcol varchar(16)[nbsp][nbsp][nbsp][nbsp]
)

select valcol,keycol*0+rand() from randtest as n order by n limit 1;