Perl, MySQL, fetcharray, while

[jollyroger]

Hello there
Please can somebody help us? We are trying to create a web page after querying a mysql database.
We have managed to connect to the database and we get results from it, we have also managed to print this results onto a web page using:
while ( @ary=sel1->fetcharray() ) {
print &quot;The query results: $ary[0] and $ary[1]&quot;,&quot;<br>\n&quot;;
}
BUT we don't want to do this. We want to capture the array in the bit here and then use it somewhere else in the script!
For example:
@ary=sel1->fetcharray();
But when we do this we get nothing back, even though earlier in the script we have called the array using the above print command?
Also, we have got an error message telling us that there is a unatural database disconnect.
Finally just for your information the database query is contained within a sub routine which is called earlier on in the script.
Any idea's
Thanks
Jollyroger

 

[goBoating]

The 'fetcharray' method returns one db record at a time as an array of fields. So, one iteration of

@array = $sth->fetcharray()

gets just one record. In order to get all the incoming records, just push them onto another array.


To illustrate, the incoming record is joined to make a string and then each string is pushed onto the records array.

while (@fields = $sth->fetcharray()) {
my $tmp = join '|', @fields;
push @records, $tmp;
undef $tmp;
}
# print pipe separated output of each record.
foreach (@records) { print "$_\n"; }

While that serves to illustrate getting your records, you probably would not want to do it quite that way. Maybe something more like.....

my $sth = $dbh->prepare("select * from table");
$sth->execute();
while (my @row = $sth->fetchrow){ push @sequence, \@row; }
$sth->finish();

foreach $rec (@sequence) {
    print "${$rec}[0], ${$rec}[1]\n"; 
    }

Even better, get a copy of &quot;Programming the Perl DBI&quot; by Descartes and Bunce from O'Reilly for a good, practical, and concise treatment of using fetchrow, fetchrow_array, fetchrow_arrayref, and fetchrow_hashref.

'hope this helps

If you are new to Tek-Tips, please use descriptive titles, check the FAQs, and beware the evil typo.

 

[jollyroger]

Thanks for this, I have tried this and it works a treat!!!!

With regards

Jollyroger