in reply to DBI, mysql, SELECT with JOIN and field names

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' } ];


Replies are listed 'Best First'.
Re^2: DBI, mysql, SELECT with JOIN and field names
by submersible_toaster (Chaplain) on Feb 14, 2005 at 13:57 UTC

    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.

    I can't believe it's not psellchecked