DBI and fetching data

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

Hi Monkeys,

To get the column names, I can write:

$output[0] = [ @{ $sql->{NAME} } ];
[download]

But to fetch the data, I have to write:

while(@temp_array =  $sql->fetchrow_array ){
  $output[$i] = [ @temp_array ];
  $i++;
}
[download]

Why can't I get away with (or similiar):

$i++ while $output[$i] = [ $sql->fetchrow_array ];
[download]

AFAIK I've tried all the variants I can think of, but I only get an error

Tom Melly, tom@tomandlu.co.uk

Comment on DBI and fetching data Select or Download Code

Replies are listed 'Best First'.
Re: DBI and fetching data by cchampion (Curate) on Sep 12, 2003 at 09:19 UTC
Have a look at our Tutorials. Check out especially DBI Recipes at the section "Getting a list of lists". In addition to solving your specific problem, you may find more valuable ways of dealing with your data.	[reply]
Re: Re: DBI and fetching data by Melly (Chaplain) on Sep 12, 2003 at 09:33 UTC
Many, many thanks to all Tom Melly, tom@tomandlu.co.uk	[reply]
Re: DBI and fetching data by Abigail-II (Bishop) on Sep 12, 2003 at 09:03 UTC
`$i++ while $output[$i] = [ $sql->fetchrow_array ];` [download] That loop never terminates. A reference is always true, even a reference to an empty array. However, you could write something like (I don't have DBI on the machine I'm currently working on, so this is untested): `my $output = $sql -> fetchall_arrayref; # Or my @output = @{$sql -> fetchall_arrayref};` [download] If you are going to collect all results in a big array, let the DBI do the work for you. Abigail	[reply] [d/l] [select]
Re: Re: DBI and fetching data by Melly (Chaplain) on Sep 12, 2003 at 09:09 UTC
That looks good - I've only just dipped my toes into DBI, looks like it's time to get soaked. Just out of curiousity (I'm happy enough to roll my own), are there any good modules for working with CGI and DBI? (i.e. nicely formatted html tables of returned data in one easy go). Tom Melly, tom@tomandlu.co.uk	[reply]
Re: Re: Re: DBI and fetching data by cchampion (Curate) on Sep 12, 2003 at 09:32 UTC
nicely formatted html tables of returned data in one easy go Sure. Have a look at jeffa's DBIx::XHTML_Table. It does exactly what you want.	[reply]