perl dbi fetchall array, but with column headers

Posted on 2012-08-28
Last Modified: 2012-08-29
Using Perl DBI, can anyone show me how to fetch all rows from a result set, but have the first row contain the column names?

Instead of [ [ row1data1,row1data2,...],[row2data1,row2data2,...] ] (which is what 'fetchall_arrayref()' returns)

I would like to have:
[ [column_name1,column_name2,...], [ row1data1,row1data2,...], [row2data1,row2data2,...] ]

Any scheme ideas would be welcome.
Question by:grump-
    LVL 10

    Expert Comment

    Here's a routine that will accomplish the task, however, if you're going to call the routine often, you should cache the field names:
    sub fetchResults
      my ($dbh,$table,$query) = @_;
      return complain("No database has been opened.") unless defined($dbh);
      my @fields = ();
      # Retrieve the field names for the table
      my $sth = $dbh->prepare("DESCRIBE `$table`");
      while (my $item = $sth->fetchrow_hashref())
        push @fields, $item->{Field} if defined($item);
      my @result = (\@fields);
      # Execute the query
      $sth = $dbh->prepare($query);
      # Process and return the results
      while (my $item = $sth->fetchrow_hashref())
        next unless defined($item);
        my @row = map{$item->{$_}} @fields;
        push @result, \@row;
      return @result;

    Open in new window

    I fetch all the table names and the field descriptions for each table when I open a database. When I need the field names, I just use @{$TABLE{$table}};
    LVL 28

    Assisted Solution

    By inference, I assume that you're sql statement is "SELECT * from table".  You really shouldn't do it that way.  Instead, you should specify the fields and in the order in which you want then retrieved.  If you do that, then you'll already know/have the field names.

    If you don't want to specify the fields in the select statement, then you can retrieve them after the execute statement.  Instead of doing a separate "DESCRIBE" statement as tdlewis suggests, it would be cleaner and more efficient to use the statement handle's "NAME" attribute.

    Here's an example.
    use strict;
    use warnings;
    use DBI;
    use Data::Dumper;
    my $dbh = DBI->connect("DBI:mysql:mysql:localhost", 'user', 'password',
                           { RaiseError => 1 }) or die $DBI::errstr;
    my $sth = $dbh->prepare("SELECT * from user");
    my $fields = $sth->{NAME};
    print Dumper $fields;

    Open in new window

    $VAR1 = [

    Open in new window

    LVL 25

    Accepted Solution


    my $sth = $dbh->prepare($sql) or die("\n\nPREPARE ERROR:\n\n$DBI::errstr");
    $sth->execute or die("\n\nQUERY ERROR:\n\n$DBI::errstr");
    my @cols = $sth->{NAME};   ## Save the column names
    my @data = $sth->fetchall_arrayref();
    unshift(@data, @cols);   ## Add the column names onto the start or array

    Open in new window


    Author Closing Comment

    I was bouncing around the 'NAME' key from
    I even bought the book:
    I assumed that since fetch.....hash option knows about the column names, somehow the handle must know about the column names for any fetch. It would seem that the database would return column names always. I just could not find an example to make it work. Now I have two. Thanks so much!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    Title # Comments Views Activity
    perl script help 12 99
    Perl efficient DB Call 8 73
    Edit Odbc.ini using perl module 2 63
    Writing a parser for java language 4 45
    Email validation in proper way is  very important validation required in any web pages. This code is self explainable except that Regular Expression which I used for pattern matching. I originally published as a thread on my website : http://www…
    I have been pestered over the years to produce and distribute regular data extracts, and often the request have explicitly requested the data be emailed as an Excel attachement; specifically Excel, as it appears: CSV files confuse (no Red or Green h…
    Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now