Hrunting has asked for the wisdom of the Perl Monks concerning the following question:

I'm trying to get all the field names from a mysql table.

According to the DBD::mysql man page, you can do something like this to get a statement handle that will give you the names of the fields in your table. 

my $sth = $dbh->prepare( "LISTFIELDS $table" );

[download]
My problem is, what do I do with that statement handle once I have it? None of the "traditional" statement handle methods return any data, and I've failed to find any special methods in the DBD::mysql docs.

On a side note, does anyone know of a more elegant, cross-database solution to this problem? I could do a pull, LIMIT 1, and then grab the field names out of a $sth->fetchrow_hashref(), but that's kind of a waste of a pull.

And on a further side note, I tried searching for LISTFIELDS on perlmonks, but apparently, I'm not allowed permission to that node. Bummer.

Replies are listed 'Best First'.
Re: DBD::mysql LISTFIELDS?
by dkubb (Deacon) on Jan 16, 2001 at 07:35 UTC
    The following routine does a portable DESCRIBE $table, that should be database independant. If it's not, please let me know: 
    my $columns = get_columns($dbh, $table);

print @$columns;


sub get_columns {
  my $dbh   = shift;
  my $table = shift;

  #SQL statement always gauranteed to return 0 rows,
  #but ALWAYS returns the column names.  Basicaly a
  #portable "DESC $table"
  my $statement = qq{
    SELECT *
      FROM $table
     WHERE 1 = 0
  };

  my $sth = $dbh->prepare($statement);
  $sth->execute;

  #Get the column names for the $table
  return $sth->FETCH('NAME_lc');
}

    [download]

    Let me explain some of the code above:

    In terms of efficiency, this solution does not return ANY rows, since 1 can never equal 0, so it shouldn't use as many resources, as a query with a LIMIT 1 would.

    I could have used $sth->FETCH('NAME'), but I read in the Perl DBI book that it's best to use NAME_lc, which forces a lower case to all the returned values. If you are porting between databases, you're code could be expecting mixed case from one database, and another could return everything in all CAPS or all lowercase. Not being prepared for this could cause many hours of hair pulling. Always better to be safe than sorry.

    NAME_lc can be specified when using $sth->FETCH and $sth->fetchrow_hashref, and a few other DBI methods too.

    As a side note, I am not sure if this is MySQL specific, but the order of the columns returned by this routine are consistent with the order the columns are in the table structure definition, as returned in MySQL by DESC $table. I wonder if this is true of other databases?

[reply]
[d/l]
[select]
Re: DBD::mysql LISTFIELDS?
by wardk (Deacon) on Jan 16, 2001 at 07:18 UTC
[reply]

Back to Seekers of Perl Wisdom