submersible_toaster has asked for the wisdom of the Perl Monks concerning the following question:
Mellow Fonks,
It frustrates me that I cannot discover the solution to this problem and so risk annoying people posting what I don't think is strictly a DBI question.
For instance, $sth->fetchall_arrayref({}) is something I have learned to love, except when returning results from joined tables where field names from different tables collide, clobbering keys in the hash. Apart from using AS in my SELECT statements, how can I convince DBI to return full table.column type field names. I thought that $dbh->{FetchHashKeyName} = might be my answer but it would seem not.
For example purposes only:
my $sth = $dbh->prepare( qq|SELECT requests.id, requests.name, request +s.requestor, requestors.name, requestors.email FROM requests LEFT JOIN requestors ON requests.requestor = requestor.id| ); my $list = $sth->fetchall_arrayref({});
Update:on Feb 14, 2005 at 21:36 GMT-10
The mapping field names to indexes idea, with my screwy logic below (untested).
my $sth = $dbh->prepare( qq|SELECT request.id, request.daterequested, request.requestor +, request.producer, requestor.name, requestor.email, producer.name, producer.e +mail LEFT JOIN requestor ON request.requestor = requestor.id LEFT JOIN producer ON request.producer = producer.id | ); my @mapping = qw/ request.id request.daterequested request.requestor r +equest.producer requestor.name requestor.email producer.name producer.email/ ; $sth->execute(); my $arrayref = $sth->fetchall_arrayref(); my @mapped = map { my %mapto; @mapto{@mapping} = @$_; \%mapto; } @$arrayref;
I can't believe it's not psellchecked
|
---|
Replies are listed 'Best First'. | |
---|---|
Re: DBI, mysql, SELECT with JOIN and field names
by dbwiz (Curate) on Feb 14, 2005 at 12:51 UTC | |
by submersible_toaster (Chaplain) on Feb 14, 2005 at 13:57 UTC | |
Re: DBI, mysql, SELECT with JOIN and field names
by ikegami (Patriarch) on Feb 14, 2005 at 08:24 UTC | |
by submersible_toaster (Chaplain) on Feb 14, 2005 at 10:17 UTC | |
by maard (Pilgrim) on Feb 14, 2005 at 10:56 UTC | |
by jbrugger (Parson) on Feb 14, 2005 at 11:04 UTC | |
Re: DBI, mysql, SELECT with JOIN and field names
by jbrugger (Parson) on Feb 14, 2005 at 09:08 UTC |