Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight
 
PerlMonks  

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

HTH

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?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (5)
As of 2019-07-22 08:20 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    If you were the first to set foot on the Moon, what would be your epigram?






    Results (11 votes). Check out past polls.

    Notices?