Gossamer Forum
Home : General : Perl Programming :

Random row from MySQL column

Quote Reply
Random row from MySQL column
Is it possible to select a random row from a MySQL table? I already have a column selected and Id like to output a random row from that column.

Quote Reply
Re: Random row from MySQL column In reply to
Hi,

Sure.

1. Get the total number of rows:

my $sth = $dbh->prepare ("SELECT COUNT(*) FROM Table");
$sth->execute;
my $total = $sth->fetchrow;

2. Fetch a random row using the offset feature.
my $offset = int (rand * $total);
$sth = $dbh->prepare ("SELECT * FROM Table LIMIT $offset, 1");
$sth->execute;
my $row = $sth->fetchrow_hashref;

Cheers,

Alex

--
Gossamer Threads Inc.
Quote Reply
Re: Random row from MySQL column In reply to
Thanks Alex! Worked like a charm!

Quote Reply
Re: Random row from MySQL column In reply to
I'm using MySQL v3.23.32. There are alot of functions avaiable in the SQL query that I just found out about.
A lot of the functions concern date formating and such but there is a random function that will return random rows without any extra code.
All you have to do is add "ORDER BY rand()" to the end of your SQL query.
Ex: "SELECT * FROM tablename ORDER BY rand() LIMIT 10" will return 10 random rows from the table.

(Anything that requires less code is good for me)
hope this helps,
Joe Hofeditz