in reply to building Drop down menu - from a flat file

DBD::CSV is definitely the way to go: 
use strict;
use DBI;

my $dbh = DBI->connect(
   "DBI:CSV:f_dir=.;csv_eol=\n;csv_sep_char=!;",
   {RaiseError=>1},
);

$dbh->{csv_tables}->{'class'} = { 
   file      => 'class.csv',
   col_names => [qw(id name class instructor)],
};

my $sth = $dbh->selectcol_arrayref("
   select distinct instructor
   from class
");

print join("\n",@$sth),"\n";

[download]

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)

Replies are listed 'Best First'.
Re: (jeffa) Re: building Drop down menu - from a flat file
by data67 (Monk) on Jul 24, 2002 at 18:13 UTC
    well i had tried the DBI::CSV before i posted this question and i did the same thing you say here BUT when i try to use the DISTINCT in my SQL it does not seem to return the desired result?

    Here is what i had: 

    #!/usr/local/bin/perl

use DBI;

my $dbh = DBI->connect(
           "DBI:CSV:f_dir=/export/home/temp;csv_eol=\n;csv_sep_char=!;
+",
                      {RaiseError=>1},
                              );

$dbh->{'csv_tables'}->{'datainfo'} = {
        'file' => 'datainfo.data',
#       'sep_char' => "!",
        'quote_char' => undef,
        'escape_char' => undef,
        'col_names' => ["id", "class", "name", "instructor"]

        };


my ($query) = "select distinct id, class from datainfo ";
my ($sth) = $dbh->prepare($query);
$sth->execute();

while (my $row = $sth->fetchrow_hashref) {
        print ("ID =",  $row->{'id'}, "\n\t CLASS = ", $row->{'class'}
+, "\n");
}
print "\n";

    [download]
    you know i just say a mistake in my code, and when i tried to run it with just one column in the select it ran correctly! I wonder if DBI::CSV does allow mutilple columns to be listed with a DISTINCT
[reply]
[d/l]

      This is a SQL issue, not a DBI::CSV issue. DISTINCT only eliminates those rows where all of the selected fields are identical. You could eliminate more rows by specifying the GROUP BY keywords, but i doubt you really want to do that ... consider either normalizing your data or using a hash solution instead.

      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]

In Section Seekers of Perl Wisdom