#!/usr/local/bin/perl -w
# Demonstrate a scrolling list with DBI

use strict;
use CGI;
use DBI;

use vars qw($q $table);

$q = new CGI;
$table = 'makes';


MAIN: {
        my ($makes, $html);

        $makes = get_makes();

        if (ref $makes) {
                print makes_page($makes);
        } else {
                print error_page('Blah!');
        }

}

sub get_makes {
        my ($dbh, $sql, $sth, @makes);
        $dbh = get_dbh();

        $sql = qq{SELECT make FROM $table};
        $sth = $dbh->prepare($sql);
        $sth->execute();
        while (my $make = $sth->fetchrow_array()) {
                push @makes, $make;
        }
        $sth->finish();
        $dbh->disconnect();

        return \@makes;
}

sub get_dbh {
        my ($dsn, $dbh);

        $dsn = "DBI:mysql:database=cartalk";
        # better:
        # $dsn = q{DBI:mysql:mysql_read_default_file=/home/you/.my.cnf};

        $dbh = DBI->connect($dsn) || die $DBI::errstr;
        return $dbh;
}

sub makes_page {
        my $makes = shift;
        my ($make, $html);

        $make = (defined $q->param('make') && $q->param('make') =~ /^\w+$/)
                ? $q->param('make')
                : '';

        $html = $q->header() . $q->start_html('Your title') . $q->start_form();
        $html .= $q->scrolling_list(-name   => 'make',
                                    '-values' => $makes,
                                    -size   => 6);
        $html .= $q->submit(-name => 'submit', -value => 'Submit!');
        $html .= "<P>Submitted: $make</P>\n";
        $html .= $q->end_form() . $q->end_html();

        return $html;
}

sub error_page {
        my $msg = shift;
        my ($html);

        $html = $q->header(). $q->start_html('Error!');
        $html .= $msg;
        $html .= $q->end_html();
        return $html;
}