CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: Intermediate:
SQL - random

 



pu_nk
journeyman

Jan 17, 2001, 5:39 PM

Post #1 of 7 (795 views)
SQL - random Can't Post

Hey,
I am working on a project that requires random links but for some reason i am having problems using the mySQL database. SO does anyone have any ideas on how to do the following

How would i go about reading the whole database and getting 1 RANDOM row out of it and then put that row into an array called @new_row for example.

Hope i am clear enough on what i need assitance on.
Thanks,
Drew



sleuth
Enthusiast / Moderator

Jan 18, 2001, 1:17 AM

Post #2 of 7 (788 views)
Re: SQL - random [In reply to] Can't Post

 This works with flat file, you'll just have to change it however you need to get it working with SQL I guess.

$filename = "test.db";
($total = `wc -l $filename`) =~ s!\D+!!g;;
$rand=int(rand($total));
open(data, "<$filename");
while(<data>){
if ($. == $rand){
$myRandomLine = "$_";
last;
}
}
close(data);

Now $myRandomLine is the random line.

Sleuth



Jasmine
Administrator

Jan 18, 2001, 1:18 AM

Post #3 of 7 (788 views)
Re: SQL - random [In reply to] Can't Post

If you actually just need to retrieve one row, there's no need to read the whole database not to mention it'll murder your server memory if it's a large database. (All of this assumes that you're using DBI)

Simple 2-step process:

First, get the total count of records by using the COUNT statement:


Code
    my $query = $DBI->prepare(SELECT COUNT(*) FROM tablename); 
$query->execute();
my ($total) = $query->fetchrow_array;
return $total;

(Of course, you should add error checking to those statements)

Next, get a random number from the first to last number in the total and use the LIMIT statement:


Code
    my $offset  = int rand $total; 
my $query = $DBI->prepare ( "SELECT * FROM tablename LIMIT $offset, 1");
$query->execute() or die $DBI::errstr;
my $rec = $query->fetchrow_hashref;
$query->finish();

$rec now contains a hash reference of a single, random record.

Hope this helps!



pu_nk
journeyman

Jan 18, 2001, 1:51 AM

Post #4 of 7 (785 views)
Re: SQL - random [In reply to] Can't Post

Thank Jasmin !!

That looks perfect.. I did get it going but i was looking through the whole database and i knew that it would kill the servers memory.

The code you showed me looks like exactly what i needed.

Thanks again



sleuth
Enthusiast / Moderator

Jan 18, 2001, 1:55 AM

Post #5 of 7 (783 views)
Re: SQL - random [In reply to] Can't Post

 
Lol, I was proud of that post, hehe.

Sleuth



Jasmine
Administrator

Jan 18, 2001, 11:04 AM

Post #6 of 7 (779 views)
Re: SQL - random [In reply to] Can't Post

Sleuth,

Your post was superb for a flatfile :) Maybe you want to post your code in the FAQ?



pu_nk
journeyman

Jan 18, 2001, 11:32 PM

Post #7 of 7 (770 views)
Re: SQL - random [In reply to] Can't Post

Both post were great.. I was mainly looking to get a random row form an SQL DB.

THanks again guys !


 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives