Re: Creating Dynamic SQL statements using arrays

Thanks Everybody that was very helpful.
I have a little extra question to tag onto to that now if anybody can help.

Having retrieved the values I now need to display the results. The code I am using requires foreknowledge of the amount of results to be outtputted.
However I don't know how many are to be outputted until run time.

<code> # Fetch each row and print it
while ( my ($field1, $field2, $field3) = $sth->fetchrow_array() ) {
print STDOUT "Field 1: $field1 Field 2: $field2 Field 3: $field3\n";
} <code>

Is there any sort of a funky loop or something I could implement?

Comment on Re: Creating Dynamic SQL statements using arrays

Replies are listed 'Best First'.
Re: Re: Creating Dynamic SQL statements using arrays by broquaint (Abbot) on Jul 11, 2002 at 14:19 UTC
The code I am using requires foreknowledge of the amount of results to be outtputted. There's a bunch of ways you could do this, but they mostly depend on how you'll be outputting your data. If you're just listing what you got out of the database you could just do this `while(my $rr = $sth->fetchrow_hashref()) { print "$_: $rr->{$_}\n" for keys %$rr; }` [download] Or if you're not too concerned about the field names you could just print out all the values like so `while(my @rows = $sth->fetchrow_array()) { print "Values: @rows\n"; }` [download] Or if you want a list of enumerated the values `while(my @rows = $sth->fetchrow_array()) { print "$_: $rows\n" for @rows; }` [download] So as you can see you have plenty of options. Personally I'd recommend using the `fetchrow_hashref()` method as the brain maps better to names than sequences. See the `DBI` docs for more info on the methods mentioned above. HTH `_________ broquaint`	[reply] [d/l] [select]
$sth->rows by BorgCopyeditor (Friar) on Jul 11, 2002 at 14:20 UTC
If I've understood your question, you just need to use `$sth->rows`, which returns the number of results from your query. BCE --Your punctuation skills are insufficient!	[reply] [d/l]
Re: Re: Creating Dynamic SQL statements using arrays by graff (Chancellor) on Jul 12, 2002 at 05:41 UTC
Regarding this point: Having retrieved the values I now need to display the results. The code I am using requires foreknowledge of the amount of results to be outtputted. However I don't know how many are to be outputted until run time. Is it the number of rows you need to anticipate? or the number of columns? or both? If the listing follows from the kind of query you asked about earlier, you already have the array of columns that you're selecting, so that can be used again for printing. To know the number of rows in advance of printing them out, you could try one of the "fetchall" or "selectall" DBI methods; these return an array ref which you can then check with "scalar( @$array_ref )" to get the number of rows returned. Or, if you're worried that this might put too much overhead/memory-load on your system if the return on the query is really big, and you really want to know how many rows you're going to get before you print them, do a "pre-query" that starts with "select count(some_field) where $where_clause"... But in my own experience (on solaris/oracle) there seems to be less time/trouble in using a "fetchall" than in doing a big query two times (though I have never tested this rigorously).	[reply]