Re: DBI, mysql, SELECT with JOIN and field names

[dbwiz]

The DBI docs as well as DBI Recipes warn aboout this problem.

Since you are using MySQL, here is a MySQL-specific workaround. Take it as a basis for a customized function you may want to implement for your programs.

my $query = qq{
        select
            sometable.user,
            othertable.user
        from
            sometable
            inner join othertable using(some_id)};

my $sth = $dbh->prepare($query);

$sth->execute;

my @fields = map {
            $sth->{mysql_table}[$_] . "." . $sth->{NAME}[$_] }
            (0 .. $#{$sth->{NAME}} ) ;

my @list;

while ( my $row = $sth->fetchrow_arrayref() ) {
    my %rec = ();
    @rec{@fields} = @$row;
    push @list, \%rec;
}

use Data::Dumper;
print Dumper \@list;
__END__
$VAR1 = [
          {
            'sometable.user' => 'something',
            'othertable.user' => 'somethingelse'
          },
          {
            'sometable.user' => 'something more',
            'othertable.user' => 'somethingelse too'
          }
        ];

HTH

[submersible_toaster]

Lovely ++ , you are a dbwiz indeed. Your post has three lessons for me.

• I have a much to learn about SQL
• I must RTFM more carefully
• One can sometimes be too lazy
My many thanks.