Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation
 
PerlMonks  

Getting a list of columns from a MySQL Table

by c (Hermit)
on Jul 22, 2002 at 22:16 UTC ( #184256=perlquestion: print w/replies, xml ) Need Help??

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

I did a PM search on a similar title and what came up seemed to be even past what little I know of working with MySQL and DBI.

I am currently using:

## TEST get a list of tables my @tables = $dbh->tables(); for my $i(sort @tables) { print uc $i . "\n\n"; my $sth = $dbh->prepare("LISTFIELDS $i"); $sth->execute; $sth->finish; sleep 1; }

The code seems good up to the point that I try and grab the column names and list them out. Its when I make the $sth execute that it returns empty. I am sure that my table does contain columns. The code I have is almost identical to the example in the MySQL DBD README.

thanks -c

Replies are listed 'Best First'.
Re: Getting a list of columns from a MySQL Table
by mp (Deacon) on Jul 22, 2002 at 22:27 UTC
    Try using "DESCRIBE table" instead of "LISTFIELDS table". This will give you column name, type, allownull, key, default, and extra. For more information on what you get, you may want to try the following inside your for loop:
    use Data::Dumper; my $sth = $dbh->prepare("DESCRIBE $i"); $sth->execute; while(my $info = $sth->fetchrow_hashref) { print Dumper($info); }
      Thanks, I saw the fetchrow_hashref routine and tried something like:

      for my $i(sort @tables) { print uc $i . "\n\n"; ## get a list of columns for the table my $sth = $dbh->prepare("LISTFIELDS $i"); $sth->execute; my $row = $sth->fetchrow_hashref(); for my $header(keys %{$row}) { print " $header\n"; } $sth->finish; sleep 1; }

      but still no dice. I'll try your code and see what it yields. Thanks! -c

      A reply falls below the community's threshold of quality. You may see it by logging in.
Re: Getting a list of columns from a MySQL Table
by Aristotle (Chancellor) on Jul 23, 2002 at 00:12 UTC

    Did you check $sth->errstr (or generally $DBI::errstr)? There's nothing in your example that checks for errors; do you use RaiseError?

    (I think you needn't call finish() either there, btw, since $sth goes out of scope at the end of the loop anyway, but that's beside the question of course.)

    Makeshifts last the longest.

Re: Getting a list of columns from a MySQL Table
by Moonie (Friar) on Jul 22, 2002 at 22:38 UTC
    You may want to look at this link here.
Re: Getting a list of columns from a MySQL Table
by ehdonhon (Curate) on Jul 23, 2002 at 03:21 UTC
Re: Getting a list of columns from a MySQL Table
by seattlejohn (Deacon) on Jul 23, 2002 at 04:59 UTC
    After you call $sth->execute, you should be able to get the number and names of columns via
      $sth->{NUM_OF_FIELDS}
    and
      $sth->{NAME}->[$n]
    where $n is an index from 0..$sth->{NUM_OF_FIELDS}-1

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://184256]
Approved by BrowserUk
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (4)
As of 2021-06-23 18:47 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    What does the "s" stand for in "perls"? (Whence perls)












    Results (121 votes). Check out past polls.

    Notices?