Using Perl with MySQL - Learning MySQL - page 549

my $dbh=DBI->connect( "DBI:mysql:host=localhost;database=$DB_Database",
"$DB_Username", "$DB_Password", {PrintError=>0, RaiseError=>0})
or
die("Failed connecting to the database ".
"(error number $DBI::err): $DBI::errstr\n");
# Process the data to calculate the total;
my $Animal_Name;
my $Animal_Count;
my $Total=0;
print "Pet roll call:\n".
"===============\n";
my $Query="SELECT Name, Count FROM Animals";
my $sth = $dbh->prepare($Query);
$sth->execute ();
while(my $ref=$sth->fetchrow_arrayref())
{
printf("%-10d %-20s\n", $ref->[1], $ref->[0]);
$Total+=$ref->[0];
}
print "===============\n".
"Total:\t$Total\n";
$sth->finish();
$dbh->disconnect();
Example 17-4 removes the need for the $ref variable by using the bind_columns( )
function to bind the result columns to the Animal_Name and Animal_Count variables.
Example 17-4. Perl fragment to read and display data from the Animals database, using binding
my $Total=0;
print "Pet roll call:\n",
"===========\n";
my $Query="SELECT Name, Count from Animals";
my $sth = $dbh->prepare($Query);
$sth->execute ();
my $Animal_Name;
my $Animal_Count;
# Bind query results to variables
$sth->bind_columns(\$Animal_Name, \$Animal_Count);
while($sth->fetchrow_arrayref())
{
print "$Animal_Name:\t$Animal_Count\n";
$Total+=$Animal_Count;
}