Hi Bill,
"Bill Moseley" <[EMAIL PROTECTED]> wrote:
How do I get the column names as a list to match the order of the
rows returned when using select/fetchall_arrayref [...] I'm not
having luck finding it in the docs.
See the "Statement Handle Attributes" section of the DBI docs:
http://search.cpan.org/~timb/DBI-1.50/DBI.pm#Statement_Handle_Attributes
Although I'm not sure if this could work for $dbh->selectall_arrayref(),
if you didn't mind preparing and executing the statement handle in
advance (and your backend database's DBD driver supports it -- MySQL
does), then you can use the NAME attribute of the statement handle to
get back the actual column names that will be returned in the query
result *before* you actually fetch any rows (but after you execute the
query) like this:
# assuming a little test db like this:
create table test (
id int not null,
name text,
age int,
primary key (id)
);
insert into test
(1, 'david', 39),
(2, 'goliath', 1764)
;
You can prepare your query and execute it but not fetch anything yet:
my $sth=$dbh->prepare("select * from test");
$sth->execute or die $sth->errstr;
and now the statement handle can tell you its ->{NAME}'s:
print Dumper($sth->{NAME});
as an arrayref of column names, in the order that they'll be returned
(once you fetch them):
$VAR1 = [
'id',
'name',
'age'
];
and here comes the fetched data:
print Dumper($sth->fetchall_arrayref)'
$VAR1 = [
'id',
'name',
'age'
];
$VAR1 = [
['39', 'david'], ['1764', 'goliath']
];
...and using an ARRAY slice?
d'oh! You like throwing those curve balls don't you?
I don't know the column names ahead of time -- I'm passed a query and
want to return the data in the column order specified in the query.
And also return the list of column names.
Okay well, now that you know the names in the query *before* you sliced
it up at fetch-time with an arrayref, so now you just need to slice the
NAME's arrayref the same way. If, for instance, you passed an arrayref
slice to get back the third and second columns, in that order:
my @names = @{$sth->{NAME}};
my @slice = (2,1);
print Dumper (@[EMAIL PROTECTED]);
$VAR1 = 'age';
$VAR2 = 'name';
the same way the fetched data got sliced:
print Dumper($sth->fetchall_arrayref([EMAIL PROTECTED]))'
$VAR1 = [
['39', 'david'],
['1764', 'goliath']
];
Hope this helps!
-dave
