Beefy Boxes and Bandwidth Generously Provided by pair Networks
Don't ask to ask, just ask

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

by dbwiz (Curate)
on Feb 14, 2005 at 12:51 UTC ( #430765=note: print w/replies, xml ) Need Help??

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

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://430765]
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (7)
As of 2020-01-25 21:18 GMT
Find Nodes?
    Voting Booth?