Re: Stuck on an idea..

I have a database with a million zillion names (well not that many, but let's pretend)

I am not really familiar with selectall_arrayref but suspect that all data is loaded into an array (please do correct me if I'm wrong). With a "million zillion" names, this might be a little hard on memory resources. If this is the case, I'd rather go for something like (untested):

my $dbh = DBI->connect($data_source, $user, $password);
my $sth = $dbh->prepare("SELECT id, name FROM names_table");
$sth->execute;

print "<form method=\"post\" action=\"path/to/script.cgi\">\n";
print "<select name=\"name\">\n";

while(my($id, $name) = $sth->fetchrow_array) {
   print "<option value=\"$id\">$name</option>\n";
}

print "</select>\n";
print "<input type=\"submit\">\n";
print "</form>\n";

$dbh->disconnect;
[download]

--
b10m

Comment on Re: Stuck on an idea.. Download Code

Replies are listed 'Best First'.
Re: Re: Stuck on an idea.. by jZed (Prior) on Dec 23, 2003 at 23:18 UTC
I catch your point. But if there are so many names that a simple arrayref of names and ids hogs memory, then I doubt the OP will be able to print them out on a downloadable web page regardless of what methods they use. A million, zillion? Thanks, I'd prefer my dropdown menus to be less than 20 items at maximum and the ones that are close to 200 (e.g. country lists) are unweildy. Imagining a select list with 1,000+ names gives me a headache. If there really are that many or more, they would need to be broken into separate pages e.g. alaphabetically with a LIMIT clause or some such. And each of the borken down lists would be easily handled with an arrayref.	[reply]
3Re: Stuck on an idea.. by jeffa (Bishop) on Dec 24, 2003 at 17:43 UTC
A lot of people think that by using the prepare/execute/fetch cycle they will be able to fetch only one row at a time. This is not always true. You may have to specify a bit more in order to achieve that ... but, even if you did only fetch one row at a time, this would hog up the CPU from other processes and you still have to send at least twice the amount of data to the browser (data wrapped in HTML). It is not always to the best thing to do, but it is good when you know you will be dealing with millions and millions of records. I recommend running a packet sniffer on the database port to see if all rows are being returned at once, or one row at a time. Now, time for a rant. You are printing one line of HTML at a time. This is Perl, not Java! ;) `use strict; use warnings; use DBI; use CGI::Pretty qw(:standard); my $dbh = DBI->connect( qw(DBI:driver:database:host user pass), {RaiseError=>1}, ); my $sth = $dbh->prepare('select id,name from director order by name'); $sth->execute; my $users = $sth->fetchall_arrayref({}); print header, start_form('select_user'), popup_menu('user', [ map $_->{id}, @$users ], undef, { map {$_->{id} => $_->{name}} @$users }, ), p(submit), end_form, ;` [download] For the record, even if i used HTML::Template for this, i would still use CGI.pm's popup_menu(). Once you get the hang of it, it doesn't seem as daunting as it really is. ;) jeffa L-LL-L--L-LL-L--L-LL-L-- -R--R-RR-R--R-RR-R--R-RR B--B--B--B--B--B--B--B-- H---H---H---H---H---H--- (the triplet paradiddle with high-hat)	[reply] [d/l]