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)

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

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)

Greetings data67,

If it was me, I'd read through the file, splitting each line and pushing a particular element into an array (if you just want each value in the column) or a hash (if you want distinct fields). The below example uses both together. (Don't do this. Just pick one of the two.)

open(INPUT, '< your-input-file.txt') or die "Badness: $!";
my (@instructors_array, %instructors_hash);
while (<INPUT>) {
  my ($id, $name, $class, $instructor) = split(/!/);
  push @instructors_array, $instructor;
  $instructors_hash{$instructor}++;
}
close(INPUT);
my @array_with_every_entry_sorted = sort @instructors_array;
my @array_with_distinct_entries = sort keys %instructors_hash;
[download]

There's probably a way to do this in one line with map, but I'm not merlyn or IO, and I haven't had my first cup of coffee yet.

-gryphon
code('Perl') || die;

You can do it all in memory if the list isn't too big. Since it's going to fit in a drop-down box, I presume that's the case. Also, I presume a simple split on the ! character will serve to isolate the fields appropriately (in other words, there are no embedded !s).

Then the trick is to use a hash to remember each distinct occurence. Afterwards you can look at the keys in the hash to retrieve all the unique values. The combination would be something like this (note, this is untested):

my %unique;
while (<IN>) {  # or whatever the file handle is
    my @fields = split /!/;
    $unique{$fields[-1]}++;   # last field in the row
}
my @drop_down = keys %unique; # Et voila
[download]

HTH

# Flat file
ac      193.0.0.193     Ascension Island
as      128.250.1.21    American Samoa
be      192.36.125.2    Belgium
biz     209.173.53.162  US BIZ
[download]

# send data to template
my @root_loop = &get_roots;
my $template = HTML::Template->new(filename => 'Digbeta/digauthform.tm
+pl', die_on_bad_params => 0);
$template->param(root_loop => @root_loop);
print $template->output;
[download]

# create Array of Hashes
sub get_roots {
        my @root_loop;
        open(ROOTS, "roots3");
        foreach (<ROOTS>) {
                chomp;
                my ($code, $ip, $country) = /(.*)\t(.*)\t(.*)/;
                my %roots_row = (
                        code => $code,
                        ip_addy => $ip,
                        country => $country
                );
                push(@root_loop, \%roots_row);
        }
        return \@root_loop;
}
[download]

# HTML Template
<select NAME="server">
<option SELECTED VALUE="Default">Default (as per Query Type)
<TMPL_IF root_loop>
     <TMPL_LOOP NAME="root_loop">
     <option VALUE="<TMPL_VAR NAME="code">"><TMPL_VAR NAME="code"> - <
+TMPL_VAR NAME="country">
     </TMPL_LOOP>
</TMPL_IF>
</select>
[download]

Look into loading your data into a hash - use the column you want distinct as the key

This is not HOMEWORK. i am trying to modify an existing app. and am restricted in Certain ways.By the way i do agree that this has to use some sort of datastructure eg. HoH or HoAoH etc..

--dda