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.
Jan 18, 2001, 9:02 AM
Administrator (9387 posts)
Jan 18, 2001, 9:02 AM
Post #2 of 4
Views: 2933
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.
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.
Feb 2, 2001, 12:01 PM
New User (3 posts)
Feb 2, 2001, 12:01 PM
Post #4 of 4
Views: 2913
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
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