Gossamer Forum
Home : Products : DBMan SQL : Discussion :

random jump

Quote Reply
random jump
I was asked to try this, and I got a working solution (I think). I don't have a large enough database to test this on.

The assumption is that this will be used on a public-readable database, rather than an "owner only" database.

Add this sub to db.cgi:

Code:
===========
sub get_random {
# --------------------------------------------------------
# Given an ID as input, get_record returns a hash of the
# requested record or undefined if not found.

my ($sth, $query, @result, %result, $i, $key_q);


$query = qq|
SELECT COUNT(*) FROM $db_table
|;

## print "Content-type: text/html\n\n";
## $html_headers_printed = 1;
## print "<BR>Query: $query <BR>";

$sth = $DBH->prepare ($query) or &cgierr("Unable to query database. Reason: $DBI::errstr. Query: $query");

$sth->execute or &cgierr("Unable to query database. Reason: $DBI::errstr. Query: $query");

my ($max_records) = $sth->fetchrow_array;

## $sth->finish;

my $offset = int rand $max_records; # off set of random record.

## print "<BR>Query: $query <BR>";
## print "<BR>$max_records : total records in $db_table<BR>offset is $offset <BR>";


local $" = ',';

$query = qq!
SELECT @db_cols FROM $db_table LIMIT $offset, 1
!;

$sth = $DBH->prepare ($query) or &cgierr("Unable to query database. Reason: $DBI::errstr. Query: $query");
$sth->execute or &cgierr("Unable to query database. Reason: $DBI::errstr. Query: $query");

if (@result = $sth->fetchrow_array) {
$i = 0;
foreach (@db_cols) {
$result{$_} = $result[$i];
$i++;
}
}
$sth->finish;
&html_view_success(@result)
## return %result;
}
======
Then, at the top of the program, change the:

Code:
if ($in{'add_form'}) { if ($per_add) { &html_add_form; } else { &html_unauth; } }
to

Code:
if ($in{'random'}) { if ($per_view) { &get_random; } else { &html_unauth; } }
elsif ($in{'add_form'}) { if ($per_add) { &html_add_form; } else { &html_unauth; } }
This should work without a problem.

Just pass [bold]&random=1[/bold] to the script in a link (make a copy of the "view_all" link in html_footer, such as:


print qq!| <A HREF="$db_script_link_url&random=1">Random Entry</A> ! if ($per_view);


I really did this just to see if I could, and I don't check this forum, so I won't see any error reports or problems, but anything should be fairly easy to fix, since this is not a very complicated routine.

http://www.postcards.com
FAQ: http://www.postcards.com/FAQ/LinkSQL/