Obtaining field names only

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

Wise Ones,
I am trying to print out the field names of each table in a mysql db and then the values.

I can get the values and the field names but I can't seem to get them in the format I want.
I basically want to output the contents of a database with several tables in CSV format, like so:

field1, field2, f3, f4, f5
Value1, value2, v3, v4, v5
Value1, value2, v3, v4, v5

field1, field2, f3, f4, f5, f6, f7
Value1, value2, v3, v4, v5, v6, v7
Value1, value2, v3, v4, v5, v6, v7
[download]

The problem I keep getting is that i'm printing the field names and values for each row in the table. Can't just seem to print the field names then each row below.

The snippet below does what I have described, from processing each table in the database.

        
if ($format eq "CSV") 
{
  while ($row = $sth->fetchrow_hashref)
  {
    while( my ($k, $v) = each %{$row} ) 
    {
      print $k;
    }
    foreach $col (keys %{$row}) 
    {
      print $$row{$col} . ",";
    }
    print "\n";
  }
  print "\n";
}
[download]

Im sure there's a fairly easy answer to this but I cant seem to find it in the DBI tutorial here or through searching google.

Cheers, Steve

Comment on Obtaining field names only Select or Download Code

Replies are listed 'Best First'.
Re: Obtaining field names only by jZed (Prior) on Feb 17, 2005 at 19:31 UTC
You're welcome to do the work yourself, but here's another way: `my $select = "SELECT * FROM mysqlTable"; # or whatever my $ad_dbh = DBI->connect(...AnyData...); my $mysql_dbh = DBI->connect(...mysql... ); $ad_dbh->import("tmp",'DBI',$mysql_dbh,{sql=>$select}); $ad_dbh->export("tmp",'CSV','myfile.csv); # all done!` [download] Or else use DBD::CSV and loop through a mysql select doing CSV inserts. Or else use MySQL's dump facility. I forget what it's called, but I think you can export directly to CSV from MySQL.	[reply] [d/l]
Re: Obtaining field names only by Animator (Hermit) on Feb 17, 2005 at 18:47 UTC
First of all you should only print the field-names ones. Your current code does that for all the rows. To accomplish that you can use the 'field' function/'fields' variable (not sure of the correct name... I leave it up to you to lookup the correct name in the DBI docs.) Second you are missing some code in printing the keys (as in you don't print a newline). Third, it is much better to use `$row->{$col}` instead of `$$row{$col}`.	[reply] [d/l] [select]
Re: Obtaining field names only by RazorbladeBidet (Friar) on Feb 17, 2005 at 18:46 UTC
You may just want the top loop (outside of the fetchrow_hashref) using $sth->{NAME} -------------- It's sad that a family can be torn apart by such a such a simple thing as a pack of wild dogs	[reply]