Database Reference
In-Depth Information
to end before processing the results. Putting MySQL on hold while processing each row
as shown earlier can slow down a program, especially when dealing with large amounts of
data. It's sometimes better to create a complex data structure (i.e., an array of arrays) and
then leave the data structure in memory until needed. To dothis, you'd use the
fetchall_arrayref()
method. It will return the starting location in memory of the
array. Here's an example of this:
...
my
$members = $sth->
fetchall_arrayref
();
$sth->
finish
();
foreach my
$member (@$members){
my
($human_id, $full_name, $membership_expiration) = @$member;
print
"$full_name ($human_id) - $membership_expiration \n"
;
}
$dbh->
disconnect
();
The
fetchall_arrayref()
fetches all of the rows, stores them in an array in
memory, and returns a reference to its location. This is stored in
$members
. Using a
foreach
, we extract each array within the
@$members
array and store it in
$member
.
With the block of the
foreach
, we extract each element of the
$member
array and store
those values in
$human_id
,
$full_name
, and
$membership_expiration
. We
then display them using
print
.
Notice that we executed the
finish()
to end the statement handle and to free MySQL
resources. We could have also put
disconnect()
immediately after it if we didn't in-
tend to create and execute more SQL statement handles. This would have had no effect on
the
foreach
processing the results fetched by
fetchall_arrayref()
.
Updating data
In the previous examples, we saw how to select data from a table. Let's now look at an ex-
ample that updates data in a table. We'll change the
$sql_statement
to include an
UPDATE
statement that will update the date of
membership_expiration
for a mem-
ber in the
humans
table. We can do that like this:
...
my
($human_id, $membership_expiration) = (
shift
,
shift
);
$sql_stmnt =
"UPDATE humans
SET membership_expiration = ?
