When interacting with the Perl DBI module, MySQL returns one row of data at a time, one element of data per column. fetchrow_array() allows you to work with each row of data as it is called, usually by using a loop of some sort. fetchrow_arrayref() stores the entire result of a query, which may have multiple rows, into an array, which you can unroll later.
fetchrow_array() is best for calls you where you want to process the results immediately. fetchrow_arrayref() is more suited for those times when you have to draw multiple sets of data from a database. Using fetchrow_arrayref(), your program fetches everything it needs first, through multiple SQL calls, and then parses the results later.
Here's how to implement: First, For a Perl program to draw MySQL data, first you call the DBI module:
use DBI;
Next you open a connection with the database, filing in the values for the database, log-in name and password, thusly:
$dbh = DBI->connect('DBI:mysql:[NAME OF DATABASE]', '[LOG IN NAME]', '[LOG IN PASSWORD]')
|| die "ERROR: $DBI::errstr";
Then you prepare the SQL statement:
$query = "[SQL QUERY (w/o trailing semicolon)]";Here you prepare and execute:
$sth = $dbh->prepare($query); $sth->execute();From here, you use either fetchrow_array() or fetchrow_arrayref().
Using fetchrow_array(), you'd set up a loop that catches each column of data as a separate variable. Here is an example using a MySQL table with 3 columns per row:
while ( @row = $sth->fetchrow_array) {
$variable1 = $row[0];
$variable2 = $row[1];
$variable3 = $row[2];
}
Finally, after you've completed your database calls, you should close the connection:
$sth->finish(): $dbh->disconnect();(A working template for a Perl fetchrow_array-based program can be found here).
In the second approach, fetchall_arrayref(), stores the entire set of results from the query within a single array. Then you extract data from the array.
As in the previous example, you prepare and execute the data query the same way, but then you use fetchall_arrayref() to store the results in a Perl array. So:
$data = $sth->fetchall_arrayref();
$sth->finish;
foreach $data ( @$data) {
($variable1, $variable2, $variable3) = @$data;
print "$variable1\n";
print "$variable2\n";
print "$variable3\n";
}
Note you close "sth" before the data is processed here. This allows you to make multiple calls before processing the data with Perl. Don't forget to close the database though ("$dbh->disconnect()").
(A working template for a fetchall_arrayref()-based program can be found here).
Material taken from the book...