#!/usr/bin/perl -w

use DBI;
use CGI qw(:standard);
use strict;

#You connect to Oracle in a similar way I'm sure...
my $driver = "mysql";
my $database = "blah";
my $hostname = "blah";
my $dsn = "DBI:$driver:database=$database;host=$hostname";
my $dbh = DBI->connect($dsn,undef,undef);

print header,start_html,"\n";


        my $people_query = $dbh->prepare("SELECT SS_NUMBER, FIRST_NAME
+, LAST_NAME FROM people");
        $people_query->execute;
        my %people_hash;

                #creat a hash of "fname lname" keyed by SSN
        while (my @people_array = $people_query->fetchrow_array) {
                $people_hash{"$people_array[0]"} = "$people_array[1] $
+people_array[2]";
        }
                #create a list of the SSNs maybe sort it?
        my @people_keys = sort{$a <=> $b} keys %people_hash;

           print table(
                Tr(
   
                    td(                

                            ['Some people:',
                            scrolling_list(    -name =>'people',
                                            -values =>\@people_keys,
                                            -labels =>\%people_hash,
                                            -size=>1)
                    ]),
                ),
            );
    print end_html;
[download]

Personally I prefer to do the sorting in Oracle:

$sql = "SELECT ss_number,
               last_name || ' ' || first_name NAME
        FROM people
        ORDER BY 2";
[download]

and using

$people_hash{$people_array[0]} = $people_array[1];
push @people_keys,$people_array[0];
[download]

to populate the local storage structures.
Just a matter of style, really. I haven't done any benchmarking, but Oracle Corp spends a lot of money writing code to make sorting effecient :)

rdfield

Sorry, I don't have the time to go into detail but just wanted to send you in the right direction : ) hope it helps.

my input to this would be to possibly find some other key than SSN, if you create this dropdown with SSN, a simple View Source will allow all users to capture all the others SSN. this can never be a good thing (unless perhaps you work for SSA and this is an app for internal use only :-)

the Social Security number is just an example for this post. I am actually using a unique refernce number. But definately a good point to remember.

thanks

yes, if your tables are related you would do it in the same statement.


#this part might be different depending on how your db does SQL
my $people_query = $dbh->prepare("SELECT ss_number, f_name, l_name, st
+ate FROM people, states WHERE people.stID = states.stID");
[download]


If your tables are not related you would use multiple statement handles like the one above. As long as they have different names you can manipulate them indepedently.

my $sth = $dbh->prepare("SELECT SSN, FIRST_NAME, LAST_NAME, 
  FROM ZZ_INFO ORDER BY LAST_NAME");
unless ($sth->execute()) { print "$DBI::errstr \n"; };
 
my %hrcd; 
my $i = 0; 
while (my @row = $sth->fetchrow_array) {
  my $ssn = (defined $row[0]) ? $row[0] : '';
  my $fn  = (defined $row[1]) ? $row[1] : '';
  my $ln  = (defined $row[2]) ? $row[2] : '';
  $hrcd{$i}= [$ssn, $ln, $fn]; 
  $i++;
} 
 
print "<table><tr><th>Name</th></tr><tr><td><select name=\"SSN\" size=
+\"1\">\n";
for my $j (sort keys %hrcd) { 
  print "<option value=\"$hrcd{$j}->[0]\">$hrcd{$j}->[1], $hrcd{$j}->[
+2]</option> \n";
} 
print "</select></td></tr></table>\n";
[download]