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

Yes, this is the situation I am stuck with ATM, apart from making the sql messy(er), I bugs me that rather than table.column mapping directly into an HTML::Template <TMPL_VAR table.column> my choices are to either

use AS everywhere in my sql SELECTs
build a field map of field=>index for each select then use fetchall_array() without the {}. I dislike this approach only because I am lazy and must then change the mapping along with the select.(pedantic I know).
Find a lazy, more perlish way - which as yet eludes me

I can't believe it's not psellchecked

Comment on Re^2: DBI, mysql, SELECT with JOIN and field names Download Code

Replies are listed 'Best First'.
Re^3: DBI, mysql, SELECT with JOIN and field names by maard (Pilgrim) on Feb 14, 2005 at 10:56 UTC
Just a simple idea (taking that your field names contain no underscores (for bravity, you can change this approach to fit your task)): `my %AS = ( requests_id => 'requests.id AS requests_id', requests_name => 'requests.name AS requests_name', requests_rating => '(select count(*) from ratings where ....) AS requ +ests_rating', ...etc... ); my $sth = $dbh->prepare( qq\|SELECT $AS{request_id}, $AS{request_name}, $AS{request_rating}, ...etc );` [download] This also allows expand your SELECTs in future without touching code logic.	[reply] [d/l]
Re^3: DBI, mysql, SELECT with JOIN and field names by jbrugger (Parson) on Feb 14, 2005 at 11:04 UTC
from DBIx documentation: NOTE: Because the query result is returned in a hash, there can only be one out of multiple fields with the same name fetched at once. If you specify multiple fields with same name, only one is returned from a query. Which one this actually is, depends on the DBD driver. so i don't think there is a more lazy perlish way yet... Are you going to provide it? :-)	[reply]