Table of Contents for

MySQL in a Nutshell, 2nd Edition

This function associates or binds an array of values in an SQL statement within a prepare() using placeholders. The first argument indicates which placeholder to replace with the array of given values, i.e., the second argument. The values are updated when the related row is retrieved using a fetch method. Attributes may be added or the data type given as a third argument. Here is an example:

...
my @old_names = ('Graham Green', 'Virginia Wolf');
my @new_names = ('Graham Greene', 'Virginia Woolf');

my $sql_stmnt = "UPDATE books 
                 SET author = ?,
                 status = ?
                 WHERE author = ?";
                 
my $sth = $dbh->prepare($sql_stmnt);

$sth->bind_param_array(1,\@new_names);
$sth->bind_param_array(2, 'active');
$sth->bind_param_array(3, \@old_names);

$sth->execute_array(undef);

$sth->finish();

Notice in this example that the first array contains all of the new author names, the corrected ones. It’s not a pairing or a grouping by row. Instead, it’s all of the values to be used for the first placeholder in the SQL statement. The second array bound contains the old names in the same order to be used in the WHERE clause. Incidentally, the backslash before each array shown here is necessary because an array reference must be given. The second bind_param_array() set in the example uses just a string (i.e., 'active'). That value will be used for all rows updated.