[ACCEPTED]-How can I get column names and row data in order with DBI in Perl?-dbi

Accepted answer
Score: 19

Replace the "what goes here" comment 7 and the following loop with:

my $fields = join(',', @{ $result->{NAME_lc} });
print "$fields\n";

while (my $row = $result->fetchrow_arrayref) {
    my $csv = join(',', @$row);
    print "$csv\n";

NAME_lc gives the field 6 names in lowercase. You can also use NAME_uc for 5 uppercase, or NAME for whatever case the database 4 decides to return them in.

You should also 3 probably be using Text::CSV or Text::CSV_XS instead of trying 2 to roll your own CSV file, but that's another 1 question.

Score: 3

If you want to preserve the order, but still 2 use a hash to refer to fields by name use:

$dbh->selectall_arrayref($sql,{ Slice => {} } );

This 1 will give you an ordered array of hashes

Score: 3

Define your column names in an ARRAY before your SELECT

Ideally you'd have a list of the columns 8 you were SELECT'ing with DBI, and you'd use that 7 array.

If you need to get the column names 6 from the hash itself, this will work, and 5 you can sort it, but there is no indication 4 of the original SQL SELECT order (in the hash):

my %cols_hash = ("name" => "john", "age" => 2, "color" => "apalachian");
my $cols_hash_ref = \%cols;  

my @keys = (sort keys %$cols_hash_ref);  
my @vals;  
foreach (@keys){ push @vals, $$cols_hash_ref{$_} };  

Hope 3 this helps.

When I searched I found a way to get the column names from the DBI:

$sth = $dbh->prepare($query) or die "Prepare exceptioin: $DBI::errstr!";  
$rv = $sth->execute() or die "Execute exception: $DBI::errstr";  
$res = $sth->fetchall_arrayref();  

# Array reference with cols captions, which were retrived.  
$col_names_array_ref = $sth->{NAME};          

That should give you the column 2 names in the original order, but I haven't 1 tested it.

Score: 1

You're asking for the result as a hash. A 8 hash is inherently unordered. Perhaps you 7 want fetchrow_arrayref instead.

In fact, if you had looked 6 at keys %$row, you would have seen the corresponding 5 keys being out of order as well. That's 4 the nature of a hash... each key is paired 3 with its value, but the overall ordering 2 of keys or values is optimized for access, not 1 external ordering.

Score: 1

Here's what I do:

    use Data::Dump qw(dump);
    # get column names in array
    my @column_names_array= $sth->{NAME};  
    # print out column names in pretty format
    print "Field names: \n";


Score: 0
use strict;
use warnings;
use DBI;
my $database = "your_mysqldb";
my $username = "your_dbusername";
my $password = "your_dbpassword";
my $tblename = "your_mysqldbtable";
my $dsn = "DBI:mysql:embedded";
my %attr = ( PrintError=>0,   #turn off error reporting via warn()                                                                                                                     
             RaiseError=>1);  #turn on error reporting via die()                                                                                                                       
my $dbh = DBI->connect($dsn,$username,$password,\%attr);
print "Connected to the MySQL database $database for username $username\n";
my $sql = "DESCRIBE ".$tblename;
my $sth = $dbh->prepare($sql);  
$sth->execute();   #execute the prepared query                                                                                                                                         
my @heads;
my $i=0;
while(my $r = $sth->fetchrow_arrayref()){
my $j=0;
    print "$j $_\n";
$dbh->disconnect(); #disconnect from mysql db   


More Related questions